- エクセルマクロで使える関数について具体的に知りたい！
- エクセルマクロと通常のエクセルの関数はどう違うのだろう？
そんな思いにお応えできるようこの記事では、
エクセルマクロで使うことのできる関数
について解説しています。
エクセルマクロでは関数と言ってもいくつか種類があるので、違いをよく理解しましょう。
エクセル関数については下記にまとめ記事がありますので、こちらもご参考下さい。
【一覧有】エクセルマクロで使える関数
エクセルマクロで使用できる関数は大きく分けると3つです。
- ワークシート関数
- VBA関数
- 自分で作る関数
それぞれについて解説していきます。
ワークシート関数とは？
普段のエクセルで使っているのが、このワークシート関数です。
例えばAVERAGE関数やSUM関数など使ったことがありますよね。このように通常のエクセルで使用する関数をVBAでも使用することができます。
例えば、SUM関数を使ってA1セルとA2セルを合計したいのであれば
WorksheetFunction.Sum(Range("A1:A2"))
という記述となります。
既に理解している関数をそのまま使うことができるので非常に便利ですね。
例えば、B4セルにB2セルの値とB3セルの値の合計を表示させたいとします。
このときtestという名前のマクロを作成したとすると、下記のようなコードで記述できます。
Sub test()
Range("B4").Value = WorksheetFunction.Sum(Range("B2:B3"))
End Sub
WorkSheetFunction.関数名
という記載をするだけなので、とても簡単です。
ワークシート関数として使える関数はあまりに多いため、通常でもよく使うものに絞って一覧にしました。
そもそもエクセル関数の基本について知りたいという方は、下記の記事もご参考下さい。
【PDFダウンロード可】厳選！エクセル実務でよく使う関数一覧
| ワークシート関数名 | 機能 | VBAでの記述例 |
| AVERAGE関数 | 引数に指定した値の平均値を返してくれる | WorksheetFunction.Average(Range("A1:A3")) |
| COUNT関数 | 引数に指定したセルの値に数値が入力されているセルの個数を返してくれる | WorksheetFunction.Count(Range("A1:A3")) |
| COUNTA関数 | 引数に指定したセルの値が空欄でないセルの個数を返してくれる | WorksheetFunction.CountA(Range("A1:A3")) |
| COUNTIF関数 | 検索条件に一致するセルの個数を返してくれる | WorksheetFunction.CountIf(Range("A1:A3"), ">= 400") |
| COUNTIFS関数 | 複数の検索条件に一致するセルの個数を返してくれる | WorksheetFunction.CountIfs(Range("A1:A3"), ">= 400", Range("B1:B3"), ">= 300") |
| IFERROR関数 | エラーの場合に引数に指定した値を返してくれる | WorksheetFunction.IfError(Range("A1"), "エラーです") |
| ROUND関数 | 引数に指定の値を指定の桁数で四捨五入した値を返してくれる | WorksheetFunction.Round(Range("A1"), 1) |
| SUBSTITUTE関数 |
引数に文字列・検索文字列・置換文字列を指定。検索文字列を置換した文字列を返してくれる |
WorksheetFunction.Substitute(Range("A1"), "エクセル", "ワード") |
| SUBTOTAL関数 | 引数に指定した集計方法で、指定の範囲を集計した結果を返してくれる | WorksheetFunction.Subtotal(9, Range("A1:A3")) |
| SUMPRODUCT関数 | 引数に指定した配列要素の積を合計した結果を返してくれる | WorksheetFunction.SumProduct(Range("A1:A3"), Range("B1:B3")) |
| MAX関数 | 引数に指定した範囲の最大値を返してくれる | WorksheetFunction.Max(Range("A1:A3")) |
| MIN関数 | 引数に指定した範囲の最小値を返してくれる | WorksheetFunction.Min(Range("A1:A3")) |
| SUM関数 | 引数に指定した値の合計値を返してくれる | WorksheetFunction.Sum(Range("A1:A3")) |
| SUMIF関数 | 検索条件に一致するセルの合計値を返してくれる | WorksheetFunction.SumIf(Range("A1:A3"), "=りんご", Range("B1:B3")) |
| SUMIFS関数 | 複数の検索条件に一致するセルの合計値を返してくれる | SumIfs(Range("C1:C3"), Range("A1:A3"), "青森", Range("B1:B3"), "りんご") |
| VLOOKUP関数 | 引数に指定した検索値・列数に一致する値を返してくれる | WorksheetFunction.VLookup(Range("A1"), Range("C1:D3"), 2, False) |
この一覧からも分かるように、実務で使う多くの関数はVBAのワークシート関数としてそのまま使うことができるのです。
VBA関数とは？
VBA関数とは、あらかじめ準備されているVBAで使うための関数のことです。
- LEFT
- ROUND
などワークシート関数と同じ名前のものも多くありますが、使い方や機能が異なることもあるので注意しましょう。
VBA関数の使い方は、
関数名(引数)
という形で使用するのが基本です。
例えば文字列の文字数を返してくれるLen関数について考えます。
B2セルの文字列の文字数を取得し、B3セルに入力したいとします。
この場合、Len関数を使って下記のようなコード記述となります。
Sub test()
Range("B3").Value = Len(Range("B2").Value)
End Subこのマクロを実行すると、B2セルの文字数である5がB3セルに表示されます。
VBA関数も膨大な量がありますので、よく使うものに絞って一覧にしました。
ここでは詳しい使い方は解説していませんが、ワークシート関数と似ているものもあれば、逆に見たこともないような関数もあると思います。
| VBA関数名 | 機能 |
| Dir | 引数に指定した属性と一致するファイル名やフォルダ名を返してくれる |
| Format | 引数に指定した値を指定した書式に変換した文字列として返してくれる |
| InStr | 引数に指定した文字列が指定した文字列の中で出現する位置を返してくれる |
| IsDate | 式を日付に変換できるかどうかを判定した結果を返してくれる |
| Left | 引数で指定した文字列から指定した文字数分を左から抽出した結果を返してくれる |
| Len | 引数で指定した文字列の文字数を返してくれる |
| Mid | 引数で指定した文字列から指定した開始位置・指定した文字数分を抽出した結果を返してくれる |
| MsgBox | 引数で指定した値をメッセージボックスとして表示してくれる |
| Replace |
引数で指定した文字列から指定の文字を検索し、指定の別の文字に置き換えした結果を返してくれる |
| Right | 引数で指定した文字列から指定した文字数分を末尾から抽出した結果を返してくれる |
自分で作る関数とは？
VBAでは自分で関数を作ることができます。ワークシート関数やVBA関数で準備されているものは基本的にそちらを使うべきですが、特定の動作や計算を繰り返したいときなどに関数にしておくと非常に便利です。
実用的なマクロではなく関数を作る必要もないのですが、分かりやすいようにあえて具体的に関数を作ってみます。
実行すると、「Hello! + A1セルの値」がメッセージで表示される
というような関数を作りたいとします。
関数を作る場合、Functionプロシージャを使います。
今回の内容の場合、
- A1の値を引数とする
- Hello!に続いて引数の値をメッセージボックスに表示する
という内容となるため下記のような関数を作りました。※今回はhelloという関数名とした
Function hello(str As String)
MsgBox "Hello!" & str
End Function関数名はhelloで、文字列型の引数が1つ必要であることが分かります。
また、内容を見ると「Hello!」という文字列に続いて引数の文字列(str)がメッセージボックスに表示されるということが分かります。
引数にA1セルの値を渡してこの関数を使えばHello!に続いてA1セルの値を表示させることができますね。
testというSubプロシージャ内で、作成したhello関数を使うとすると下記のようになります。
Sub test()
hello Range("A1").Value
End Sub
------------------------------------------------------
Function hello(str As String)
MsgBox "Hello!" & str
End Functiontestプロシージャの中身はシンプルに、hello関数にA1セルの値を渡して実行しているだけです。
当然、A1セルの値を変えるとメッセージに表示される値も変わります。
実際は今回のようなコードでわざわざ関数を作る必要はありませんが、「関数を作る」というイメージを少しでもご理解頂ければと思います。
まとめ
