エクセル雑感
言語依存の関数を使用できるFormulaLocal

ExcelマクロVBAとエクセル関数についての私的雑感
最終更新日:2020-08-31

言語依存の関数を使用できるFormulaLocal


ツイッターでVBAのお題として出したものです。


複数セルに一括で数式を入れるバ宇井の記述と、
言語環境に依存する関数をセルに設定する場合のFormulaプロパティの使い方についての問題です。

問題を出したツイート

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

VBA マクロ 言語依存 FormulaLocal
https://twitter.com/yamaoka_ss/status/1300005324812738562

回答のツイート

VBA マクロ 言語依存 FormulaLocal
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プロパティで何も問題はありません。
以上で解説を終わります。



同じテーマ「エクセル雑感」の記事

100桁の正の整数値の足し算
「VBA Match関数の限界」についての誤解
VBAで数値を漢数字に変換する方法
囲碁で相手の石を囲んで取るアルゴリズム
VBAで「3Lと5Lのバケツで4Lの水を作る」を解く
言語依存の関数を使用できるFormulaLocal
配列のUBoundがLBoundがより小さいことはあり得るか
ショートカット(Ctrl+Shift+n)抜け番ばどれだ
コレクションの要素を削除する場合
入力規則で○△を入れる数を制限する方法
greeenはgreenに、greeeeeNをGReeeeNに変換


新着記事NEW ・・・新着記事一覧を見る

import文(パッケージ・モジュールのインポート)|Python入門(9月24日)
例外処理(try文)とexception一覧|Python入門(9月23日)
リスト内包表記|Python入門(9月22日)
Pythonの引数は参照渡しだが・・・|Python入門(9月21日)
lambda(ラムダ式、無名関数)と三項演算子|Python入門(9月20日)
関数内関数(関数のネスト)とスコープ|Python入門(9月18日)
関数の定義(def文)と引数|Python入門(9月18日)
組み込み関数一覧|Python入門(9月17日)
辞書(dict型)|Python入門(9月16日)
入力規則への貼り付けを禁止する|VBA技術解説(9月16日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