VBA ワークシート関数のエラー

質問

シートに数式を入れていたものを、ＶＢＡで値のみ入力しようと考えています。
そこで、.Cells(1,1) = WorksheetFunction.数式といった形のメソッドを試しています。
しかし、複雑な数式を記述するとエラーが出てしまいます。
成功 (iferrorというワークシート関数が１つ）
Debug.Print WorksheetFunction.IfError(1 / .Cells(1, 5) + 1 / .Cells(1, 6) + 1 / .Cells(1, 7) - 1, "P")
失敗（ワークシート関数のifとcountifなど複数のものが数式に混入）
Debug.Print WorksheetFunction.If(CountIf(Range("C17:D49"), Range("C29")) > 5, Range("C29"), Range("D29"))
Countifの場所でエラーになります。このcountifを使えるようにするためには、どうすればよいのでしょうか？
WorksheetFunction.if(WorksheetFunction.Countif(、、、、という書き方はダメでした。
数式が汚くてすみません。

宜しくお願いいたします。

回答No.3

real beatin（@realbeatin）

こんにちは。既に正解は出ていますが、行掛り上、お邪魔します。

たぶん、こういうことをなさりたいのかと思います。

　　Debug.Print IIf(WorksheetFunction.CountIf(Range("C17:D49"), Range("C29")) > 5, Range("C29"), Range("D29"))

一応こちらが想定した条件でシートを作成した限りでは、
想定した通りの正しい結果が得られています。

> ... WorksheetFunction.数式といった形のメソッド ...
たぶん、昨夜私が別スレで書いた内容を曲解されているように思うのですが、
参考>> Excel関数の [ =TEXT(値,表示形式)　] を
　　>> worksheetfunction.Text メソッド で呼び出します。

私の言う「メソッド」というのは"様式"等を意味する一般語ではなくて、
ＶＢＡの用語としての「クラス」「メソッド」「プロパティ」等のひとつです。
「worksheetfunction.Text メソッド」と書いてある場合は、
「【worksheetfunction クラス】の【.Text メソッド】」を意味しています。

×　"WorksheetFunction.関数名"のような書式で書けば、
　　Excelの【関数】を、なんでも計算してくれる
のではなく、
○　ＶＢＡの【worksheetfunction クラス】に用意されている【メソッド】
　　を用いて、計算結果を得るのです。
×　【worksheetfunction クラス】に用意されていない【メソッド】
　　を指定しても、それは、エラーになるのが当然の結果です。

○　worksheetfunction.CountIf
CountIf メソッドの使い方はご提示の記述で問題ありません。

×　worksheetfunction.If
If メソッドはないですが、ＶＢＡには、IIf()関数が用意されています。

今回のケースでは、
IIf(worksheetfunction.CountIf(arg1, arg2) > 5. rtnT, rtnF)
のような構文にするのが妥当であろうと思います。

因みに、
●Excelの【数式】自体をＶＢＡで評価した結果を得たい
という目的ならば、appliaction.Evaluate() が、その目的に適うメソッドです。
が、一般的にはあまり使われない手法です。

【worksheetfunction クラス】の【メソッド】
の種類を知るには、
　ＶＢＥ画面上で、[F2] キー
　　→　オブジェクトブラウザを表示
　オブジェクトブラウザの検索窓に「worksheetfunction」と書き込み
　　→　検索ボタン（双眼鏡アイコン）
以上の操作で、添付画像のように、worksheetfunctionクラスのメンバーが表示されます。
各メソッドの仕様を確認するには、
　オブジェクトブラウザ右側のメンバー欄でメソッドを選んでから
　[F1] キー
という手順で、ＶＢＡのヘルプを表示できます。

以上、ご参考まで。

回答No.2

eden3616

No1の回答に記述ミスがあるため訂正いたします。
訂正箇所は countif を閉じていませんでした。

if worksheetfunction.CountIf(Range("C17:D49"), Range("C29") > 5 then
　　　　　↓
if worksheetfunction.CountIf(Range("C17:D49"), Range("C29")) > 5 then

回答No.1

eden3616

worksheetfunctionで使用できるExcel関数には制限があります。
Excel関数の「if」関数はworksheetfunctionのオブジェクトメンバとして対応しておりません。
VBAヘルプの検索窓にて「worksheetfunction オブジェクトメンバ」で検索して頂くとworksheetfunctionで使用できるExcel関数の一覧を見ることが出来ます。

countifはworksheetfunctionで利用できますので、「worksheetfunction.CountIf(Range("C17:D49"), Range("C29")」の記述が可能です。
返り値をもって、VBAのifステートメントで判定してはどうでしょうか。
if worksheetfunction.CountIf(Range("C17:D49"), Range("C29") > 5 then
　　debug.print Range("C29")
else
　　debug.print Range("D29")
end if

どうしてもIF構文を関数式で利用したい場合は以下の方法が有ります。

上記のように対応していない関数式の帰り値をVBAで取得したい場合は「evaluate」を使ってください。
debug.print evaluate("=if(countif(C17:D49,C29)>5,C29,D29)")