マクロVBAでも通常で使用する関数をプログラムすることができます。
使用頻度の高い合計・平均・カウント・最大値・最小値については個別で記事をまとめました。
対象記事はこちらから☟
【マクロVBA知識 18】合計・総和をSUM・SUMIF・SUMIFSで求める ► 独学エクセル塾 (dokugakuexcel.com)
【マクロVBA知識 19】平均をAVERAGE/AVERAGEIF/AVERAGEIFSで求める ► 独学エクセル塾 (dokugakuexcel.com)
【マクロVBA知識 20】カウントをCOUNT/COUNTIF/COUNTIFSで求める ► 独学エクセル塾 (dokugakuexcel.com)
【マクロVBA知識 21】データの最大値・最小値をMAX.MINを使用して求める ► 独学エクセル塾 (dokugakuexcel.com)
今回はその他の関数をマクロVBAで使用する方法をまとめています。
この知識を身に着けると関数式で組んでいるファイルをVBAで自動化することも可能です。
それでは見ていきましょう。
■ Application.WorksheetFunctionで様々な機能を実行する
・プログラムの構造について
プログラムの構造は下記に示します。
反映・表示先 = Application.WorksheetFunction.関数名(関数にあった引数)
関数の種類はとても多いので一覧は掲載しませんが
Application.WorksheetFunction.を入力した後に表示される関数はすべて使用可能です。
・プログラムを組む
では実際にプログラムを組んでみましょう。
プログラムの組み方は関数の使用方法が理解できていれば簡単です。
裏を返せば関数の使用方法を知っていなければ使用は難しいと思います。
まず、行いたい処理に合った関数を見つけて、使用方法がわからなければ
その関数の理解から進めていきましょう。
今回は見本で「RANK関数」を使用してみたいと思います。
通常のRANK関数はこちらです。
=RANK(数値,参照,順序)
さらにかみ砕いて説明すると下記の通りです。
=RANK(比較元,比較先の範囲,表示の順番)
では下図の見本シートを元に進めていきます。
このように得点表がまとめてあり、今回は各順位を表示してみたいと思います。
ではプログラムを組んでいきます。
まず、Aさんの順位を求めてみましょう。
通常の関数を使えばこのようになります。
=RANK(C3,C3:C13,0)
VBAのプログラムでも考え方は全く同じです。
ではApplication.WorksheetFun.のあとに「RANK」を入力してください。
すると英語ではありますが引数のガイドが表示されました。
まず比較元はAさんの点数なので「Range(“C3”)」です。
次に比較先の範囲はAさんからKさんなので「Range(“C3:C13”)」となります。
最後に降順で表示したいので「0」としましょう。
それではプログラムをつなげてみましょう。
結果はこのようになります。
Sub VBAでワークシート関数を使用()
Cells(3, 4) = Application.WorksheetFunction.Rank(Range(“C3”), Range(“C3:C13”), 0)
End Sub
実行してみましょう。
Aさんの順位が表示されました。
もちろんD3には数式は入力されておらず結果の「3」のみが入力されています。
もちろんVBAなので引数は変数を使用することも可能です。
ではAさんからKさんまでループ処理をしてみましょう。
プログラムはこのようになります。
Sub VBAでワークシート関数を使用()
Dim A As Long
For A = 3 To 13 Step 1
Cells(A, 4) = Application.WorksheetFunction.Rank(Cells(A, 3), Range(“C3:C13”), 0)
Next A
End Sub
実行した結果はこのようになります。
AさんからKさんの順位が自動で表示されるようになりました。
とても便利な機能ですね。
■よくあるエラーと対処法
・コンパイルエラー 修正候補：式
こちらはApplication.WorksheetFunctionに対応していない関数を使用している可能性があります。
たとえば引数①と引数②の相違を確認できる「EXACT関数」を見てみましょう。
「EXACT関数」は候補に含まれていないため使用することができず、エラーになってしまいます。
エラーが生じる際にはリストに対象の関数があるか確認してみてください。
■練習問題
練習問題も別の記事でまとめているので是非参考にしてみてください！
■公式の説明
わかりやすいように説明したため公式と使用する語句が異なりますが
マイクロソフト公式の説明については下記のリンクを参照してください。☟
WorksheetFunction オブジェクト (Excel) | Microsoft Docs
■まとめ
いかがだったでしょうか。
親しみのある関数をマクロVBAで使用できるのはとても便利ですよね。
結果も数式でなく、実行結果が表示されるので
ファイルが重くならない点も１つのメリットです。
ぜひ、Application.WorksheetFunctionを活用して
処理を行ってみてください
次回はマクロVBAで置き換えする方法についてまとめています。記事はこちらから☟
【マクロVBA知識 23】文字・数値の置換と完全一致など様々な置換オプション ► 独学エクセル塾 (dokugakuexcel.com)
それでは次回の記事でお会いしましょう。