VBAでの複雑な関数の作動方法

質問

VBAのプログラミングで関数「=LEN($A$1)-LEN(SUBSTITUTE($A$1,"1",""))」を反映する方法について質問です。

以下プログラミングなら作動するのですが

Sub Sample()
Dim Wb As Workbook
For Each Wb In Application.Workbooks
Wb.Worksheets("Sheet1").Range("B1").Formula = "=LEN(A1)"
Next Wb
End Sub

上記のような複雑な関数になると「"」のせいなのかエラーが出て動作しません。
VBAプログラミングでは作動不可能なんでしょうか？

回答No.2

imogasi

１回限りの確定で値を出してよいのなら（データが変わり連動を考えなくてよいのなら）
ActiveWorkbook.Worksheets("Sheet1").Range("B2") = Len(Range("A2")) - Len(Replace(Range("A2"), "1", ""))
のようにすることもできます。
引数のセル番地の表現がＲａｎｇｅ（”Ａ１”）かＣｅｌｌｓ（１，１）式に変わること。
エクセル関数ではあるが、ＶＢＡでは別の関数を使うべきことがあること
関数でよく行われる、式の複写の変わりに、繰り返し処理をするように、式を組み立てるので、絶対番地かどうかは、ほとんどの場合気にしないでよい
などがあります。

回答No.1

n-jun

マクロの自動記録を用いて数式バーから代入すると、

ActiveCell.FormulaR1C1 = "=LEN(R1C1)-LEN(SUBSTITUTE(R1C1,""1"",""""))"
となります。
「"」の所を見比べてみると、
・数式内の"は""
・数式内の""は""""
となってます。