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

「VBAの処理が、もっと簡単にできたらいいのに…」
そんなことを思ったことがありませんか？

・VBAで「SUM関数」って使用できるの？

・VBAで「VLOOKUP関数」を使って検索を行いたい

その場合は、VBAの「WorksheetFunction」を
使用すると解決します！

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

この記事では、WorksheetFunctionを使ってVBAで合計や平均を簡単に計算する方法を解説します。
さらに、COUNTIFやVLOOKUPを使ったデータ検索や条件に合うセルのカウントなど、実務で役立つ具体例も紹介します。

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

【　この記事の概要　】

VBAをしっかり学びたいけれど、「どこから始めればいいかわからない」と迷っていませんか？

そんな方には、UdemyのVBA講座がおすすめです。

動画での丁寧な解説や、実践的な学習スタイルで、初心者でも無理なく基礎から応用まで習得できます。
自己学習を進める上で、何度でも繰り返し見返せる講座は非常に心強い味方になりますよ！

＼ 　自分のペースで学べるVBA講座はこちら　 ／

【 Udemy 】初心者でも安心！VBA講座を見る

​

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

VBAには関数が少ない？ WorksheetFunctionで簡単解決！

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でワークシート関数を簡単に実装！

そこで便利なのが「WorksheetFunction」です。

これを使えば、わずか1行で「SUM（合計）」や「AVERAGE（平均）」など、Excelでおなじみのワークシート関数をVBAで簡単に使えるようになります。

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

コード例: WorksheetFunctionを使って合計を求める

Sub WorksheetFunctionを使った合計計算()
    Dim total As Double
    
    total = WorksheetFunction.Sum(Range("A1:A10"))

    MsgBox ("合計は、" & total & "です。")
End Sub

このように、「WorksheetFunction」を使うことでコードがシンプルになり、ミスを減らすことができます。

VBA初心者でも簡単に「合計」や「平均」を求められるので、作業効率がぐっとアップしますよ。

予測変換を使ってWorksheetFunctionを素早く入力しよう

VBAで「WorksheetFunction」という長い名前を毎回入力するのは大変ですよね。
そこで便利なのが予測変換です。

そこで便利なのが予測変換です。
キーボードの「Ctrl」 + 「スペース」キーを押すだけで、入力候補から簡単に選択できるので、長い関数名を入力する手間を省けます。

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

予測変換の使い方

1. まず、「Work」まで入力します。
2. 次に「Ctrl」 + 「スペース」キーを押すと、自動的に候補が表示されます。
3. 候補から「WorksheetFunction」を選択するだけで、簡単に入力できます。

WorksheetFunctionのデメリットと注意ポイント【VBAエラー回避方法】

WorksheetFunctionは非常に便利な機能ですが、VBAで使う際にはいくつかの注意ポイントやデメリットがあります。
これらを理解し、適切に対処することで、安全かつ効率的に利用できます。

WorksheetFunctionのエラー回避方法について

WorksheetFunction はVBAで便利にExcelの関数を使うことができる機能ですが、使用中にエラーが発生することがあります。

たとえば、WorksheetFunction.VLookupを使って値を検索する際、該当する値が見つからないとエラーが発生します。

しかし、VBAではこのエラーを自動で処理できないため、しっかりとエラー回避対策を行わないと、プログラムが途中で停止してしまうリスクがあります。

【対策】On Error GoTo を使ったエラー回避方法

以下のコードでは、On Error GoToを使用してWorksheetFunction.VLookupのエラー回避対策を行う方法を紹介します。
これにより、VLookupで検索した値が見つからない場合にエラーメッセージを表示し、プログラムが正常に終了するようにしています。

コード例：On Error GoToを使ったエラー回避対策

Sub VLookupWithErrorHandling()
    Dim result As Variant
    Dim searchValue As Variant
    Dim searchRange As Range

    '// 検索する値と範囲の設定
    searchValue = 100 '// 検索する値を設定
    Set searchRange = Range("A1:B10") '// 検索する範囲を設定

    '// エラー回避対策の開始
    On Error GoTo ErrorHandler

    '// VLOOKUPの実行
    result = WorksheetFunction.VLookup(searchValue, searchRange, 2, False)

    '// エラーがなければ結果を表示
    MsgBox "検索結果は: " & result
    On Error GoTo 0
    Exit Sub

ErrorHandler:
    '// エラーが発生した場合の処理
    MsgBox "値が見つかりません"
    On Error GoTo 0
End Sub

解説

なぜOn Error Resume NextよりOn Error GoToが良いのか？

On Error Resume NextとIsErrorを使った方法では、VLookupが見つからない場合に空白が返されるなど、予期せぬ挙動をすることがあります。

On Error GoToを使ったエラー回避対策は、エラー発生時に確実に処理を制御できるため、より堅牢で信頼性の高いコードを書くことができます。

一部の関数しか利用できない

WorksheetFunctionでは、Excelのすべての関数を使用できるわけではありません。
特に、「IFERROR」や「TEXTJOIN」などの新しい関数は、WorksheetFunctionでは利用できません。

このため、これらの関数を使いたい場合には、他の方法を検討しなければならないことがあります。
代わりに、VBA内で独自にエラー処理を行ったり、文字列結合やエラーチェックのコードを作成する必要があります。

処理速度が低下する可能性

大量のデータを扱う際に、WorksheetFunctionを多用すると、処理が遅くなることがあります。

これは、WorksheetFunctionが呼び出されるたびに、Excelのワークシートにアクセスするためです。
特に大規模なデータセットを繰り返し処理する場合、何度もワークシートを参照することになり、処理速度が低下する原因となります。

こういった場合には、WorksheetFunctionを使うよりも、VBA独自のループや条件分岐を使用して処理を効率化する方法を検討することが必要です。

VBAのWorksheetFunctionを使った使用例をご紹介

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

以下に紹介する使用例は、すべてコピペしてすぐに実行できるコードです。
気になる例は、ぜひご覧いただき、実際に実行してみてください。

VBAの基礎から応用までを効率よくしっかり学習したい方には、UdemyのVBA講座もおすすめです。

特に自己学習で進める場合、ビデオや実践例があると理解が進みやすくなります。

＼ 　自分のペースで学べるVBA講座はこちら　 ／

【 Udemy 】初心者でも安心！VBA講座を見る

​

比較例1: 合計値を求める (Sum関数)

この例では、指定した範囲内のセルに入っている数値の合計 を計算します。

合計値を求める方法として、WorksheetFunctionを使った方法と使わない方法を比較します。

WorksheetFunctionを使った場合

以下のコードでは、WorksheetFunctionを使ってSum関数で合計を求めています。

コード例

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: 平均値を求める (Average関数)

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

WorksheetFunctionを使った場合

以下のコードでは、WorksheetFunctionを使ってAverage関数で平均を求めています。

コード例

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の力を最大限に引き出しましょう。

あわせて読みたい

サイトマップ このページは、本サイトの記事を分かりやすくまとめました。ぜひご覧ください。 目次 「VBA」を見る 「Officeスクリプト」を見る 「ワークシート関数」を見る 「JavaScr...