VBA Evaluate関数 型が一致しません

質問

Excel2003

VBAのEvaluateで以下の数式を実行すると
エラー「型が一致しません」となってしまいます。

類似の質問を検索していろいろ参考にしてみたのですが
解決できなかったので質問させてください。

Sub test()

Dim aa, bb, cc As String
Dim y As Byte
y = 1

With Sheets("Sheet1")
aa = ".Cells(y, 1) > 0"
bb = Left(aa, InStr(aa, "y") - 1)
cc = Mid(aa, InStr(aa, "y") + 1)
If Evaluate(bb & y & cc) Then ←ここでエラーになります。
y = 2
End If
End With

End Sub

.Cells(1, 1)には１０が入力されています。

宜しくお願い致します。

回答No.2

cj_mover · ベストアンサー

こんにちは。

.Evaluateメソッドの練習でしょうか？
.Evaluateメソッドを使う必要も必然性もないですから、
コードの提示だけでは実際に何をしたいのか判断付きませんけれど、
ご提示のコードを（正しくやりたいことを表現できているものとして）素直に読んで
修正を加えるとするば、以下のようになります。

' ' ///
Sub Re8383252()
　　　　Dim 数式雛型 As String, 数式 As String
　　　　Dim y As Long

　　　　数式雛型 = "A1>0"
　　　　y = 1
　　　　数式 = Replace(数式雛型, "1", y)
　　　　With Sheets("Sheet1")
　　　　　　　　If .Evaluate(数式) Then
　　　　　　　　　　　　y = 2
　　　　　　　　End If
　　　　End With
End Sub
' ' ///

ご提示のコード内の変数の値等を確認してみると、
aa　⇒　".Cells(y, 1) > 0"
bb　⇒　".Cells("
bb　⇒　", 1) > 0"
bb & y & cc　⇒　".Cells(1, 1) > 0"
（値はすべて　文字列値　↑）
のようになっています。
Evaluate(bb & y & cc)
は、
Evaluate(".Cells(1, 1) > 0")
を実行していることになり、
Excel側で".Cells(1, 1) > 0"を評価した結果として
「エラー　入力した数式は正しくありません」
が返り、
VBA側では、If文にエラー値を渡すことになるので
「実行時エラー '13' 型が一致しません」
という結果になります。

そもそも.Evaluateメソッドは、文字列式を渡して、
Excelに評価（計算）させる為のメソッドですから、
If Evaluate("A1>0") Then
のようにExcel数式を引数に渡してこそ成立しますが、
"Cells"という関数も名前もExcelにはありませんから、必然エラーになります。

ご提示のコードでおかしな点を指摘しておきます。

> Dim aa, bb, cc As String
３つの変数の内、文字列型で宣言されているのは、cc、だけです。
　Dim aa As String, bb As String, cc As String
と書くのが意図にそった正しい書き方なのではないでしょうか？

> Dim y As Byte
行位置を扱う変数には、Long 型を使うのが正解です。
Byte型は本来、バイナリデータの配列を格納する為に用意されているもので、
通常は、数値を格納することはない（間違い）、と覚えてください。
今から２０年も前ならば、少しでもメモリを節約する目的で、
無理矢理Byte型を使うような悪いお手本も見かけましたが、
今日的には、如何なる目的であろうとも間違いは間違いです。

> With Sheets("Sheet1")
・・・
> End With
Withフレーズを使うのは、
　　Sheets("Sheet1").Cells(1, 1).Value = 10
　　Sheets("Sheet1").Cells(1, 2).Value = 20
のような例で、Sheets("Sheet1")への参照を繰り返すのは無駄が多いので、
　　With Sheets("Sheet1")
　　　　.Cells(1, 1).Value = 10
　　　　.Cells(1, 2).Value = 20
　　End With
のようにオブジェクトへのアクセスを一つにまとめる意味です。
Withフレーズを書いているのに、一度も使っていないのでは意味がありませんし、
読み手は困惑してしまいます。
こちらで提示したコードでは、.Evaluate のように、
Sheets("Sheet1")への参照を一度だけ活用していますが、
そのままなら、わざわざWithブロックを使う必要はありません。

> aa = ".Cells(y, 1) > 0"
> bb = Left(aa, InStr(aa, "y") - 1)
> cc = Mid(aa, InStr(aa, "y") + 1)
> If Evaluate(bb & y & cc) Then
元の数式としてのaaは間違いですが、
それ以外の処理のしかたは間違っていません。
ただ、Replace関数を使って数式の一部（行位置）を置換する方が簡単です。

> .Cells(1, 1)には１０が入力されています。
具体的なシートの状況を説明する時は、
「数値 10 」なのか、「文字列値 "１０" 」なのか、明示的に書き分けるのがベターです。
「数値 １０ 」のような書き方ならば、見やすく全角で書いたものとして伝わりますが、
「１０」のようにただ全角で書くのは誤解の元です。
VBAを扱うのならば、データ型には注意しましょう。

やりたいことは何となく想像は付くのですが、
何故.Evaluateメソッドなのか、把握できていませんので、
そちらの実際にやりたいことに、擦寄るような回答は書きません。
ご質問文に書かれたお尋ねに対しては、十分にお応え出来たものと思っています。
そちらで尚も不足が残るようでしたらば、スレッドを改め、質問を再構築してください。

以上です。

お礼

ご教示頂いたコードで出来ました！
EvaluateがそもそもExcel関数である為、Excelの数式を与えないといけないことやReplaceの方が簡潔であることに加え、コードの書き方に至るまで懇切丁寧に説明して頂き誠にありがとうございました。
Webで質問するのは初めてでしたが、して良かったです。
自分のコードを再度見直したいと思います。

回答No.1

mt2008

Evaluateメソッドでのセル参照は、A1の様な形式ですRangeやCellsは使えません。
コードを以下の様に変えてみて下さい。

aa = ".Cells(y, 1) > 0"
↓
aa = "Ay > 0"

お礼

ご教示ありがとうございました。
Evaluateメソッドでのセル参照は、A1の様な形式でRangeやCellsは使えないこと、肝に銘じておきます。