ExcelVBAでワークシート関数を使う方法をご説明します。
ワークシート関数とはワークシート上で使用できるSUM関数、VLOOKUP関数やCOUNTIF関数の事です。
VBAはそのワークシート関数をコード上で使用する事が可能です。
VBAでツールを作成する上で、ワークシート関数を利用することで複雑な処理ロジックを自作する必要もなくシンプルに、高速に処理する事が可能です。
例えば、合計を算出するコードを自作しようとするとループして都度加算していくなど数行のコードを書いたりしますが、SUM関数を使用する事で1行のコードで記述できますし、且つ処理速度も高速です。
サンプルコードでは、代表的な関数のSum、Vlookup、Countifを使用して説明したいと思います。
1.ワークシート関数の使い方
VBAでワークシート関数を使う方法は非常に簡単です。
次のように記述します。
Application.WorksheetFunction.ワークシート関数名「Application」は省略して記述可能ですので、次のように記述しても問題ありません。
WorksheetFunction.ワークシート関数名また、「WorksheetFunction」を省略した記述を目にする事があるかと思いますが、こちらについてはExcel97以前の古い記述方法となります。
特別な理由がない限りは「WorksheetFunction」を使用して記述する事をおすすめします。
範囲や検索値を指定する際は、文字列で式を作成する必要がなく、RangeやCellsがそのまま使用できますので、ループなども簡単に組み込めます。
Excelでワークシート関数を使用した場合に結果がエラーとなる場合、「#DIV/0!」などのエラー値を返すだけですが、VBAの場合はエラーでコードが停止します。
そのため、使いやすいですがしっかり挙動を把握して、エラー処理などを組み込む必要となります。
2.サンプルコード
ワークシート関数を使うサンプルコードを行くとか記載したいと思います。
シンプルなサンプルコード
SUM関数
A11に合計を算出します。
Sub Sample1()
With ActiveSheet
.Cells(11, 1) = Application.WorksheetFunction.Sum(Range(.Cells(1, 1), .Cells(10, 1)))
End With
End Sub
VLOOKUP関数
E1にAを検索文字としたB列の値を取得します。
Sub Sample2()
With ActiveSheet
.Cells(1, 5) = Application.WorksheetFunction.VLookup(.Cells(1, 4), Range(.Cells(1, 1), .Cells(4, 2)), 2, 0)
End With
End Sub
COUNTIF関数
A列のBの個数をD１に取得します。
Sub Sample3()
With ActiveSheet
.Cells(1, 4) = Application.WorksheetFunction.CountIf(Range(.Cells(1, 1), .Cells(12, 2)), .Cells(1, 3))
End With
End Sub
変数を使用したサンプルコード
毎回セルの範囲を記述してはコードも長くなりますし、処理速度も遅くなりますので、変数に格納するとシンプルかつ処理も早くなります。
結果は上記の「シンプルなサンプルコード」と同じです。
SUM関数
Sub Sample4()
Dim MyRng As Range
With ActiveSheet
Set MyRng = Range(.Cells(1, 1), .Cells(10, 1))
.Cells(11, 1) = Application.WorksheetFunction.Sum(MyRng)
End With
End SubVLOOKUP関数
Sub Sample5()
Dim MyRng As Range
Dim SearchRng As Range
With ActiveSheet
Set MyRng = Range(.Cells(1, 1), .Cells(4, 2))
Set SearchRng = .Cells(1, 4)
.Cells(1, 5) = Application.WorksheetFunction.VLookup(SearchRng, MyRng, 2, 0)
End With
End SubCOUNTIF関数
Sub Sample6()
Dim MyRng As Range
Dim SearchRng As Range
With ActiveSheet
Set MyRng = Range(.Cells(1, 1), .Cells(12, 1))
Set SearchRng = .Cells(1, 3)
.Cells(1, 4) = Application.WorksheetFunction.CountIf(MyRng, SearchRng)
End With
End Sub
ループを使用したサンプルコード
SUM関数
A11、B11、C11にそれぞれ合計を出力します。
Sub Sample7()
Dim MyRng As Range
Dim i As Long
With ActiveSheet
For i = 1 To 3
Set MyRng = Range(.Cells(1, i), .Cells(10, i))
.Cells(11, i) = Application.WorksheetFunction.Sum(MyRng)
Next i
End With
End Sub
VLOOKUP関数
E1、E2、E3、E4にそれぞれA、B、C、Dの隣の数字を取得して、出力します。
Sub Sample8()
Dim MyRng As Range
Dim SearchRng As Range
Dim i As Long
With ActiveSheet
Set MyRng = Range(.Cells(1, 1), .Cells(4, 2))
For i = 1 To 4
Set SearchRng = .Cells(i, 4)
.Cells(i, 5) = Application.WorksheetFunction.VLookup(SearchRng, MyRng, 2, 0)
Next i
End With
End Sub
COUNTIF関数
D1、D2、D3、D4にそれぞれABCDの数を取得して出力します。
Sub Sample9()
Dim MyRng As Range
Dim SearchRng As Range
Dim i As Long
With ActiveSheet
Set MyRng = Range(.Cells(1, 1), .Cells(12, 1))
For i = 1 To 4
Set SearchRng = .Cells(i, 3)
.Cells(i, 4) = Application.WorksheetFunction.CountIf(MyRng, SearchRng)
Next i
End With
End Sub
エラー処理のサンプルコード
VLOOKUP関数を例に説明しますと、VLOOKUP関数をVBAで使用しますと検索結果が見つからなかった場合はエラーとなり、VBAが停止してしまします。
エラー処理としては「On Error GoTo ラベル名」でエラーが発生したら、指定したラベルに処理をスキップさせるか、「On Error Resume Next」でエラー自体を無視してしまう方法です。
「On Error GoTo ラベル名」は基本的に1回しか反応しないため、ループ時に使用すると1回以上反応させるための処理が必要になりますので、個人的には「On Error Resume Next」で無視するのも一つかと思います。
エラー処理の詳細について「「On Error」でエラー処理」をご覧ください。
上記のVLOOKUP関数のサンプルで使用したデータを、次のようにD列の「B」を「E」に変更します。
EはA列に存在しないため、「Sample8」のコードのまま何も処理を組み込まないとエラーで停止します。
エラーを回避するためにOn Error Resume Nextを組み込みます。
Sub Sample10()
Dim MyRng As Range
Dim SearchRng As Range
Dim i As Long
With ActiveSheet
Set MyRng = Range(.Cells(1, 1), .Cells(4, 2))
On Error Resume Next
For i = 1 To 4
Set SearchRng = .Cells(i, 4)
.Cells(i, 5) = Application.WorksheetFunction.VLookup(SearchRng, MyRng, 2, 0)
Next i
On Error GoTo 0
End With
End Sub
「On Error Resume Next」は組み込むと解除しない限り、ずっとエラーを無視し続けてしまうので、処理のあとに「On Error GoTo 0」を組み込むことで無視を解除できます。
Eのエラーは空白で処理されて、無事全ての値を取得出来ました。
