VBAを使っていると「もっと簡単に数値の処理ができればいいのに…」と思うことがありませんか?
実は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を使用すると、通常のExcel関数と同じようにエラーが発生することがあります。
たとえば、WorksheetFunction.VLookupを使って値を検索する場合、該当する値が見つからないとエラーが発生します。
しかし、VBAではそのエラーを自動的に処理することができません。
このため、エラーハンドリングのコードを事前に組み込まないと、エラー発生時にプログラムが停止してしまうリスクがあります。
エラーハンドリングの対策として、「On Error Resume Next」と「IsError関数」を使用するなど、エラーが発生してもプログラムが止まらず、適切なメッセージを表示できるようにすることが重要です。
これにより、予期しないエラーによるプログラムの停止を防ぎます。
- WorksheetFunctionを使用すると、通常のExcel関数と同じようにエラーが発生することがあります。
- 一部の関数しか利用できない
- WorksheetFunctionでは、Excelのすべての関数を使用できるわけではありません。
特に、「IFERROR」や「TEXTJOIN」などの新しい関数は、WorksheetFunctionでは利用できません。
このため、これらの関数を使いたい場合には、他の方法を検討しなければならないことがあります。
代わりに、VBA内で独自にエラー処理を行ったり、文字列結合やエラーチェックのコードを作成する必要があります。
- WorksheetFunctionでは、Excelのすべての関数を使用できるわけではありません。
- 処理速度が低下する可能性
- 大量のデータを扱う際に、WorksheetFunctionを多用すると、処理が遅くなることがあります。
これは、WorksheetFunctionが呼び出されるたびに、Excelのワークシートにアクセスするためです。
特に大規模なデータセットを繰り返し処理する場合、何度もワークシートを参照することになり、処理速度が低下する原因となります。
こういった場合には、WorksheetFunctionを使うよりも、VBA独自のループや条件分岐を使用して処理を効率化する方法を検討することが必要です。
- 大量のデータを扱う際に、WorksheetFunctionを多用すると、処理が遅くなることがあります。
WorksheetFunctionを使った使用例
では、実際にWorksheetFunctionを使ったコードと、使わなかった場合のコードを比較してみましょう。
使用例1: 合計値を求める
この例では、指定した範囲内のセルに入っている数値の合計を計算します。
合計値を求める方法として、WorksheetFunctionを使った方法と使わない方法を比較します。
WorksheetFunctionを使った場合
Sub SumWithWorksheetFunction()
Dim total As Double
total = WorksheetFunction.Sum(Range("A1:A10"))
MsgBox "合計値は " & total
End Sub
- このコードでは、WorksheetFunction.Sumを使って「A1」から「A10」までのセルの合計を計算しています。
- 合計を計算するだけであれば、1行で済むため、非常にシンプルです。
- MsgBoxは合計値を表示するために使われます。
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
- 「Forループ」を使って、1行ずつ手動で合計を計算しています。
- Cells(i, 1).Valueは、A列のi行目のセルの値を指しています。
このように手動でループを使う場合、計算式が複雑になりがちで、コードも長くなります。 - そのため、複数のセル範囲を合計する際は、WorksheetFunction.Sumを使う方がコードが簡潔になります。
使用例2: 平均値を求める
こちらの例では、指定された範囲のセルに含まれる数値の平均を計算します。
平均値を求める方法として、WorksheetFunctionを使う方法と使わない方法を比較します。
WorksheetFunctionを使った場合
Sub AverageWithWorksheetFunction()
Dim avg As Double
avg = WorksheetFunction.Average(Range("A1:A10"))
MsgBox "平均値は " & avg
End Sub
- WorksheetFunction.Averageを使って、「A1」から「A10」までの範囲の平均値を計算しています。
- コードが非常に簡潔で、1行で平均を計算できるのが利点です。
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
- 合計とデータの個数を手動で計算して、最後に割り算をして平均を求めています。
- Forループを使うことで手動で処理していますが、WorksheetFunction.Averageを使った方がコードが少なく、エラーも出にくいです。
使用例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.CountIfを使うことで、条件に合ったセルの個数を簡単に数えられます。
- たとえば、「”>10″」と書くことで、「10」より大きい数をカウントする設定をしています。
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
- For Eachループを使って、セルを1つずつチェックし、条件を満たすセルの数を手動でカウントしています。
- ループ処理が必要になるため、コードが長くなりがちですが、WorksheetFunction.CountIfを使うと簡単にできます。
使用例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.VLookupを使って、値が100のセルを検索し、対応するB列の値を取得します。
- エラーが発生した場合でも、プログラムが止まらないように、IsErrorを使ってエラーチェックを行っています。
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
- Forループを使って、A列の中から手動で値を検索し、その値に基づいてB列の対応する値を取得しています。
- WorksheetFunctionを使わない場合は、こうした手動の検索が必要となるため、やや複雑なコードになります。
まとめ
WorksheetFunctionは、VBAでExcelの関数を使いたいときに非常に便利です。
数値の合計や平均、条件に合ったセルのカウント、値の検索など、複雑な処理をシンプルにしてくれます。
しかし、便利な一方で、いくつかのデメリットも存在します。
ここで、この記事で学んだポイントをおさらいしましょう。
ポイントのおさらい
WorksheetFunctionは、VBAでExcelの関数を使う際に非常に便利で、コードを簡潔にすることができます。
特に数値の処理や検索関数の利用などにおいて、作業効率を大幅に向上させることができます。
しかし、エラーハンドリングや処理速度の低下などのデメリットもあるため、注意が必要です。
これらをしっかり理解し、状況に応じてWorksheetFunctionを使いこなすことで、VBAの力を最大限に引き出しましょう。