excel VBA シートをメモリの読み込んでから各種の処理をする

———————————————————————————-

amazonを請求書払いで!

シートを配列に読み込む

———————————————————————————-

Public Function Sheet2Array(BookName As String, SheetName As String) As Variant

Dim RowNum As Double

Dim ColNum As Double

With Workbooks(BookName).Sheets(SheetName)

RowNum = .Cells(Rows.Count, 1).End(xlUp).row

ColNum = .Cells(1, Columns.Count).End(xlToLeft).Column

Sheet2Array = .Range(.Cells(1, 1), .Cells(RowNum, ColNum))

End With

End Function

‘まずはシート全体を2次元配列にするプログラムである。標準モジュールに下記Sheet2Arrayを記入する。内容としてはシート内の最終行列を取得し、Rangeの範囲内を2次元配列にするというものである。

———————————————————————————-

Dim DataBase() As Variant

DataBase = Sheet2Array(ThisWorkbook.Name, “Sheet1”)

‘使い方としてはメインフローのデータを読み込みたいところで↑のようにして使う。1行目で大きさ未定のDataBase配列(Variant型)を定義し、2行目でSheet2Array関数を呼び出してDataBase配列に値が入るようになっている。引数は1つ目がブック名(同じブック内であればThisWorkbook.Nameでブック名が取得できる)、2つ目がシート名である

———————————————————————————-

‘配列の有効列数を求める

Public Function ArrayColumn(ArrayData As Variant) As Double

Dim i As Double

For i = 1 To UBound(ArrayData, 2)

If ArrayData(1, i) = “” Then

ArrayColumn = i – 1

Exit For

End If

Next

If ArrayColumn = 0 Then

ArrayColumn = UBound(ArrayData, 2)

End If

End Function

‘配列の有効行数を求める

Public Function ArrayRow(ArrayData As Variant) As Double

Dim i As Double

For i = 1 To UBound(ArrayData, 1)

If ArrayData(i, 1) = “” Then

ArrayRow = i – 1

Exit For

End If

Next

If ArrayRow = 0 Then

ArrayRow = UBound(ArrayData, 1)

End If

End Function

———————————————————————————-

これを使って2次元配列内をループさせてみる。処理内容のところにIF文や出力内容を入れれば必要なデータのみを抜き出すことが出来る。for Next文を使っているがVBAではインデックス(数字を変数にしてループ)を使ったものだとFor Nextが一番速い。For Each Nextを使ってインデックスループさせると処理速度が急激に遅くなるので注意しよう。

Dim RowNum As Double ‘行数

Dim ColNum As Double ‘列数

Dim i As Double ‘ループ変数

Dim j As Double ‘ループ変数

RowNum = ArrayRow(DataBase)          ‘行数取得

ColNum = ArrayColumn(DataBase)       ‘列数取得

For i = 1 To RowNum

For j = 1 To ColNum

Debug.Print DataBase(i, j)   ‘処理内容

Next

Next

———————————————————————————-

‘配列をシートに書き込む

———————————————————————————-

Public Sub Array2Sheet(ArrayData As Variant, BookName As String, SheetName As String)

Dim RowNum As Double

Dim ColNum As Double

RowNum = ArrayRow(ArrayData)

ColNum = ArrayColumn(ArrayData)

With Workbooks(BookName).Sheets(SheetName)

.Cells.Clear

.Range(.Cells(1, 1), .Cells(RowNum, ColNum)) = ArrayData

End With

End Sub

使い方としてはシートに出力したいところで

Call Array2Sheet(DataBase, ThisWorkbook.Name, “Sheet4”)

ように呼び出す。これでSheet4にDataBase配列の内容が出力されているはずである。引数としては1つ目が出力する2次元配列、2つ目がブック名、3つ目がシート名である。読み込みと同様、ループ文は使っていないので高速で処理ができる。

———————————————————————————-

ちなみに2次元配列であればArray2Sheet関数でデバッグのようにSheetに出力できるのでプログラム作成中にも役立つはずだ。注意する点としては通常配列は(0,0)から始まるため、このままシートに出力すると余分な空白の1行、1列が出来てしまう。これを防ぐため(わかりやすくするためにも)配列を作成する時に(1 to 1000, 1 to 1000)と言う風に範囲を指定してやると良い。

dim test1(1000,500)          ’ 行0~1000 列0~500 の範囲になる

