エクセルでマクロを右クリックメニューに登録するとCSVのデータに対応できない。

エクセルで　指数表示された数字を　有効数字4桁のSI接頭辞表示（1.234E9を1.234G、1.234E-9を1.234n　など）と変換するマクロを開発しています。　この時　変換したい数字の書かれたセルを選択し、右クリックメニューからマクロを呼び出す　→　変換した数字がメッセージボックスにポップアップという仕様にしたいのです。
　現在のところ、エクセルを立ち上げて　数字を記載したものについては　動作するマクロを開発することができましたが、　数字が記載されたCSVデータを読み込んだ場合、　CSVに記載されてあったデータについては、右クリックメニューにマクロが出てこない」というバグがあることがわかりました。

　下に右クリックでマクロをメニューに呼び出すマクロを記載します。

Sub Engineering()
Dim Newb
Set Newb = Application.CommandBars("Cell").Controls.Add()
With Newb
.Caption = "AAA（右クリックメニューに表示したいマクロ名）"
.OnAction = "BBB(SI接頭辞に変更するマクロ）"
.BeginGroup = False
End With
End Sub

　バグの内容の詳細を以下に記載します。

1．　CSVデータ（例えば　1.234E9、-1.234e-12　が記載されたテキストファイル)を　
　　　エクセルのデータのメニューから呼び出し
　　　A1に　1.234E9　B1に-1.234e-12が記載されているとします。
　　　そうすると、　A1、B1以外では、右クリックメニューでAAAは表示されるのにA1,B1では
　　　表示されない。
2．　このシートをエクセルの形式でセーブして再度読み込んでも上記は改善されない。
3．　A1のデータに対し　BBBのマクロを実行すると、SI接頭辞に変換される。

　上記の3．の試行により　バグは右クリックメニューでマクロを呼び出すマクロにあると考えますが、　どうしても　CSVから呼び出したデータについては　右クリックメニューにマクロを呼びだすことができません。
　どのように修正すればよいか　どなたかご教授ねがえないでしょうか？

質問者からの補足コメント

• 対処療法的ですが、　CSVデータにて　右クリックメニューからマクロを呼び出すことができました。　ただし　マクロを書き換えたわけではありません。
  　以下のとおりです。
  　
  CSVで読み込んだデータを選択し右クリック→データ範囲のプロパティ→クエリの定義を保存する　のチェックを外す。
  
  以上の作業の後では、CSVで呼び出したセルについても　右クリックにAAAが呼び出されるようになりました。　理由は　わかりませんが、クエリが保存されているデータについては、右クリックメニューは　デフォルトのまま　ということになっているようです。

    補足日時：2016/09/24 16:27

No.3 ベストアンサー 回答者： bonaron 回答日時： 2016/09/24 16:19

CSVファイルを「外部データの取り込み」から取り込むと

その部分のポップアップメニューは
「Cell」ではなく「Query」です。

「Query」についても同様にメニューに登録するか
「名前の管理」から CSVのデータ部分の名前を削除する
お好きな方法で。

この回答へのお礼

ご回答ありがとうございます。　
　根本的原因は、bonaron様のご指摘のとおりでした。
　今回　Queryが原因とご指摘していただいた　本ご回答をBAとさせていただきたいと思います。

　さて、
　恥ずかしながら、今回のトラブルで　Queryという概念に初めて出会った次第です。
　>「Query」についても同様にメニューに登録するか
　>「名前の管理」から CSVのデータ部分の名前を削除する
　>お好きな方法で。　
　
　補足に書いた対処療法が早いので　名前の削除で対応したいと思います。

お礼日時：2016/09/24 17:33

No.2 回答者： WindFaller 回答日時： 2016/09/24 16:07

実際問題として、肝心なマクロのほうが見せられていないのでは、なんとも答えようがないのですが……。

書き方をどうこういうというつもりはないのですが、私などは、こんなふうな書き方で続けてきたわけです。

'標準モジュール

Sub Auto_Open()
　With Application.CommandBars("CELL")
　　.Reset
　　With .Controls.Add _
　　　(Type:=msoControlButton, Before:=1, Temporary:=True)
　　　.BeginGroup = False
　　　.Caption = "SI表示"
　　　.OnAction = "ConvertSIPro"
　　End With
　End With
End Sub

それで、OnAction というのは、マクロ名ですから、完結で分かりやすいこと、なるべく英字でというところでしょうが、Caption 側は説明的でも、文字が入ればよいでしょう。

これをどこに入れるということになると、内容的の公共性からすれば、「個人用マクロブック」が最適だと、個人的には思うのです。そうでなければ、アドインとか。

ただ、肝心なマクロ自体は、なかなか難しいなって感じました。
そして、その導入部分だけの話になるのですが、
'Private ステートメントがあればよいかもしれません。

Sub ConvertSIPro()
Dim c As Variant
If TypeName(Selection) <> "Range" Then Exit Sub
　 Set c = Selection.Cells(1)
　If VarType(c.Value) = vbDouble Then
　　 MsgBox ConvertSI(c.Value)　'ユーザー定義関数
　End If
End Sub

こんな具合でよいのでは、と思うわけで、

>A1、B1以外では、右クリックメニューでAAAは表示されるのにA1,B1では
>表示されない。

というのは、実際のマクロの問題でしょうね。さもなければ、イベント・ドリブン型のマクロが入っているとか。つまり、上記で見て分かるように、値を与えるだけのものに、セル位置などは、何ら関係はないのです。

なお、個人的に気になるのは、

12340000000　NUMBERSTRING関数では、百二十三億四千万
さて、当のマクロでは、どのような表示になるのでしょうか？
12.34G とするのか、12.3 G とするのか、1.23E+10 のままになるのか、
また、小数点以下の場合は、

-0.0000000010234　　-1.0E-09 は、どういう表記をするものでしょうね。どんぴしゃりの場合はよいにしても、そうでない場合の表記とか。

この回答へのお礼

ご回答ありがとうございます。
>さて、当のマクロでは、どのような表示になるのでしょうか？
12340000000の場合は、　12.34G と表記されます。
-0.0000000010234　　の場合は、　-1.023nと表示されます。
5桁目を四捨五入して有効数字4桁とします。

お礼日時：2016/09/24 17:21

No.1 回答者： ママチャリ 回答日時： 2016/09/24 14:41

次のように修正してみて下さい。

.OnAction = "BBB(SI接頭辞に変更するマクロ）"
　↓
.OnAction = ThisWorkbook.Name & "!BBB(SI接頭辞に変更するマクロ）"

ちょっと気になるのは、”BBB(SI接頭辞に変更するマクロ）”って、サブプロシジャ名ですよね？カッコの中は引数として扱われてしまう可能性があるので、カッコなしの名前にすべきです。

この回答へのお礼

ご回答ありがとうございます。　質問の仕方がわるくて申し訳ありません。　”BBB"　’SI接頭辞に変更するマクロ　の意味でした。
　括弧は名前に付けておりません。
　残念ながら教えていただいた　方法では、右メニューに表示させることができませんでした。

お礼日時：2016/09/24 16:57