Excel VBAでワークシート関数についてお探しですね。
広告
Excel VBAでワークシート関数を使う方法【初心者向け完全ガイド】
Excel VBAで集計や検索の処理を作るとき、SUMやVLOOKUPといったおなじみのワークシート関数をそのまま使えたら便利だと思いませんか? 実は、VBAからこれらの関数を呼び出すことができるんです。
わざわざVBAだけで合計や検索の処理を一から書かなくても、Excelに最初から入っている関数を活用すれば、コードがぐっと短く、分かりやすくなります。
この記事では、VBA内でワークシート関数(VLOOKUPやSumなど)を使う方法を、基本からエラー対策まで丁寧に解説していきます。
VBAからワークシート関数を呼び出す基本のキ
VBAからワークシート関数を使うには、`WorksheetFunction`というものを利用します。
普段シート上で`=SUM(A1:A10)`と入力して使っている関数を、VBAでは`WorksheetFunction.Sum(…)`という形で書けば使えるんです。
たとえば、A1からA10までの合計を計算したいとき、セルに数式を入力しなくても、VBAの処理の中だけで計算結果を受け取れます。
これなら作業用のセルを増やす必要もなく、マクロの処理もスッキリまとまります。
基本的な書き方はこんな感じです。
`Application.WorksheetFunction`は、Excelに標準で入っているワークシート関数を使うための入口だと考えてください。
ちなみに、VBAで関数名を書くときは、日本語版のExcelを使っていても**英語名**で書きます。
「合計」という名前のSUM関数も、VBAでは`Sum`と書くので注意しましょう。
“`vb
Sub SampleSum()
Dim total As Double
total = Application.WorksheetFunction.Sum(Range(“A1:A10”))
MsgBox “合計は ” & total & ” です”
End Sub
“`
このコードでは、`Range(“A1:A10”)`をSUM関数に渡して、その結果を`total`という変数に入れています。
セルに`=SUM(A1:A10)`と書いて結果を見るのではなく、VBAの中で直接計算結果をもらっているのがポイントです。
合計、平均、最大値、最小値といった単純な集計なら、`WorksheetFunction.Sum`、`WorksheetFunction.Average`、`WorksheetFunction.Max`のように素直に書けばOKです。
ただし、実際の仕事で使うときは注意点があります。
`Range(“A1:A10”)`のように対象シートを省略すると、そのとき開いているシートによって結果が変わってしまうことがあるんです。
マクロを安定して動かすには、`ThisWorkbook.Worksheets(“Sheet1”).Range(“A1:A10”)`のように、**どのシートを使うか明確に書く**ことをおすすめします。
複数のシートやブックを扱う場合は特に、参照先をはっきりさせておくと、思わぬミスを防げますよ。
SumやAverageなど集計系の関数を使ってみよう
SUMのような集計系の関数は、VBAでワークシート関数を使う練習にぴったりです。
なぜかというと、結果が数値として扱いやすく、検索系の関数みたいに「見つからなかったらエラー」という心配が少ないからです。
売上の合計、テストの平均点、在庫の最大値など、Excelでもよく使う計算ですよね。
これらをVBAから呼び出せるようになると、セルに数式を入れずに集計結果をメッセージで表示したり、別のシートに転記したりできるようになります。
以下は、対象シートをちゃんと指定してSUMとAVERAGEを使う例です。
`Set ws = ThisWorkbook.Worksheets(“売上”)`としておくと、このあとの処理でどのシートを使っているかが一目瞭然になります。
VBAでは同じことを何度も書くより、シートや範囲を変数に入れて使うほうが読みやすく、あとで修正するときも楽になります。
“`vb
Sub SummarySales()
Dim ws As Worksheet
Dim total As Double
Dim avg As Double
Set ws = ThisWorkbook.Worksheets(“売上”)
total = Application.WorksheetFunction.Sum(ws.Range(“B2:B100”))
avg = Application.WorksheetFunction.Average(ws.Range(“B2:B100”))
ws.Range(“D2”).Value = total
ws.Range(“D3”).Value = avg
End Sub
“`
このコードは、B2からB100までの合計をD2に、平均をD3に出力しています。
セルに数式を入れているわけではなく、VBAで計算した結果を**値として**書き込んでいる点がポイントです。
そのため、元データを後から変更しても、D2やD3の値は自動では変わりません。
常に最新の値を表示したいならセルに数式を入れる方法が向いていますが、「この時点での結果」を記録として残したい場合は、VBAで計算して値として出力する方法が便利です。
ただし、気をつけたいことが一つあります。
Excelの計算方法が手動になっている場合や、シート上の数式結果をもとにVBAで集計する場合です。
`WorksheetFunction.Sum`を実行すると指定した範囲の値を集計しますが、その範囲のセルに入っている数式が最新の状態に計算されていないと、古い結果で計算してしまう可能性があります。
必要に応じて`Application.Calculate`でブック全体を再計算したり、`ws.Calculate`で特定のシートだけ再計算してから集計すると安心です。
“`vb
Sub SummaryAfterCalculate()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“売上”)
ws.Calculate
MsgBox Application.WorksheetFunction.Sum(ws.Range(“B2:B100”))
End Sub
“`
集計系の関数をVBAで使うときの基本は、「範囲を正しく指定する」「対象シートを省略しない」「必要なら再計算してから値を取得する」の3つです。
この3点を意識するだけで、単純な集計マクロの安定性はグッと上がります。
特に、他の人が使うマクロや定期的に実行する業務用のマクロでは、アクティブシート任せのコードを避けるだけでもトラブルが減りますよ。
VLOOKUPなどエラーが出る関数は要注意!安全な使い方
VLOOKUPやMATCHといった検索系の関数をVBAから使う場合、SUMと同じ感覚で`WorksheetFunction.VLookup`を使うと、ちょっと困ったことになる場合があります。
シート上でVLOOKUPを使うと、検索値が見つからないときは`#N/A`が表示されますよね。
でも、VBAで`Application.WorksheetFunction.VLookup`を使った場合、検索値が見つからないと`#N/A`を受け取るのではなく、**実行時エラーが発生してマクロが止まってしまう**ことがあるんです。
ここが、VBAでワークシート関数を使うときに一番つまずきやすいポイントです。
たとえば、次のようなコードは検索値が存在すれば問題なく動きます。
でも、A列に該当するコードがない場合、`WorksheetFunction.VLookup`の行でエラーになってマクロが止まってしまう可能性があります。
実際の業務データでは、「絶対に見つかるはず」と思っていた値が、入力ミスやデータ更新のタイミングで見つからないことは珍しくありません。
だからこそ、VLOOKUPをVBAで使うときは、**エラーが起きる前提で書く**ことが大切なんです。
“`vb
Sub BadVLookup()
Dim result As Variant
result = Application.WorksheetFunction.VLookup(“A001”, Range(“A:B”), 2, False)
MsgBox result
End Sub
“`
検索系の関数を安全に使うには、`WorksheetFunction`を使わず、`Application.VLookup`のように直接呼び出す方法がよく使われます。
この書き方だと、検索値が見つからなくてもマクロがすぐに止まるのではなく、戻り値としてエラー値が返ってきます。
その戻り値がエラーかどうかを、VBAの`IsError`関数で判定すればいいんです。
`IsError`は、変数に入っている値がExcelのエラー値かどうかを調べるための関数です。
“`vb
Sub SafeVLookup()
Dim ws As Worksheet
Dim result As Variant
Set ws = ThisWorkbook.Worksheets(“商品マスタ”)
result = Application.VLookup(“A001”, ws.Range(“A:B”), 2, False)
If IsError(result) Then
MsgBox “該当する商品コードが見つかりませんでした。
”
Else
MsgBox “商品名は ” & result & ” です。
”
End If
End Sub
“`
この例では、商品コード`A001`を商品マスタのA列から探して、見つかったらB列の商品名を取得しています。
見つからなかった場合は`IsError(result)`が`True`になるので、マクロを止めずにメッセージを表示できます。
実際の仕事では、見つからないデータをログ用のシートに書き出したり、「該当なし」として空欄を返したりする処理に変更すると、もっと使いやすいマクロになりますよ。
`WorksheetFunction`と`Application`の使い分けを簡単にまとめると、エラーが起きにくい集計系の関数は`WorksheetFunction`でも大丈夫で、検索値が存在しない可能性がある関数は`Application`経由で呼び出して`IsError`で判定するのが安全です。
もちろん、`On Error Resume Next`でエラーを無視する方法もありますが、原因が分かりにくくなるので、初心者の方や保守しやすいコードを書きたい場合は、戻り値を確認する書き方を基本にするのがおすすめです。
実務で失敗しないための使い分けと注意点
VBAからワークシート関数を呼び出す方法を覚えると、コードを短く書けて便利なんですが、すべてを関数任せにすればいいわけではありません。
実際の仕事で安定したマクロを作るには、関数の戻り値、参照範囲、エラー処理、再計算のタイミングをしっかり意識する必要があります。
特にVLOOKUPのような検索処理では、検索範囲の一番左の列に検索キーがあるか、列番号は正しいか、完全一致を指定しているかを確認しなければなりません。
シート上で使うときと同じ制約が、VBAから呼び出す場合にもそのまま当てはまるんです。
VLOOKUPの第4引数には、完全一致なら`False`を指定します。
業務データのコード検索や社員番号検索では、たいてい完全一致を使うことが多いですよね。
ここを省略したり`True`にしたりすると、近似一致として扱われて、意図しない値が返ってくる可能性があります。
マクロは一度作ると何度も実行されるので、たった一つの引数指定ミスが大量の誤処理につながることもあります。
検索処理では、ちょっと冗長に見えても引数をちゃんと書いておくことが大切です。
また、VBAでワークシート関数を使うときは、戻り値の型を`Variant`にしておくと扱いやすい場面があります。
SUMのように必ず数値が返ってくる想定なら`Double`や`Long`でもいいんですが、VLOOKUPのように正常な値だけでなくエラー値が返る可能性がある場合、`Variant`にしておくと`IsError`による判定がしやすくなります。
数値、文字列、エラー値のどれが返ってくるか分からない処理では、最初から`Variant`を使っておくほうが安全です。
関数を呼び出す方法としては、`Evaluate`を使う方法もあります。
`Evaluate(“SUM(A1:A10)”)`のように、文字列として書いた数式をExcelに評価させる方法です。
複雑な数式をそのまま評価したいときには便利なんですが、文字列の中でシート名や範囲を組み立てる必要があって、書き方を間違えるとデバッグしにくくなります。
初心者の方がまず覚えるべきなのは、`Application.WorksheetFunction.Sum`や`Application.VLookup`のように、関数として明示的に呼び出す方法です。
最後に、ワークシート関数をVBAで使うときの実務的な判断基準をまとめておきますね。
– **SUM、AVERAGE、MAX**などの集計系は、`Application.WorksheetFunction`でシンプルに呼び出す
– **VLOOKUP、MATCH**など見つからない可能性がある関数は、`Application`経由で呼び出して`IsError`で判定する
– 対象範囲は`ThisWorkbook.Worksheets(“シート名”).Range(…)`のように明示する
– シート上の数式結果を使う場合は、必要に応じて再計算してから処理する
Excel VBAでワークシート関数を使う方法は、単に`WorksheetFunction`を覚えるだけでは不十分です。
集計系の関数と検索系の関数では、エラーが起きたときの動きが違うので、関数の性質に合わせた呼び出し方を選ぶ必要があります。
SUMのような関数は`WorksheetFunction`で分かりやすく書いて、VLOOKUPのように`#N/A`が起こり得る関数は`Application.VLookup`と`IsError`を組み合わせる。
この使い分けを身につければ、マクロが途中で止まりにくくなって、実際の仕事でも安心して使えるVBAコードが書けるようになりますよ。
広告
