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

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

言語依存の関数を使用できる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プロパティで何も問題はありません。
以上で解説を終わります。




同じテーマ「ツイッター出題回答 」の記事

VBAで数値を漢数字に変換する方法

ツイッターで出したVBAのお題です。算用数字を漢数字に変換するVBAです。滅多に必要になるものではないのに、なぜこんな問題を出したかと言うと、最近シリーズで書き始めた「Excel将棋」で必要になったからです。
囲碁で相手の石を囲んで取るアルゴリズム
ツイッターで出したVBAのお題です。Excel囲碁を作っていて、相手の石を囲んで取れるかどうかの判定、相手の石を取るにはどうしたら良いかというもの。囲碁で相手の石をとる ここで、8二に黒を打てば、このように囲まれている白が取られます。
VBAで「3Lと5Lのバケツで4Lの水を作る」を解く
ツイッターでVBAのお題として出したものです。昔からよくある問題です。「3Lと5Lのバケツで4Lの水を作る」これをVBAを使って自動で求めてみようという事です。VBA問題:ツイートの記録 【VBA問題】 「3Lと5Lのバケツで4Lの水を作る」・2つの容器サイズは変えられるように引数で受け取る (3,5,
言語依存の関数を使用できるFormulaLocal
配列のUBoundがLBoundがより小さいことはあり得るか
ツイッターでVBAのお題として出したものです。配列の下限が上限より大きくなるような配列は存在するかの問題です。LBound(ary)>UBound(ary) この条件を満たすような配列は存在するか? 問題を出したツイート 配列の下限と上限を調べるにはLBound関数とUBound関数を使います。
コレクションの要素を削除する場合
ツイッターで出したVBAのお題(投票)です。Collectionから要素を順に削除するVBAの正誤問題です。問題を出したツイート 【VBA問題】 DimcAsNewCollection Dimi Fori=1To100 c.Addi,CStr(i) Next Fori=1To100 c.Remove□ Next 四…
greeenはgreenに、greeeeeNをGReeeeNに変換
ツイッターで出したエクセルの入力規則のお題です。「greeenはgreenに、greeeeeNやGReeeeeenはGReeeeNに直す」文字列操作のVBA問題です。問題を出したツイート 【VBA問題】 greenは緑です。
数値変数の値を別の変数を使わずに入れ替える
ツイッターで出したエクセルVBAのお題です。数値が入っている3つの変数を、他の変数を使わずに値を入れ替えるという問題です。問題を出したツイート 【VBA問題】 変数a,b,cに整数値が入っています。これをa>b>cとなるように値を入れ替えてください。
Rangeオブジェクトを受け取り"行数,列数"で埋める
ツイッターで出したエクセルVBAのお題です。Rangeオブジェクトを受け取り、"行数,列数"の値(数式ではなく値)で埋めるVBAを書く問題です。問題を出したツイート 【VBAお題】 Rangeオブジェクトを受け取り、添付のように"行数,列数"の値(数式ではなく値)で埋めてくだ…
数式の関数の使用回数、関数名を配列で返す
ツイッターで出したエクセルVBAのお題です。セルに入っている計算式に使われている関数(ユーザー定義含む)を数えてみましょう、というお題になります。問題を出したツイート 【VBAお題】セルに計算式が入っています。
日付型と通貨型のValueとValue2について
ツイッターでVBAに関する問題を出しました。問題としてはValueとValue2について問うもので、日付型と通貨型が問題の焦点になります。https://twitter.com/yamaoka_ss/status/1408259798357794817 VBA問題の出題ツイート 【VBA問題】 Rangeに対して、


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

エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.繰り返し処理(For Next)|VBA入門
6.RangeとCellsの使い方|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.セルのクリア(Clear,ClearContents)|VBA入門
9.並べ替え(Sort)|VBA入門
10.メッセージボックス(MsgBox関数)|VBA入門




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


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


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