ExcelVBAからWorksheet関数を呼び出す

VBAからワークシート関数を呼び出すことが出来る
(Applicationは省略可)

Application.WorksheetFunction.関数名(引数)

1. SUM関数

WorksheetFunction.Sum(セル範囲)
WorksheetFunction.Sum(Range("A1:A5")) '例

2. COUNTIF関数

WorksheetFunction.CountIf(セル範囲, 条件)
WorksheetFunction.CountIf(Range("A1:A5"), "佐藤")) '例

3. SUMIF関数

WorksheetFunction.SumIf(条件範囲, 条件, 合計範囲)
WorksheetFunction.SumIf(Range("A1:A5"), "佐藤", Range("B1:B5")) '例

4. LARGE関数/SMALL関数

WorksheetFunction.Large(セル範囲, 順位)
WorksheetFunction.Small(セル範囲, 順位)
WorksheetFunction.Large(Range("A1:A5"), 3) '例:3番目に大きいセルの値
WorksheetFunction.Small(Range("A1:A5"), 3) '例:3番目に小さいセルの値

5. MAX/MIN関数

WorksheetFunction.Max(セル範囲)
WorksheetFunction.Min(セル範囲)
WorksheetFunction.Max(Range("A1:A5")) '例
WorksheetFunction.Min(Range("A1:A5")) '例

6. SUBTOTAL関数

WorksheetFunction.SubTotal(計算方法,セル範囲)
WorksheetFunction.SubTotal(3, Range("A1:A5")) '例:COUNTA

【第一引数の計算方法】

1	AVERAGE
2	COUNT
3	COUNTA

AutoFilterで抽出した結果をカウントするにはSUBTOTAL関数を使用する

7. EOMONTH関数

WorksheetFunction.EoMonth(開始日, 月)
WorksheetFunction.EoMonth(Now(), 0)  '例:今月末
WorksheetFunction.EoMonth(Now(), -1) '例:先月末
WorksheetFunction.EoMonth(Now(), 1)  '例:来月末

8. VLOOKUP関数

WorkseetFunction.VLookup(検索値, セル範囲, 列, 完全一致or部分一致)
WorksheetFunction.VLookup("佐藤", Range("A1:D5"), 3, False) '例:Falseは完全一致

9. MATCH関数
セル範囲の上から何番目かを返す

WorksheetFunction.Match(検索値, セル範囲, 照合の種類)
WorksheetFunction.Match("佐藤", Range("A1:A5"), 0) '例

10. INDEX関数
セル範囲の行番号の位置にあるセルの値を返す

WorksheetFunction.Index(セル範囲, 行番号)
WokrsheetFunction.Index(Range("A1:A5"), 3) '例

VLOOKUP関数と同様の動きをするもの

VLOOKUP関数は実務での使用頻度が高いです。
(1) VLOOKUP関数

WorksheetFunction.VLookup(Range("D1"), Range("A1:B10"), 2, False)

(2) MATCH関数 + INDEX関数
検索値より左側にある値を取得したい場合は、MATCH関数とINDEX関数を組み合わせて使う。

Dim num as Long
num = WorksheetFunction.Match(Range("D1"), Range("A1:A10"), 0)
Index(Range("B1:B10"), num)

(3) Findメソッド + Offsetプロパティ
VBAのメソッドやプロパティだけで再現するとFindメソッドとOffsetプロパティを使う。

Dim myRng as Range
Set myRng = Range("A1:A10").Find(What:=Range("D1"), LookAt:=xlWhole)
myRng.Offset(0, 1).Value