概要
・ブックBからブックAのシートに"="がついた文字列をコピーしようとしたらエラー1004が出た
・文字列をシングルクォーテーションでエスケープしても同じエラーが表示された
・VBAで値を代入する際にシングルクォーテーションをつけてあげることでコピペできた
環境
・本番環境：Excel2016
・Qiita掲載のためのテスト環境：Excel2007
エラーについて
やりたいこと
・Sheet1のB2にある文章をSheet2のA1にコピーすること
複数のシートから値をループでコピーしてファイル出力するマクロを作っていた。順調に動いていたがどうしても上図のようなエラーが出てしまった。
一個ずつ分析していたところ、下図のように"="から始まる文章の時に停止することがわかった。
エラーが出たソース
Sheets(2).Cells(1, 1) = ExecuteExcel4Macro("Bpath[Book1.xlsx]sheet1'!R2C2")
通常のコピーであれば問題は無いと思われる内容で、事実これ以外の数値や文字列ではすんなりコピペできていた。
原因
色々と検証した結果、ペースト「される側」の表示形式に依存することがわかった。
エクセルでのコピペの場合
左側が抽出元、右側が抽出先
エクセルでコピペすると抽出元のセルに入力した文字そのものがコピペされる。
ゆえに抽出先がどのような表示形式でも抽出元の表示形式でペーストされる。
標準だと関数をそのまま使える、文字列だと文字になる
次にExcuteExcel4Macroを使って抽出先の表示形式を標準と文字列にしたものでどうなるか検証した（A2が対象）。
抽出元は文字列の" '=1+1 "だが、抽出先が標準になっていると関数の返り値で出力される。
以上からExcuteExcel4Macroを使うと抽出元の表示形式が文字列でも抽出先の表示形式に依存するということである。
つまり"="をコピペしたければマクロ側で文字列にする⇒エスケープしてあげれば良い
Sheets(2).Cells(1, 1) ="'" & ExecuteExcel4Macro("Bpath[Book1.xlsx]sheet1'!R2C2")
ということで"="をマクロで文字列扱いするには上記のように" ' "をExcuteExcel4Macroの前につけてあげると
結果として文字列がペーストされる。
そもそもなんでこんなことになったのか
実はファイル自体は短いコードが書かれたファイルで、条件に応じてコードに行を追加したり
値を変更したりする必要があるが手作業だと面倒だった。
そこで効率よく出力するためにマクロで各シートに正規のコードを貼り、それに対してマクロで処理するものだったのだが
この正規のコードに"="から始まるものがあった。
それまでの処理は正常終了していたのに最後の最後のコードがこれでハマってしまった。
ただ、調べてもよく出てこなかったので備忘録や似たような状況の人の助けになればと思い書いた。