Excelの関数には、Visual Basic関数とワークシート関数の2種類があります。
ワークシート関数はワークシートのセル内に入力して使用できる関数で、
VB関数はVBA(マクロ)内で使用できる関数です。
同名の関数でも動作が全く違ったり、似ていても動作が微妙に違ったりします。
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.Math
、VBA.Strings
)
要するに関数が種類毎にグルーピングされているわけですね。
つーか、「VBA」はヘルプに出てこない…これはいったいどういう位置づけなんだろう?
コンテナとかnamespaceなのかなぁ?
しかしMathとかStringsはヘルプに出てきて「オブジェクト ブラウザ」と題されているから、言語仕様やオブジェクトとは無関係の単なるグループ化か?
また、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関数を使うことも出来ます。
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
ほとんど意味が無いような気もしますが(苦笑)
注意! 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みたいな関数があればいいってことだ!…無いよなぁ…。
ちゅーか、どうしてセルで出来る演算が自由にできる関数が無いんだ?
“関数に関して色々ネットで調べている最中に見つけて疑問に思ったもの”をメモしました。
Function func() Dim r As Range Set r = Application.Caller Debug.Print r.Row & "," & r.Column func = "dummy" End Function
Function volatileTest() As Variant Application.Volatile volatileTest = [A1].Value End Function例えばB1セルに「
=volatileTest()
」と入れておく。
(この例の関数は単純にA1セルの値を返すので、B1セルとA1セルが同じ内容になる)=NOW()
」とか入れていると、表示されている日時が新しい値に変わる。対象 | メソッド・プロパティ | 説明 | 備考 |
---|---|---|---|
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のバージョンを返す。 |