dim test2(1 to 1000, 1 to 500)    ‘行1~1000 列1~500 の範囲になる

自分で作成した2次元配列を出力したい場合は(1 to 1000, 1 to 1000)という感じで範囲を指定して定義しよう。するとシートと座標が一致してわかりやすい。

————————————————————————————————————————————–

————————————————————————————————————————————–

以前にも配列を使うと便利という記事を書いたが、もう少し踏み込んで紹介したいと思う。サンプルプログラムも載せていくので参考にしていただければと思う。ただ、筆者は趣味でプログラムを組むぐらいなので細かい間違いがあるかもしれない。そこは読み解きながら理解していただけるとありがたい。(御連絡いただければ修正します)

まず、大量のデータ処理を行うにはメモリにデータを格納する必要がある。配列、コレクション、構造体などあるが、シンプルに配列を使うと処理速度も速く、プログラムもわかりやすくなるだろう。配列には1次元配列、2次元配列、3次元配列等、次元数を増やして使うことが出来る。ここではエクセルで使うことが多いであろう、2次元配列をメインで紹介していく。エクセルは表計算ソフトであるためセルを1つ1つのデータ格納領域とみることができ、非常に2次元配列に近いものがあるからだ。

基本的な宣言の仕方としては

 

Dim Data1(1 To 10) As Variant ‘1次元配列

Dim Data2(1 To 10, 1 To 10) As Variant ‘2次元配列

Dim Data3(1 To 10, 1 To 10, 1 To 10) As Variant ‘3次元配列

Dim Data4()as Variant ‘動的配列

と、書くことが出来る。「Dim 変数名(要素数) As 型」で書くのが基本だ。括弧の中は要素数で1次元配列で言うとData1(1)~Data(10)までの10個のデータを入れる箱ができる。同様にData2(1,1)~Data2(10,10)という2次元配列、Data3(1,1,1)~Data(10,10,10)という3次元配列が出来る。動的配列は要素数が未定のときに宣言するというものである。型は何でも入れれるようにバリアント型を今回は使用している。

 

本当は「Dim Data1(10) as Variant」という書き方でもよいが、この場合、配列はData1(0)~Data1(10)の11個の要素ができてしまう。このゼロから始まるとループするときなどややこしくなるので、1から始まるように指定している。また、エクセルのセルは左上が1,1(A1)から始まっているので1から始まるようにしたほうが、処理していくにはわかりやすい。

では実際に使っていこうと思うが、データ処理というと、大体は「入力→処理→出力」という流れである。これを実際に配列を使って一連のことをやってみようと思う。エクセルとの親和性を生かすためにシートのデータを読み取って、シートに結果を出力すると言うものを作成する。

 

始めに入力であるが、データをエクセルのセルから1つずつ読み取って配列に入れていては時間がかかる。これはエクセルのセルの操作が入るためで、できるだけセルやシートの操作をしないほうが高速なVBAプログラムを組むことができる。つまり最初(入力)と最後(出力)のときだけセルを操作すると言うのがベストなプログラムの組み方である。途中の計算はすべてメモリ上で行うのが高速化のコツである。

どうやって配列に入力するかというと、セルを範囲で選んで一気に2次元配列化するというのが一番速い。汎用的な操作なので2つの関数を作成する。

まずはシート全体を2次元配列に入れる関数である。モジュールなどに下記コードを入力する。

‘– シートを2次元配列化 —

Public Function Sheet2Array(BookName As String, Sheet As String) As Variant

Dim MaxRow As Double ‘最大行数

Dim MaxCol As Double ‘最大列数

With Workbooks(BookName).Sheets(Sheet)

MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row                ‘最大行数取得

MaxCol = .Cells(1, Columns.Count).End(xlToLeft).Column      ‘最大列数取得

Sheet2Array = .Range(.Cells(1, 1), .Cells(MaxRow, MaxCol))  ‘戻り値に配列

End With

End Function

次に指定した範囲を2次元配列化する関数である。こちらは関数化する必要はないくらい短いので、毎回書いても問題ないレベルだろう。

‘– 指定範囲を2次元配列化 —

Public Function Range2Array(BookName As String, Sheet As String, _

P1R As Double, P1C As Double, _

P2R As Double, P2C As Double) As Variant

With Workbooks(BookName).Sheets(Sheet)

Range2Array = .Range(.Cells(P1R, P1C), .Cells(P2R, P2C))     ‘戻り値に配列

