非IT企業に勤める中年サラリーマンのIT日記

非IT企業でしかもITとは全く関係ない部署にいる中年エンジニア。唯一の趣味がプログラミングという”自称”プログラマー。

【ExcelVBA】ワークシート関数をVBAで使いこなす!基本とエラー対策

   

Excel VBAを使ってデータ処理を行う際、ワークシート関数をVBAから利用できると、処理の幅が大きく広がります。この記事では、VBAでワークシート関数を使う基本的な方法から、エラー発生時の対処法まで、具体的なコード例を交えて解説します。

VBAでワークシート関数を使う基本

WorksheetFunctionオブジェクトの利用

Excelのワークシート関数は、VBAの `Application.WorksheetFunction` オブジェクトを介して利用できます。これにより、Excelシート上で使う関数とほぼ同じ感覚でVBAコード内に組み込むことが可能です。

まずは、`SUM` 関数を使って範囲の合計を計算する例を見てみましょう。

Sub SumExample()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") '対象シートを設定

    ' A1:A10の合計を計算し、B1に表示
    ws.Range("B1").Value = Application.WorksheetFunction.Sum(ws.Range("A1:A10"))

    MsgBox "A1:A10の合計は " & ws.Range("B1").Value & " です。", vbInformation
End Sub

上記のコードでは、`Sheet1` の `A1:A10` 範囲の合計値を `B1` セルに書き込み、メッセージボックスで結果を表示しています。

エラー発生時の対応:VLOOKUPを例に

WorksheetFunctionとApplicationの違い

`Application.WorksheetFunction` は非常に便利ですが、関数がエラー(例: `VLOOKUP` で検索値が見つからない `#N/A`)を返すと、VBAの実行が停止してしまいます。これを避けるためには、エラーハンドリングを導入する必要があります。

エラーを捕捉するためには、`Application.WorksheetFunction` ではなく `Application` オブジェクトを直接使い、`On Error Resume Next` と `IsError` 関数を組み合わせるのが一般的です。

Sub VLookupExampleWithErrorHandling()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim searchValue As String
    Dim lookupRange As Range
    Dim result As Variant

    searchValue = "商品Z" ' 検索したい値
    Set lookupRange = ws.Range("A1:B10") ' 検索範囲

    On Error Resume Next ' エラーが発生しても処理を続行
    ' Application.WorksheetFunction.VLookup はエラーで停止するため、Application.VLookup を使う
    result = Application.VLookup(searchValue, lookupRange, 2, False)
    On Error GoTo 0 ' エラーハンドリングを解除

    If IsError(result) Then
        MsgBox searchValue & " は見つかりませんでした。", vbExclamation
        ws.Range("C1").Value = "見つかりません"
    Else
        MsgBox searchValue & " の価格は " & result & " です。", vbInformation
        ws.Range("C1").Value = result
    End If
End Sub
 

 

この例では、`VLOOKUP` で `searchValue` が `lookupRange` 内に見つからない場合、`result` 変数にはエラー値が格納されます。`IsError` 関数でそれを判定し、適切なメッセージを表示しています。

よく使うワークシート関数の利用例

他にも、VBAでよく使われるワークシート関数をいくつか紹介します。

COUNTIF関数で条件に合うセルを数える

特定の条件を満たすセルの数を数える `COUNTIF` 関数も、VBAから簡単に利用できます。

Sub CountIfExample()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim targetRange As Range
    Set targetRange = ws.Range("A1:A10")

    ' "りんご" の数を数える
    Dim appleCount As Long
    appleCount = Application.WorksheetFunction.CountIf(targetRange, "りんご")

    MsgBox "範囲内に 'りんご' は " & appleCount & " 個あります。", vbInformation
End Sub
 
MAX/MIN関数で最大値・最小値を取得する

範囲内の最大値や最小値を取得する `MAX`、`MIN` 関数も同様に利用できます。

Sub MaxMinExample()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim dataRange As Range
    Set dataRange = ws.Range("A1:A10")

    Dim maxValue As Double
    Dim minValue As Double

    maxValue = Application.WorksheetFunction.Max(dataRange)
    minValue = Application.WorksheetFunction.Min(dataRange)

    MsgBox "最大値は " & maxValue & "、最小値は " & minValue & " です。", vbInformation
End Sub
 

 

まとめ

  • VBAからワークシート関数を利用するには `Application.WorksheetFunction` オブジェクトを使います。
  • エラーが発生する可能性のある関数(例: `VLOOKUP`)では、`Application` オブジェクトと `On Error Resume Next`、`IsError` を組み合わせてエラーハンドリングを行うことが重要です。
  • ワークシート関数をVBAで活用することで、コードの記述量を減らし、複雑な処理を簡潔に実装できます。

スポンサーリンク

 - Excel, VBA