S-JIS[2006-06-17/2008-01-14] 変更履歴

ExcelVBAからワークシート関数呼び出し

Excelの関数には、Visual Basic関数とワークシート関数の2種類があります。

ワークシート関数はワークシートのセル内に入力して使用できる関数で、
VB関数はVBA(マクロ)内で使用できる関数です。

同名の関数でも動作が全く違ったり、似ていても動作が微妙に違ったりします。



WorksheetFunction

VBA側から、ワークシート関数を呼び出すことができます。

Applicationのプロパティとして取得できるWorksheetFunctionオブジェクトが、VBAから呼び出せるワークシート関数を保持しています。

Sub test()

	Debug.Print WorksheetFunction.Asc("ABC") '→ABC

	Debug.Print Asc("ABC")		'→-32160
	Debug.Print VBA.Asc("ABC")	'→-32160

End Sub

VB関数は、VBAというキーワードを付けて表すこともできます。でも普通は付けません(笑)

他にMathとかStringsとかのキーワードもあります。(より長く書けば、VBA.MathVBA.Strings
要するに関数が種類毎にグルーピングされているわけですね。

つーか、「VBA」はヘルプに出てこない…これはいったいどういう位置づけなんだろう? コンテナとかnamespaceなのかなぁ?
しかしMathとかStringsはヘルプに出てきて「オブジェクト ブラウザ」と題されているから、言語仕様やオブジェクトとは無関係の単なるグループ化か?


Evaluate

また、VBA側から ワークシート関数名を文字列で指定して呼び出すことも出来ます。
この方法の場合、計算式を文字列で書いて その演算を行うことも出来ます。

Sub eval()

	Debug.Print Evaluate("Asc(""ABC"")")	'→ABC

	Debug.Print Evaluate("1+2")		'→3
	Debug.Print Evaluate("1+max(2,99,3)")	'→100
	Debug.Print Evaluate("""ABC"" & ""DEF""")	'→ABCDEF

End Sub

しかし実際は、計算を行う為にあるのではなくて、セルを取得する為にあるようです。

Sub eval2()

	Debug.Print Evaluate("A1")	'アクティブシートのA1セル(Rangeオブジェクト)が返る→Value省略扱いで、A1セルの内容が表示される

	Debug.Print Sheets(2).Evaluate("A1").Text

End Sub

Evaluateの前に何もつけないとApplicationが指定されたものとして扱われます。そして、対象はアクティブシートとなります。
ワークシートオブジェクトに対して行うと、そのワークシートが対象となります。

さらには、Evaluateは角括弧を使ってちょっと省略した書き方が出来ます。

Sub eval3()

	Debug.Print [A1]	'アクティブシートのA1セル(Rangeオブジェクト)が返る→Value省略扱いで、A1セルの内容が表示される
	Debug.Print Sheets(2).[A1].Text

	Dim expression As String
	expression = "1+2"
	Debug.Print [expression]		'→1+2(そのまま表示される)
	Debug.Print Evaluate(expression)	'→3 (計算される)

End Sub

ここまで来ると、分かりにくいだけって気もしますが(苦笑)


CallByName

ワークシート関数名の文字列を使って呼び出すには、CallByName関数を使うことも出来ます。

Sub testCallByName()

	Dim arg(2) As Variant
	arg(0) = 66
	arg(1) = 77
	arg(2) = 55
	Debug.Print CallByName(WorksheetFunction, "max", VbMethod, arg)	'max(66,77,55)と同じ→77

End Sub

ほとんど意味が無いような気もしますが(苦笑)


FormulaLocal

注意! WorksheetFunctionで使える関数はワークシート関数の全てではありません

例えばLenBというワークシート関数は、WorksheetFunctionオブジェクトでは用意されていません。
こういうときは、Evaluateで代用できる可能性があります。

Sub testLenB()

	'Debug.Print WorksheetFunction.LenB("ABC")	'「実行時エラー'438' メソッドをサポートしていません」のエラー
	Debug.Print Evaluate("LenB(""ABCあいう"")")	'→9 (半角1byte,全角2byte換算)
	Debug.Print LenB("ABCあいう")		'→12(半角も全角も2byte換算)

End Sub

しかしEvaluateでも実行できない関数もあります。例えばJISがそうです。
そうなったら、実際のセルに埋め込んで、その結果をとるしかなさそうです。

Sub testJIS()

	'Debug.Print WorksheetFunction.JIS("ABC")	'「実行時エラー'438' メソッドをサポートしていません」のエラー

	Dim r As Range
	Set r = [A1]	'←わざわざ角括弧を使ってみた(笑)
	r.FormulaLocal = "=JIS(""ABC"")"
	Debug.Print r.Value			'→ABC

End Sub

JISはFormulaでは使えないので、FormulaLocalを使用します。

ということは、Evaluateは環境固有の関数は使えないってことなのかなぁ。
すると、EvaluateLocalみたいな関数があればいいってことだ!…無いよなぁ…。
ちゅーか、どうしてセルで出来る演算が自由にできる関数が無いんだ?


おまけ

“関数に関して色々ネットで調べている最中に見つけて疑問に思ったもの”をメモしました。

Application.Caller [2008-01-14]
関数の呼び出し元のオブジェクトが入る。
セルに埋め込まれた関数の場合、そのセルのRangeオブジェクトが入る。
Function func()
    Dim r As Range
    Set r = Application.Caller
    Debug.Print r.Row & "," & r.Column

    func = "dummy"
End Function
Application.Volatile
自分の関数(自作関数)の中に書く。この関数では、シート内のどこかのセルを参照しているものとする(そうでないと意味が無い)。
すると、ワークシート内で自作関数を使っている場合、シートのどこかに変更があったらそのセルが自動的に再計算される(すなわち、自作関数が呼ばれる)。
Function volatileTest() As Variant
	Application.Volatile

	volatileTest = [A1].Value
End Function
例えばB1セルに「=volatileTest()」と入れておく。 (この例の関数は単純にA1セルの値を返すので、B1セルとA1セルが同じ内容になる)
そしてA1セルを書き換えると、B1セルに表示される内容も変わる。全然関係ないC1セルを書き換えても再計算されるが(苦笑)
Application.Volatileを呼ばない場合、A1セルを書き換えてもB1セルの表示内容は変わらない(ことがある)。
 
Application.Calculate
アプリケーション(ブックのワークシート全部)の再計算を行う。
例えばセルに「=NOW()」とか入れていると、表示されている日時が新しい値に変わる。
Applicationの代わりにRangeに対して行うと、その範囲だけ再計算する。
ただし、自分で定義した関数(ユーザー定義関数)ではVolatileにしておかないと再計算されないようだ。
再計算に関連する関数・プロパティ[2008-01-14]
対象 メソッド・プロパティ 説明 備考
Application
Worksheet
Range
Calculate 指定対象内でExcelが“計算対象”と認識している全セルを再計算する。  
Application CalculateFull 開かれている全ブックの再計算を行う。  
Application CalculateFullRebuild データを全て再計算して、セル間の依存関係を再構築する。  
Range Dirty 指定範囲を再計算する。 Excel2003以降
Application CalculateBeforeSave Trueの場合、ブックの保存前に再計算を行う。 Calculationが「手動」の場合のみ有効
Application Calculation 計算方法のモードを指定する。 xlCalculationAutomatic(自動)
xlCalculationManual(手動)
xlCalculationSemiautomatic
Application CalculationVersion 現在のExcelのバージョンを返す。  
Workbook そのブックが最後に再計算した際のExcelのバージョンを返す。  

参考


Excelへ戻る / 技術メモへ戻る
メールの送信先:ひしだま