Mutable_Yunの業務改善/生産性向上

VBAやPythonなどを利用した業務改善、自動化について。実践的なサンプルや考え方、考察、問題提起。VBAの記事は独自に定めたレベルのランク付けに応じています。

VBAで作ったマクロの高速化① 配列を使う

せっかくエクセル作業の自動化プログラムを作ったのに、実務で使ってみたら思ったより時間がかかったり、画面が白くなって強制終了したりしたことはありませんか。私はあります。

それでも手作業よりは早いので、初めて私のツールを使ってくれた人は満足です。でも、そのうち不満が出てきます。業務の8割はエクセルを使っていて、その一部を自動化したけど、その一部のマクロを使ったら数分間別のエクセルを開くことができない。。。

不満です。そこで、高速化を試みます。

エクセルの自動化がサクサク動かない理由は大きく分けて2つあります。

  • セルの書き込みに時間がかかる
  • 繰り返すことを繰り返していて、トータルでみると何百万回も計算している

今回は前者の対策をします。

下記のコードを実行してみてください。
環境によって違いますが5秒~15秒の間くらいで終わると思います。

Sub 時間計測_1()
    
    Dim i As Long, j As Long
    
    Debug.Print Time
    
    For i = 1 To 100
        For j = 1 To 100
            Cells(i, j) = i * j
        Next j
    Next i
        
    Debug.Print Time
    
End Sub

100 x 100の掛け算表を作成しましたので、10,000個のセルに書き込んだことになります。

私の実行環境では約5秒かかりました。10,000セルで5秒ということは似たような作業を何度か行ったり、
行数が増えたり、あるいはその両方をさらに数回繰り返すとなると許容できない時間がかかってくることになります。

ワークシートが見える状態で実行すると、次々にセルに書き込む様子を見ることができます。
逆に言うと、目に見える程度の速さしか出ていないことになります。

それでは、配列を使ってみたいと思います。
セルに書き込むところに時間がかかるので、掛け算は配列の中で行い、最後に一回だけシートにペタッと貼り付けます。

普通、範囲を変数に格納する時は、オブジェクトを入れる時のルールでSetを使いますが、今回は値のみを入れる為、Setは不要です。エクセルさんが気を利かせて勝手に二次元配列に置き換えてくれます。

このため、シートに書き込むのと同じ感覚で取り扱えるため、とても便利です。私は心の中ではこの配列の事を「脳内エクセルシート」と呼んでいます。

上のコードを配列を利用したものに書き換えます。

Sub 時間計測_2()
    
    Dim i As Long, j As Long
    Dim arr As Variant '100x100の掛け算表を格納する変数 ’★
    
    Debug.Print Time
    
    arr = Range(Cells(1, 1), Cells(100, 100)) 'Setをつけずに範囲を変数に入れる ’★
    
    For i = 1 To 100
        For j = 1 To 100
            arr(i, j) = i * j   '★
        Next j
    Next i
    
    '配列の値ををシートに戻す。範囲を配列にした式の左右を逆にしただけ
    Range(Cells(1, 1), Cells(100, 100)) = arr    '★ 
        
    Debug.Print Time
    
End Sub


★のコメントを付けたところが変更点です
Variant型で変数を定義し、Setをつけずに範囲を格納すると、勝手に二次元配列となります。

End Subの直前でStopと書いてマクロを止め、イミディエイトウィンドウで表示させるとこうなります。
f:id:mutable_yun:20190907193419p:plain

インデックス番号が1から始まるのでワークシートでCells( i , j )と書くのと同じ感覚でarr( i , j )と書く事ができます。

VBAで書くと処理が遅い」はエクセルさんのセルが列の幅や行の高さ、文字の色、背景の色など様々なプロパティを持っていて、それをコンピュータさんが律義に画面に表示しているからです。

皆さんが見えるように文字の色や背景などをいちいち考慮して下さっています。普段はめちゃくちゃお世話になってるのに、今回は余計なお世話感があります。

なので、「VBAは遅い」とか「エクセルは遅い」と言うのはやめて、パソコンさんが必要な計算だけをしてもらう事を考えましょう。コンピュータは余計な計算をさせられてしかも遅い呼ばわりされている被害者です。

エクセルさんもいつもの手作業の時と同じように良かれと思って様々なプロパティをコンピュータさんに渡しています。

それなら自動化を試みる私たちが、相手(実際に計算をするコンピュータさんと、いつもは便利な機能にお世話となっているけど今回は余計なお世話のエクセルさん)の気持ちになって考えればよい話です。

配列は変数の計算なのでエクセルさんのお気遣いである前述の様々なプロパティをコンピュータさんが考えなくていいので、本領を発揮できます。

そこまで分かった上で「VBAは遅い」と言うのなら、それはVBAの仕様なので、別のプログラミング言語に挑戦するタイミングだと思います。