ExcelVBAでの開発時に引っかかった点

これは何

バグみたいな仕様？ に引っかかり、エラー解消までに時間のかかった点をまとめた記事です。

Evalute(1) の 1 は「数式」では無い可能性がある

3行で

Evalute(1) を実行するとエラーになることがある
知恵袋にドンピシャの回答有り。 フォームコントロールの番号として扱われてしまっている
数字ならそのまま扱うか、 "--" をつなげるなど、強制的に数式へ変換して対処する

詳細

こんな感じでボタンを押すとA列にある数式の計算結果をB列に出力してくれるマクロを組む。

Option Explicit
Sub Test()
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets("Sheet1")
    Dim i
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        sht.Cells(i, 2).Value = Evaluate(sht.Cells(i, 1).Value)
    Next i
End Sub

…が、実行すると引数に 1 を与えたとき 実行時エラー '1004' アプリケーション定義またはオブジェクトのエラーです。 と表示され、にエラーとなってしまう。
Copilot君に聞いてみても「1は短すぎて数式として扱われていません。でも2は数式です」とかいう的外れな回答しかしてくれず、途方に暮れる。

ちなみにイミディエイトウィンドウに直接打ち込んでもエラーになる…が、エラー文が違う。

原因

この知恵袋 にある通り、 1 が 数式ではなくフォームコントロールの番号として扱われ、「計算開始」のボタン情報を取ってきている。
それに気づかず数式のつもりでセルに代入しようとしてエラーになっていた。 フォームコントロールの評価って何？ なぜ数式評価と同じ関数にまとめたのか？

?Evaluate(2)
 2 
?Evaluate(1).Caption
計算開始

Worksheet.Evaluateの方にはフォームコントロール番号を取り扱っていないのでこれに変えれば行ける…と思ったが、ダメだった。何故？

?sht.Evaluate(1).Caption
計算開始

解決策

• IsNumeric で数値では無かったら Evaluate を使う
• "--" をつなげるなど、強制的に数式へ変換する
  • 例えば 1 なら --1 となり、-(-1) = 1 として扱われる
  • 例: sht.Cells(i, 2).Value = Evaluate("--" + sht.Cells(i, 1).Value)

指定したセルが非表示だと Range.Text は空文字？

3行で

非表示セルを Range.Text で取得しようとすると、空文字が戻ってくることがある
原因はTextがセル上に表示されている通りの値を取得するため？（※例外有）
Valueで取得するか、非表示セルを無くして対処する

詳細

B2セルをValueとTextで引っ張ってくるとTextは「表示されている文字列」を取得するため、列幅が足りずにエラー表記だとエラーのままの値になる。

Option Explicit
Sub Test2()
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets("Sheet2")
    sht.Range("D2") = sht.Range("B2").Value
    sht.Range("E2") = sht.Range("B2").Text
End Sub

だが、セルが非表示の場合はそもそも取得してくれない。

原因？

Textは表示されている文字列を取得するため、非表示セル = 見える文字は無いので空文字を返すという認識…なのだが、 書式設定が標準でセルの値が文字列の場合など、一部のケースでは非表示でも文字を引っ張ってこれる。 意味が分からない…

WinActor(Excelも操作できるRPAソフト)の ユーザフォーラム で似たような事例はあるものの、原因解明には至らず…

解決策

書式などを気にしなくてよければValueを使って値を取得した方が良い。
もしくは sht.Columns.Hidden = False などで非表示を解除して取得する。

正しくEnumを宣言しているのに、エラーになる

3行で

エディタ上ではEnumの補完が効くのに、マクロを実行するとエラーになる
再現性は無いが、ググると同じ症状に遭遇した人はちらほら出る
変数の定義を変更すると一応動く

詳細

マクロを実行するとEnumの値を使用した箇所で「定数式が必要です」というエラーが出る。
しかし使用方法は間違っておらず、エディタ上ではEnum要素の補完機能が出るため、エディタにもエラーが出たEnumの要素は認知されている。でも動かない。
再現性は不明。デバッグなどでマクロを中途半端に止めたりしていると発生しやすいような…
（スクショを取ろうとしましたが、再現できず…）

迂回策

はっきりとした原因・解決策はわかりませんでした… エディタのバグっぽいですが…
迂回策としてはこのQiitaの記事にあるように各変数に省略していた数値を代入したり、Privateをつけたりして変数の定義を変えるとエラーを解消できる可能性があります。
再発する場合もあるので、その場合は逆に外したりしてください。
もし根本的な解決方法を知ってる方は教えてください…