配列関係の覚書(3) - 素人がExcelVBAによる「一人働き方改革」に挑むブログ

配列関係の覚書

表を一旦配列にぶち込む

まことに今さらながらで恐縮だが、

表のデータを利用するなら先に配列にぶち込むと激速

ということを知った。

っていうか、いろんなところで目にはしていたんだけれども、重要性を認識していなかった、ないしは端的に分かっていなかったということです、ハイ。

表の値を２次元配列にぶち込むテクニック

これは、あちこちで紹介されているテクニックで、私は一番最初は日経ソフトウエア誌の武藤玄さんの連載で知った。今なら『いますぐExcel VBAが使えるようになる本』で読めますね。

たとえば、

f:id:akashi_keirin:20180212111952j:plain

こんなふうに、「Sheet1」のJ1:M11セルにこんな表があるとしたら、

Dim Sh As Worksheet
Set Sh = ThisWorkbook.Worksheets("Sheet1")
Dim a As Variant
a = Sh.Range("J1").CurrentRegion.Value

ってするだけで、Variant型の変数 a に（投げやりな変数名でスマン）、２次元配列の形で表の値をぶち込むことができるというものだ。

使ってみた

さきほどの

f:id:akashi_keirin:20180212111952j:plain

こいつを使って、次のコードで実験してみた。

リスト１　標準モジュール

Public Sub testArray()
  Dim Sh As Worksheet    '……(1)'
  Set Sh = ThisWorkbook.Worksheets("Sheet1")
  Dim a As Variant
  a = Sh.Range("J1").CurrentRegion.Value
  Dim maxRow As Integer    '……(2)'
  Dim maxColumn As Integer
  maxRow = UBound(a, 1)
  maxColumn = UBound(a, 2)
  Dim i As Integer
  Dim j As Integer
  Dim str As String
  For i = 0 To maxRow    '……(3)'
    str = ""
    For j = 0 To maxColumn
      If j = 0 Then
        str = a(i, j)
      Else
        str = str & " | " & a(i, j)
      End If
    Next
    Debug.Print str
  Next
End Sub

まず、(1)からの４行

Dim Sh As Worksheet
Set Sh = ThisWorkbook.Worksheets("Sheet1")
Dim a As Variant
a = Sh.Range("J1").CurrentRegion.Value

は、Variant型の変数 a に、Ｊ１セルのアクティブセル領域の値をそのままぶち込む、というコードになっている。

たったこれだけで、 a には表の値が２次元配列の形でぶち込まれるのだ。便利！

(2)からの４行

Dim maxRow As Integer
Dim maxColumn As Integer
maxRow = UBound(a, 1)
maxColumn = UBound(a, 2)

では、おなじみUBound関数を用いて、２次元配列の１次元目、２次元目の添字最大値を取得。

UBound関数の第２引数「Demention」ってのはそういうことだったんだね。

あとは、(3)からの11行

For i = 0 To maxRow
  str = ""
  For j = 0 To maxColumn
    If j = 0 Then
      str = a(i, j)
    Else
      str = str & " | " & a(i, j)
    End If
  Next
  Debug.Print str
Next

では、２重のForループを用いて、２次元配列 a の値を列方向に「 | 」でつなぎつつ行ごとにイミディエイトに出力している。

コードが無駄に長いので、内側のForループは

For j = 0 To maxColumn
  str = str & a(i, j) & " | "
Next
str = Left(str, Len(str) - 3)

とでもすべきだったかな。

正直、スマンかった。

実行

実行してみると、

f:id:akashi_keirin:20180212112118j:plain

！？

は、はぁ？？？

f:id:akashi_keirin:20180212112134j:plain

ま、まさか……？

f:id:akashi_keirin:20180212112151j:plain

……ってことは、添字が「1」始まりとかなの？？？

コードの修正

ということで、次のように修正

リスト１改　標準モジュール

Public Sub testArray()
  Dim Sh As Worksheet
  Set Sh = ThisWorkbook.Worksheets("Sheet1")
  Dim a As Variant
  a = Sh.Range("J1").CurrentRegion.Value
  Dim maxRow As Integer
  Dim maxColumn As Integer
  maxRow = UBound(a, 1)
  maxColumn = UBound(a, 2)
  Dim i As Integer
  Dim j As Integer
  Dim str As String
  For i = 1 To maxRow    '……(*)'
    str = ""
    For j = 1 To maxColumn    '……(*)'
      If j = 1 Then    '……(*)'
        str = a(i, j)
      Else
        str = str & " | " & a(i, j)
      End If
    Next
    Debug.Print str
  Next
End Sub

配列添字の開始値に関するところ(*)を全部「0」から「1」に変えただけ。