Excel VBAでワークシート関数を使う

スポンサードリンク

ところで、VBAプログラミングで条件分岐、繰り返し、変数などが使えるようになると、他のテクニックも覚えたくなりますよね？

例えば、SUMやVLOOKUPなどのワークシート関数が使えれば、もっとできることが増えるのではないかと。

他にもグラフやピボットテーブルや・・・。

もちろん、手作業で行っているほとんどの作業はマクロで自動化出来ますが、今回はVBAでワークシート関数を使う方法についてまとめてみました。

VBAでワークシート関数を使うメリット
セルにワークシート関数を直接入力する
ワークシート関数の計算結果のみを反映させる
AccessでExcelのワークシート関数を使う
まとめ

この記事を書いている私は、約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

⑤は結果をセルにそのまま反映し、⑥はいったん変数に格納しています。

他にも、