VBA(Excel)高速化対策 -配列化編-

概要

「VBA 高速化」で検索すると恐らく「配列化」みたいなキーワードが高確率で引っかかるんじゃないかと思います。

確かに有効な手段なのですが、VBA触り始めだとこの概念が掴めなかったりしませんか？
(私の思考の問題かも知れないですが…。)

今回はその辺りを私なりに噛み砕いて綴っていきます。

本編

サンプルシート

こんなシートがあったとします。

Rangeで範囲取得

ワークシートから任意のセル範囲を取得する時は、Rangeプロパティを使うよ。

Dim myRange as Range

Set myRange = ActiveSheet.Range( "A1:C3" )
' または
Set myRange = ActiveSheet.Range( "A1", "C3" )

これで myRange に「A1～C3」のセル情報が格納されるよ！

配列化

上の myRange 配列化する場合はこう書くよ。

Dim myArray as Variant

myArray = myRange

とっても簡単ね！

概念

このとき myArray がどういう状態なのか、C#風に表すとこんな感じだよ！

string[,] myArray = {
    { "壱", "弐", "参" },
    { "肆", "伍", "陸" },
    { "漆", "捌", "玖" }
};

↑のように、Valueが格納された二次元配列になっているよ！

myArray[行][列] だね！

セル番号で表現するとこんな感じ。

myArray = {
    { A1, B1, C1 },
    { A2, B2, C2 },
    { A3, B3, C3 }
};

わかったかな？

注意点

セル番号と要素番号

配列の要素番号は1スタートだから気を付けてね！

' Error!! (@_@;)
MsgBox myArray(0,0)

' OK!! (^o^)
MsgBox myArray(1,1) ' MsgBox[壱]

また、要素番号の最大値は UBound 関数で取得することができるよ！

Dim r_max, c_max As Integer

r_max = UBound(myArray)   ' 行要素番号の最大値を取得(第二引数は省略可)
c_max = UBound(myArray,2) ' 列要素番号の最大値を取得

For文を回すときはこんな感じ。

Dim r, c as Integer

For r = 1 To UBound(myArray)
    For c = 1 To UBound(myArray,2)
        ' Rangeで取得した全てのセルのValueをMsgBoxで表示
        MsgBox myArray(r,c)
    Next
Next

あと、例えば myRange が

Set myRange = ActiveSheet.Range( "B2:D4" )

だった場合、 myArray の内訳は、

MsgBox myArray(1,1) ' [伍]
MsgBox myArray(1,2) ' [陸]
MsgBox myArray(1,3) ' []
MsgBox myArray(2,1) ' [捌]
 :
 :

となる点に注意してね！

つまり、 myArray の要素番号は、Rangeで拾った範囲内での位置で、ワークシートのセル番号とは異なるよ！

セルに対する操作

myArray = myRange

で myArray に格納されるのは、あくまで Range().Value だから、
実際のセルに対して操作を行なう場合はCellsやRangeで改めて要素にアクセスする必要があるよ！

例えば、「セルB2の値、"伍"を"5"に書き換える」場合。

' NG!! (ToT)
myArray(2,2) = "5"

' Justice!! ('ω'*)
myRange.Cells(2,2).Value = "5"

要素数

例えば、

Set myRange = ActiveSheet.Range( "A1" ) ' Rangeの範囲が1セルのみ

myArray = myRange

このように myRange の要素数が1つだった場合、 myArray は配列として扱われないよ！

以降の処理が「myArray = 配列」の前提で記載されているとエラーが出ちゃうから気を付けてね。

' Error!! (>_<;)
For Each e in myArray
    MsgBox e
Next e

実用例

以上を踏まえて、簡単な実用例を書いてみるね！

こんなシートがあります。

現在選択されている「F3～L10」で、値が格納されていないセルの色を変えるサンプルだよ。

Private Sub MyButton_Click()
    Dim myArray As Variant
    Dim myRange As Range: Set myRange = Selection ' 選択中のセル範囲を取得
    Dim r, c As Integer

    ' myRangeの要素数が1より大きい場合
    If myRange.Count > 1 Then
        myArray = myRange ' 配列化

        ' r(ow) * c(ol) ループ
        For r = 1 To UBound(myArray, 1)
            For c = 1 To UBound(myArray, 2)
                If Len(myArray(r, c)) = 0 Then
                    ' myRange.Cellsでアクセスし直す
                    myRange.Cells(r, c).Interior.color = RGB(128, 128, 255)
                End If
            Next
        Next
    ' myRangeの要素数が1の場合(配列化できない場合)
    Else
        If Len(myRange.Value) = 0 Then
            myRange.Interior.color = RGB(128, 128, 255)
        End If
    End If

End Sub

うまくできたかな？

SpecialCells使えば…とかいうツッコミはナシで…！ (>ω<;)

myRange.Cells.SpecialCells(xlCellTypeBlanks).Interior.color = RGB(255, 128, 128)