End With

End Function

使い方としてはメインフローの中で、

Dim Data() As Variant

Data = Sheet2Array(ThisWorkbook.Name, “Sheet1”)

と書くと、DataにSheet1のデータが2次元配列として格納される。Dataの大きさは不確定なので動的配列として宣言している。次にSheet2Arrayで先ほどの関数を呼び出して、引数にブック名、シート名を指定している。ThisWorkbook.nameはプログラムを走らせているブック名になる。他のブックを開いてそこから読み取る場合はこの引数を変更して対応可能である。

同様に範囲を指定して2次元配列化するには

Dim Data() As Variant

Data = Range2Array(ThisWorkbook.Name, “Sheet1”, 4, 3, 25, 3)

と書く。この場合はSheet1のC列の4行目からC列の25行目までを2次元配列として読み込んでいる。1列しかないから1次元配列ではないかと思われるかもしれないが、Data(行数,1)の2次元配列になっている。

これでループ等使わず、まとめて一気に2次元配列化できるので、ひとつずつセルを読み込む場合に比べて非常に高速化できると思う。この2つの関数があればシートにあるデータを比較的簡単に配列に入れることが出来ると思う。入力については以上である。

配列を使っているときに不便だと思うのが、本当にデータの入っている要素数がどれだけあるかというのがわかりにくいところだ。コレクションであればAddされた数だけデータが入っているので要素数=データ数になっている。配列の場合は先に固定要素数(大きめに格納領域)を指定してからデータを入れていくので、どこまでがデータが入っていてどこからが空なのかわからないというのがある。そこで次元数と有効な要素数を取得する関数を作成した。

‘ — 配列情報(次元数、要素数)を取得 —

Public Function GetArrayInfo(Data As Variant, ByRef Items As Variant)

Dim i As Double ‘ループ変数

Dim Temp As Variant ‘テンポラリ変数

On Error GoTo DtErr                                 ‘エラーチェック有効

For i = 1 To 10                                  ‘とりあえず10次元までチェック

Temp = UBound(Data, i)                           ‘指定次元のUBoundをチェック

Next

DtErr:

GetArrayInfo = i – 1                                ‘エラーが発生したとき-1が次元数

On Error GoTo 0                                     ‘エラーチェック無効

If GetArrayInfo = 1 Then ‘1次元配列のとき

ReDim Items(1 To 1)                              ‘要素数 次元を1次元に

For i = 1 To UBound(Data, 1)

If Data(i) <> “” Then Items(1) = i            ‘空欄でなければ要素数を増やす

Next

If Items(1) = “” Then GetArrayInfo = -1          ‘データがなければ-1を返す

End If

If GetArrayInfo = 2 Then ‘2次元配列のとき

ReDim Items(1 To 2)                              ‘要素数 次元を2次元に

For i = 1 To UBound(Data, 1)                     ‘1次元の有効要素をチェック

If Data(i, 1) <> “” Then Items(1) = i         ‘空欄でなければ要素数を増やす

Next

If Items(1) = “” Then GetArrayInfo = -1          ‘データがなければ-1を返す

For i = 1 To UBound(Data, 2)                     ‘2次元の有効要素をチェック

If Data(1, i) <> “” Then Items(2) = i         ‘空欄でなければ要素数を増やす

Next

If Items(2) = “” Then GetArrayInfo = -1          ‘データがなければ-1を返す

End If

End Function

使い方としてはメインフローで

Dim DFig As Double ‘配列の次元数

Dim AItem() As Variant ‘要素数

DFig = GetArrayInfo(Data, AItem)                       ‘次元数、有効要素の取得

と言う感じで使う。GetArrayInfo(調べたい配列、有効要素数を入れる配列)を実行すると次元数が戻り値になる。次元数が1次元であればAItem(1)に有効要素数が入っている。2次元であればAItem(1)に1次元目の有効要素数、AItem(2)に2次元目の有効要素数が入っている。今回は指定するData配列が1次元と2次元配列の場合のみ有効要素数と次元数を返すような関数にしている。

これで配列が何次元なのか、有効な要素はいくつあるかを簡単に調べることができる。ループする前に有効要素数を求めておけばループする回数を少なく出来るので高速化する場合もあるだろう。

