ツイッター出題回答
言語依存の関数を使用できるFormulaLocal

A1:A10セルに半角の英数文字が入っているので、これを全角で表示するためにB1:B10セルに数式をVBAで設定しようとしました。
Range("B1:B10") = "=jis(A1)"
さて、このVBAで正しく数式は設定されるでしょうか?
正しく設定されないとしたら、どのように直したらよいでしょうか?
・全て正しく設定される
・A1セルのみ正しく設定される
・全て正しく設定されない

https://twitter.com/yamaoka_ss/status/1300005324812738562

https://twitter.com/yamaoka_ss/status/1300421884970893318
連続ツイートしていますので、全てを読む場合は順次辿ってください。
ただし解説については、以下により詳細に書いておきました。

正解は、「全て正しく設定されない」です。
Range("B1:B10") = "=jis(A1)"
このVBAを実行した結果は、
B1=@jis(A1)
B2=@jis(A2)
・・・
このような結果になってしまいます。
正しく設定できないと思った方の中でも、そもそもこのように1行のVBAで設定できることを知らずに選んだ人もいるかもしれませんね。
複数セルに同一数式を設定する場合は、
Range("セル範囲") = "=先頭セルの数式"
これで一括で設定できます。
例えば、今回は全角にするものでしたが、半角にするASC関数なら、
Range("B1:B10") = "=ASC(A1)"
これでB1:B10に全て正しく数式が入ります。
絶対参照の$を付けていない相対参照であれば、各セルの数式は入力したセルからの相対位置としてのセル参照になって正しく数式が入ります。
これは手動で数式を入れるときに、複数セルを選択して数式を入れて「Ctrl + Enter」する場合のVBAになります。
したがって、VBAの書き方としては問題ありません。
では、どこに問題があるかというと、、、
それは、JIS関数そのものです。
この関数は日本独自の関数です。
英語版では関数名が変わります。
（他の国でもJIS関数が使われている国も存在するようですが少数なはずです。）
このような言語環境によって変化するような関数をVBAでセルに入れる場合は、上記のVBAでは正しく入れることができません。
上記VBAの結果は「#NAME?」となってしまいます。
Range("B1:B10") = "=jis(A1)"
これはプロパティを指定していないので、Valueプロパティに入れていることになります。
数式を入れるプロパティとしては、
Value
Formula
FormulaR1C1
これらがありますが、これらのプロパティはいずれも言語環境を考慮しません。
したがって、言語に依存するJIS関数を入れる場合、これらのプロパティを使ったのでは正しく入れることができません。
JIS関数のような言語環境に依存する関数をVBAで入れる場合は、
Localのついたプロパティを使います。
FormulaLocal
Formula2Local
FormulaR1C1Local
Formula2R1C1Local
※Formula2はスピル用に用意されたプロパティです。
JIS関数の場合は、このLocalの付いたプロパティを使う必要があります。
Range("B1:B10").FormulaLocal = "=JIS(A1)"
Range("B1:B10").FormulaR1C1Local = "=JIS(RC[-1])"
これでB1:B10に正しく数式が入ります。
スピルさせるなら、
Range("B1").Formula2Local = "=JIS(A1:A10)"
B2からB10セルはスピルして結果が出力されます。
ただし、このような関数は極めて稀です。
普通に良く使われる関数でこれらを気にする必要は全くありません。
実際にJIS関数以外で何があるかと問われても答えに窮してしまいます。
詳しく調べればいくつか出てくるはずですが、ぱっと思い浮かぶのは、
YEN関数
DOLLAR関数
このあたりでしょうか。
これらの関数は、数値を指定した桁数に四捨五入した通貨書式の文字列に変換します。
VBAでValueまたはFormulaプロパティにこれらの関数を入れた場合は、
YENは=@yenに変換されて「#NAME?」になってしまいますし、DOLLERはYENに変換されてしまいます。
ここまで見てくると、ある疑問が浮かんできます。
シートでは、これらの関数は普通に入れられますよね、ではマクロの記録は、、、
マクロの記録を実際にやってみましょう。
JIS関数
→ActiveCell.FormulaR1C1 = "=DBCS(RC[-1])"
YEN関数
→ActiveCell.FormulaR1C1 = "=DOLLAR(RC[-1],0)"
DOLLAR関数
→ActiveCell.FormulaR1C1 = "=USDOLLAR(RC[-1],2)"
関数名が変換されています。
WorksheetFunctionにはJISもYENもありません。
マクロの記録で変換された関数は、結果としてWorksheetFunctionにある関数に変換されています。
JIS→DBCS
YEN→DOLLAR
DOLLAR→USDOLLA
マクロの記録で作成されたVBAはFormulaプロパティにこの変換後の関数を入れています。
Formulaプロパティに入れられるものはValueプロパティにも入れられます。
つまり、
Range("B1:B10") = "=DBCS(A1)"
このVBAの実行結果は、B1:B10にJIS関数が正しく入ります。
シートの関数とVBAでValueまたはFormulaに設定する関数の関係は、
シート ⇔ VBA
JIS ⇔ DBCS
YEN ⇔ DOLLAR
DOLLAR ⇔ USDOLLA
これを意識してVBAを書くようなことはありえないと思いますが、
マクロ記録したVBAを見た時に戸惑う事はありえるかもしれません。
まとめると、最初の数式、
Range("B1:B10") = "=jis(A1)"
これを正しく書き直すと、
Range("B1:B10").FormulaLocal = "=JIS(A1)"
Range("B1:B10").FormulaR1C1Local = "=JIS(RC[-1])"
Range("B1").Formula2Local = "=JIS(A1:A10)"
Range("B1").Formula2R1C1Local = "=JIS(R1C[-1]:R10C[-1])"
Range("B1:B10").Value = "=DBCS(A1)"
このようにいろいろな書き方があります。
ただし繰り返しになりますが、ほとんどの数式はValueまたはFormulaプロパティで何も問題はありません。
以上で解説を終わります。

同じテーマの記事
新着記事
アクセスランク