VBA(Excel)

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

スポンサーリンク

こんにちは。システム担当の父親、KATSUです。

このブログもおかげさまで月間2000PVまで達成しそうです(2021年1月現在)。

どこかで、記事を読んで業務に活用してくれる人がいることを考えると、うれしいですね♪

今後もより有益な内容を意識して、更新を継続していきます。


ところで、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行で済ませられます。

処理もより高速になります。

とても便利なテクニックですので、一度試してみてはいかがでしょうか?


本日もありがとうございました。

スポンサーリンク

-VBA(Excel)