最後にデータ処理した配列をシートに書き込む関数を作成する。本来であれば2次元配列しかシートに貼り付けできないが、今作成したGetArrayInfoの戻り値の次元数で1次元配列、2次元配列を判断し、シートにどちらでも書き込めるようにプログラムしている。

‘ — 配列をシートに貼り付け —

Public Sub Array2Sheet(Data As Variant, BookName As String, Sheet As String)

Dim DFig As Double ‘配列の次元数

Dim AItem() As Variant ‘要素数

   DFig = GetArrayInfo(Data, AItem)                     ‘次元数、有効要素の取得

If DFig = 1 Then ‘1次元配列の場合

Dim BufArray(1 To 100000, 1 To 1) As Variant ‘2次元配列を用意(10万データまで)

For i = 1 To AItem(1)

         BufArray(i, 1) = Data(i)                     ‘2次元配列に1次元配列を入れる

Next

Call Array2Sheet(BufArray, BookName, Sheet)       ‘2次元配列で再度呼出

ElseIf DFig = 2 Then ‘2次元配列の場合

With Workbooks(BookName).Sheets(Sheet)            ‘シートの指定

         .Cells.Clear                                 ‘前の内容を削除

         .Range(.Cells(1, 1), .Cells(AItem(1), AItem(2))) = Data ‘2次元配列を貼り付け

End With

 

Else

    MsgBox “3次元配列以上またはデータが入っていないので貼り付けできません”

End If

End Sub

使い方としてはメインフローで

Call Array2Sheet(Data, ThisWorkbook.Name, “Sheet2”)

のように呼び出す。Data配列をThisWorkBook.NameのSheet2に書き出している。これも読み込みと同様に範囲を一括で書き出しているので、セルを操作する方法に比べると高速だ。1次元配列、2次元配列を気軽にシートに書き出すことが出来るので最終データの書き出し以外にも計算途中の配列にどのような値が入っているかの確認、デバッグにも使うことができるだろう。

では、ここまでの関数を使って簡単なメインプログラムを作ってみようと思う。Sheet1のA列,B列の数字を読み取って足した数字が1000を超えていれば出力するというプログラムである。条件式を変えれば必要な行のみの出力が可能である。基本的にこれがわかれば大体のデータ処理に対応できるだろう。

Sub main()

‘入力

Dim Data() As Variant ‘データを入れる配列を定義

Data = Sheet2Array(ThisWorkbook.Name, “Sheet1”)         ‘Sheet1シートを配列化

‘データ処理

Dim Result(1 To 10000, 1 To 3) As Variant ‘結果を入れる配列を定義

Dim i As Double ‘ループ変数

Dim j As Double ‘カウント変数

j = 1                                                   ‘初期値

For i = 1 To UBound(Data, 1)

Dim Cal As Variant ‘計算用変数

Cal = Data(i, 1) + Data(i, 2)                        ‘1列目と2列目を足す

If Cal > 1000 Then ‘1000以上の場合のみ出力

Result(j, 1) = Data(i, 1)                         ‘そのまま元データを転写

Result(j, 2) = Data(i, 2)                         ‘そのまま元データを転写

Result(j, 3) = Cal                                ‘3列目に足した結果を格納

j = j + 1                                         ‘出力配列カウントアップ

End If

Next

‘特にやらなくてよいが、配列の中にデータがいくつ入っているか調べてみる

Dim AItem() As Variant

Call GetArrayInfo(Result, AItem)                        ‘Resultの有効要素数を取得(戻り値で次元数も取得できる)

MsgBox AItem(1) & “件が1000を超えました。” ‘1次元目の有効要素数を表示

‘出力

Call Array2Sheet(Result, ThisWorkbook.Name, “Sheet2”)   ‘Sheet2シートにResult配列を出力

End Sub

このプログラムを実行すると、Sheet1のデータを読み取ってSheet2に結果を書き出すという動作になる。

Sheet1のデータ

excelvbaarraypra001

↓↓ Sheet2に出力される ↓↓

excelvbaarraypra002

A,B列は読み取った数字をそのまま出力、C列はAとBを足した値が入っている。(条件で1000を超えるものを出力としているで該当しない行は消えている)

配列を理解して使うことが出来れば、高速で読みやすいプログラムになると思われる。もちろん他の言語ではコレクションや構造体でもよいかもしれないが、VBAに限っては配列を使うことをおすすめしたい。

さらに、実践!エクセルVBAでクイックソートを使うを読んでいただけると大体のデータ処理が出来るようになるだろう。