VBAでWorksheetFunctionを使いこなそう！基礎からデメリット、使用例まで徹底解説

VBAを使っていると「もっと簡単に数値の処理ができればいいのに…」と思うことがありませんか？

お悩みポイント

• ワークシート関数のように「SUM関数」って使用できるの？
• VBAで「VLOOKUP関数」を使って検索を行いたい。

実はVBAそのものには、Excelのように簡単に「足し算」や「平均」を求めるための関数がありません。
そこで役立つのが「WorksheetFunction」です。

この記事では、初心者の方でも理解しやすいようにWorksheetFunctionの基礎から、使用する際の注意点、具体的な使用例まで解説していきます。

最後まで読んでいただければ、VBAのコードがぐっとシンプルになる使い方を学ぶことができます。

【　この記事の概要　】

この記事を読むとできること

VBAには関数が足りない？ WorksheetFunctionで解決しよう

それでは、「WorksheetFunction」について説明します。

VBAには「関数」がない？

VBAは強力なツールですが、Excelのワークシート上で使えるような「足し算」「平均」などの基本的な関数がそのままでは使えません。

たとえば、ワークシートでよく使うSUM（合計）やAVERAGE（平均）は、VBAの標準関数としては存在していないのです。

VBAで「足し算」で実装しようとすると、以下のように少し複雑なコードを書く必要が出てきます。

Sub 数値の合計を手動で計算するコード()
    Dim i As Long
    Dim total As Double
    
    total = 0
    For i = 1 To 10
        total = total + Cells(i, 1).Value
    Next i
    MsgBox ("合計は、" & total & "です。")
    
End Sub

このように、合計や平均を手動で計算しようとすると、ループ処理や条件式を駆使する必要があり、コードが長くなってしまいます。

これは初心者にとってはハードルが高く、さらにミスの原因にもなりやすいです。

WorksheetFunctionとは？

そこで登場するのが、Excelのワークシート関数をVBA内で使えるようにするWorksheetFunctionです。

このWorksheetFunctionを使えば、VBAから簡単にExcelの関数を利用することができ、コードが非常にシンプルになります。

たとえば、先ほどの「合計」を求めるコードは、WorksheetFunctionを使うと以下のように1行で書けるようになります。

Sub WorksheetFunctionを使った合計計算()
    Dim total As Double
    
    total = WorksheetFunction.Sum(Range("A1:A10"))
    MsgBox ("合計は、" & total & "です。")
End Sub

このように、WorksheetFunctionを使うことで、難しい処理を簡単に書くことができるようになるのです。

予測変換を使おう

毎回「WorksheetFunction」という長い名前を入力するのは大変です。
そこで「Ctrl」+「スペース」キーを押すことで、予測変換を利用できます。
これにより、候補から簡単に選択できるため、長い関数名を入力する手間が省けます。

たとえば、「Work」まで入力したら「Ctrl」+「スペース」を押し、候補からWorksheetFunctionを選択すると、簡単に予測変換する事ができます。

WorksheetFunctionのデメリットと注意ポイント

WorksheetFunctionはとても便利な機能ですが、いくつかのデメリットや注意ポイントがあります。
これらを理解した上で適切に使うことが大切です。

WorksheetFunctionを使った使用例

では、実際にWorksheetFunctionを使ったコードと、使わなかった場合のコードを比較してみましょう。

使用例1: 合計値を求める

この例では、指定した範囲内のセルに入っている数値の合計を計算します。
合計値を求める方法として、WorksheetFunctionを使った方法と使わない方法を比較します。

WorksheetFunctionを使った場合

Sub SumWithWorksheetFunction()
    Dim total As Double
    total = WorksheetFunction.Sum(Range("A1:A10"))
    MsgBox "合計値は " & total
End Sub

WorksheetFunctionを使わなかった場合

Sub SumWithoutWorksheetFunction()
    Dim total As Double
    Dim i As Long
    total = 0
    For i = 1 To 10
        total = total + Cells(i, 1).Value
    Next i
    MsgBox "合計値は " & total
End Sub

使用例2: 平均値を求める

こちらの例では、指定された範囲のセルに含まれる数値の平均を計算します。
平均値を求める方法として、WorksheetFunctionを使う方法と使わない方法を比較します。

WorksheetFunctionを使った場合

Sub AverageWithWorksheetFunction()
    Dim avg As Double
    avg = WorksheetFunction.Average(Range("A1:A10"))
    MsgBox "平均値は " & avg
End Sub

WorksheetFunctionを使わなかった場合

Sub AverageWithoutWorksheetFunction()
    Dim total As Double
    Dim count As Long
    Dim i as Long
    total = 0
    count = 0
    For i = 1 To 10
        total = total + Cells(i, 1).Value
        count = count + 1
    Next i
    MsgBox "平均値は " & total / count
End Sub

使用例3: COUNTIF関数の使用

特定の条件を満たすセルの個数を数えます。
「A1」から「A10」までの範囲で、10より大きい数値のセルがいくつあるかをカウントする処理をWorksheetFunctionを使う方法と使わない方法で比較します。

WorksheetFunctionを使った場合

Sub CountIfWithWorksheetFunction()
    Dim count As Long
    count = WorksheetFunction.CountIf(Range("A1:A10"), ">10")
    MsgBox "10より大きい数値の個数は " & count
End Sub

WorksheetFunctionを使わなかった場合

Sub CountIfWithoutWorksheetFunction()
    Dim rng As Range
    Dim cell As Range
    Dim count As Long
    
    Set rng = Range("A1:A10")
    count = 0
    
    For Each cell In rng
        If cell.Value > 10 Then
            count = count + 1
        End If
    Next cell
    
    MsgBox "10より大きい数値の個数は " & count
End Sub

使用例4: VLOOKUP関数の使用

VLOOKUP関数を使って、ある値を基に別の値を検索します。
この例では、A列に基づいてB列の対応する値を検索する処理を、WorksheetFunctionを使う方法と使わない方法で比較します。

WorksheetFunctionを使った場合

Sub VlookupWithWorksheetFunction()
    Dim result As Variant

    result = WorksheetFunction.VLookup(100, Range("A1:B10"), 2, False)

    If Not IsError(result) Then
        MsgBox "検索結果は " & result
    Else
        MsgBox "値が見つかりません"
    End If
End Sub

WorksheetFunctionを使わなかった場合

Sub VlookupWithoutWorksheetFunction()
    Dim found As Boolean
    Dim result As Variant
    Dim i As Long

    found = False

    For i = 1 To 10
        If Cells(i, 1).Value = 100 Then
            result = Cells(i, 2).Value
            found = True
            Exit For
        End If
    Next i

    If found Then
        MsgBox "検索結果は " & result
    Else
        MsgBox "値が見つかりません"
    End If
End Sub

まとめ

WorksheetFunctionは、VBAでExcelの関数を使いたいときに非常に便利です。
数値の合計や平均、条件に合ったセルのカウント、値の検索など、複雑な処理をシンプルにしてくれます。
しかし、便利な一方で、いくつかのデメリットも存在します。

ここで、この記事で学んだポイントをおさらいしましょう。

ポイントのおさらい

WorksheetFunctionは、VBAでExcelの関数を使う際に非常に便利で、コードを簡潔にすることができます。
特に数値の処理や検索関数の利用などにおいて、作業効率を大幅に向上させることができます。

しかし、エラーハンドリングや処理速度の低下などのデメリットもあるため、注意が必要です。
これらをしっかり理解し、状況に応じてWorksheetFunctionを使いこなすことで、VBAの力を最大限に引き出しましょう。