言語依存の関数を使用できるFormulaLocal
ツイッターでVBAのお題として出したものです。
言語環境に依存する関数をセルに設定する場合のFormulaプロパティの使い方についての問題です。
問題を出したツイート
Range("B1:B10") = "=jis(A1)"
さて、このVBAで正しく数式は設定されるでしょうか?
正しく設定されないとしたら、どのように直したらよいでしょうか?
・全て正しく設定される
・A1セルのみ正しく設定される
・全て正しく設定されない

https://twitter.com/yamaoka_ss/status/1300005324812738562
回答のツイート

https://twitter.com/yamaoka_ss/status/1300421884970893318
ただし解説については、以下により詳細に書いておきました。
詳細解説
このVBAを実行した結果は、
B1=@jis(A1)
B2=@jis(A2)
・・・
このような結果になってしまいます。
複数セルに同一数式を設定する場合は、
Range("セル範囲") = "=先頭セルの数式"
これで一括で設定できます。
Range("B1:B10") = "=ASC(A1)"
これでB1:B10に全て正しく数式が入ります。
絶対参照の$を付けていない相対参照であれば、各セルの数式は入力したセルからの相対位置としてのセル参照になって正しく数式が入ります。
これは手動で数式を入れるときに、複数セルを選択して数式を入れて「Ctrl + Enter」する場合のVBAになります。
では、どこに問題があるかというと、、、
それは、JIS関数そのものです。
英語版では関数名が変わります。
(他の国でもJIS関数が使われている国も存在するようですが少数なはずです。)
このような言語環境によって変化するような関数をVBAでセルに入れる場合は、上記のVBAでは正しく入れることができません。
上記VBAの結果は「#NAME?」となってしまいます。
これはプロパティを指定していないので、Valueプロパティに入れていることになります。
数式を入れるプロパティとしては、
Formula
FormulaR1C1
したがって、言語に依存するJIS関数を入れる場合、これらのプロパティを使ったのでは正しく入れることができません。
JIS関数のような言語環境に依存する関数をVBAで入れる場合は、
Localのついたプロパティを使います。
Formula2Local
FormulaR1C1Local
Formula2R1C1Local
※Formula2はスピル用に用意されたプロパティです。
Range("B1:B10").FormulaR1C1Local = "=JIS(RC[-1])"
スピルさせるなら、
Range("B1").Formula2Local = "=JIS(A1:A10)"
B2からB10セルはスピルして結果が出力されます。
普通に良く使われる関数でこれらを気にする必要は全くありません。
詳しく調べればいくつか出てくるはずですが、ぱっと思い浮かぶのは、
YEN関数
DOLLAR関数
このあたりでしょうか。
これらの関数は、数値を指定した桁数に四捨五入した通貨書式の文字列に変換します。
YENは=@yenに変換されて「#NAME?」になってしまいますし、DOLLERはYENに変換されてしまいます。
ここまで見てくると、ある疑問が浮かんできます。
シートでは、これらの関数は普通に入れられますよね、ではマクロの記録は、、、
マクロの記録を実際にやってみましょう。
→ActiveCell.FormulaR1C1 = "=DBCS(RC[-1])"
→ActiveCell.FormulaR1C1 = "=DOLLAR(RC[-1],0)"
→ActiveCell.FormulaR1C1 = "=USDOLLAR(RC[-1],2)"
マクロの記録で変換された関数は、結果としてWorksheetFunctionにある関数に変換されています。
YEN→DOLLAR
DOLLAR→USDOLLA
Formulaプロパティに入れられるものはValueプロパティにも入れられます。
つまり、
Range("B1:B10") = "=DBCS(A1)"
このVBAの実行結果は、B1:B10にJIS関数が正しく入ります。
JIS ⇔ DBCS
YEN ⇔ DOLLAR
DOLLAR ⇔ USDOLLA
マクロ記録したVBAを見た時に戸惑う事はありえるかもしれません。
Range("B1:B10") = "=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プロパティで何も問題はありません。
以上で解説を終わります。
同じテーマ「ツイッター出題回答 」の記事
VBAで数値を漢数字に変換する方法
囲碁で相手の石を囲んで取るアルゴリズム
VBAで「3Lと5Lのバケツで4Lの水を作る」を解く
言語依存の関数を使用できるFormulaLocal
配列のUBoundがLBoundがより小さいことはあり得るか
コレクションの要素を削除する場合
greeenはgreenに、greeeeeNをGReeeeNに変換
数値変数の値を別の変数を使わずに入れ替える
Rangeオブジェクトを受け取り"行数,列数"で埋める
数式の関数の使用回数、関数名を配列で返す
日付型と通貨型のValueとValue2について
新着記事NEW ・・・新着記事一覧を見る
新旧マスタの差異比較|Power Query(M言語)入門(2023-02-28)
有効な最新単価の取得|Power Query(M言語)入門(2023-02-26)
有効な最新単価の取得|Power Query(M言語)入門(2023-02-21)
グルーブ内の最小・最大|Power Query(M言語)入門(2023-02-17)
2つのテーブルのマージ|Power Query(M言語)入門(2023-02-15)
「売上」が数値の行のみ取り込む|Power Query(M言語)入門(2023-02-13)
A列のヘッダー名を変更する|Power Query(M言語)入門(2023-02-11)
CSVのA列が日付の行だけを取り込む|Power Query(M言語)入門(2023-02-10)
列数不定のCSVの取り込み|Power Query(M言語)入門(2023-02-09)
別ブックの最終シートの取り込み|Power Query(M言語)入門(2023-02-08)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロって何?VBAって何?|VBA入門
7.並べ替え(Sort)|VBA入門
8.Excelショートカットキー一覧|Excelリファレンス
9.エクセルVBAでのシート指定方法|VBA技術解説
10.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。