ところで、VBAプログラミングで条件分岐、繰り返し、変数などが使えるようになると、他のテクニックも覚えたくなりますよね？
例えば、SUMやVLOOKUPなどのワークシート関数が使えれば、もっとできることが増えるのではないかと。
他にもグラフやピボットテーブルや・・・。
もちろん、手作業で行っているほとんどの作業はマクロで自動化出来ますが、今回はVBAでワークシート関数を使う方法についてまとめてみました。
この記事を書いている私は、約10年前にVBAを使い始め、ずっとExcel作業の効率化を進めてきました。
VBAを使ってきた約10年間で実感したことは、VBAを書く時にワークシート関数がたまに必要になります。
ワークシート関数をVBAで使う便利さと方法を知っていただければ、必ずマクロ作りに役立ちます。
VBAでワークシート関数を使うメリット
ワークシート関数をExcelVBAで使うメリットには、以下のことが挙げられます。
- プログラムコードの行数を大幅に減らせる
- 高速で処理できる
Excelには400種類以上の関数があります。
通常のExcel操作でも、関数を使えば複雑な計算を一瞬で行うことが出来ます。
一方、マクロを使えばワンクリックで自動処理が可能です。
マクロを作る時は、一般に「条件分岐」や「繰り返し」などのフロー(流れ)を組み立てます。
複雑なフローになると、数十行の長いコード文になってしまいます。
時には処理が完了するのに数秒ほどかかる場合もあります。
ところが、フローでは何行もかかる処理が、ワークシート関数をVBAに活用すると1行で済んでしまいます。
プログラムを実行すれば一瞬で処理が完了します。
ワークシート関数を使うことで、「コードを書く時」と「マクロを実際に使う時」の両方で役に立ちます。
また、VBAでのワークシート関数の使い方は2通りに分けられます。
- セルの直接数式を入力する場合
- 計算結果のみを何かに反映させる場合
この2種類の使い方についてこれから紹介していきます。
セルにワークシート関数を直接入力する
ワークシートにA～F列に商品マスタが、G列に商品CDがランダムに表示されています。
H列の商品名にマクロでVLOOKUP関数を挿入し、マスタからデータを取り出してみます。
Sub Vlookup_Func()
'セルに関数を入れる場合
'①そのまま式を入力 "=VLOOKUP(G2,A1:E8,2,0)"
Range("H2").Value = "=VLOOKUP(G2,A1:E8,2,0)"
'②Formulaプロパティ "=VLOOKUP(G3,A1:E8,2,0)"
Range("H3").Formula = "=VLOOKUP(G3,A1:E8,2,0)"
'③FormulaR1C1プロパティの絶対参照 "=VLOOKUP($G$4,$A$1:$E$8,2,0)"
Range("H4").FormulaR1C1 = "=VLOOKUP(R4C7,R1C1:R8C5,2,0)"
'④FormulaR1C1式の相対参照 "=VLOOKUP(G5,A1:E8,2,0)"
Range("H5").FormulaR1C1 = "=VLOOKUP(RC[-1],R[-4]C[-7]:R[3]C[-3],2,0)"
End Sub
実際にマクロを実行してみると、商品CDに相当する商品名が追加されるのが分かります。
ここでは4種類のコードを例として挙げましたが、それぞれコメントに記載している数式が入力されます。
①と②は同じ結果を出力します。③と④はR1C1方式でセル位置を指定します。
セルの範囲が決まっているのなら①や②の方法でも問題ありませんが、VBAを使って数式を作る場合は、指定するセル範囲が都度変わることを想定する必要があります。
③や④の方法に慣れておくと、セルの範囲を変数で自由に設定できるのでオススメです。
ワークシート関数の計算結果のみを反映させる
次はI列に品種を表示させます。
以下のコードを実行すると、セルには計算結果のみがセルに入力されます。
Sub Vlookup_WSFunc()
'マクロの中でワークシート関数を使う
'⑤結果のみをセルに計算結果を表示する
Range("I2").Value = WorksheetFunction.VLookup(Cells(2, 7), Range("A1:E8"), 3, 0)
'⑥一旦変数に計算結果を格納する
Dim st1 As String
st1 = WorksheetFunction.VLookup(Cells(3, 7), Range("A1:E8"), 3, 0)
Range("I3").Value = st1
End Sub
⑤は結果をセルにそのまま反映し、⑥はいったん変数に格納しています。
他にも、
- ユーザーフォームのテキストボックスに結果を反映させる
- 複数の変数を使って更に計算させる
などの処理を行うときにも、worksheet.functionプロパティを使うと便利です。
AccessでExcelのワークシート関数を使う
Accessのテーブルデータを加工し、Excelでレポートとして出力させる場合にも、ワークシート関数を活用できます。
小計などの簡単な計算をはさむことで、レポートの完成度が高まります。
Accessのレポート機能ではなく、Excelに出力させて計算結果も付け加えておくと、出力後にワークシートの編集を行えます。
Accessを使用しているユーザーにとって、出力結果をExcelブック上で手直しできるのはかなり便利に感じてもらえます。
こちらの解説については、後日紹介させていただきます。
また、Accessだけでなく、VB.NETにもExcelを操作できる機能がありますので、関数を挿入できます。
職場のPCにAccessをインストールしていない場合でも、Excelと連携するための選択肢になります。
まとめ
今回は、Excel VBAでワークシート関数を操作する方法を紹介しました。
Excelの主な機能である関数の入力は、もちろんVBAでも活用できます。
プログラミングの基本だけでは複雑になってしまう処理を、ワークシート関数を使えば1行で済ませられます。
処理もより高速になります。
とても便利なテクニックですので、一度試してみてはいかがでしょうか？
本日もありがとうございました。