Search code examples
vbaexcel

Evaluate("1") gives error 438

I have few "Why?"s about evaluate...

Sub TestEvaluate()
Dim Tag As String
Tag = "5"
Tag = Evaluate(Tag) 'works fine

Tag = "1"
Tag = Evaluate(Tag) 'error 438 wrong property or method(-> my 1st "Why?")

But ok i can handle it:

Tag = "1"
Tag = [Tag] 'works fine

Now I need to evaluate a property of some object:

Dim Object As cObject
Set Object = New cObject
Object.pProperty = "5"
Tag = Evaluate(Object.pProperty) 'Works fine

And again the same problem as above:

Object.pProperty = "1"
Tag = Evaluate(Object.pProperty) '438 wrong property or method

But now i'm traped, becouse:

Tag = [Object.pProperty] 'generates error 13 type mismatch(-> my 2nds "Why?")

Is there some solution without the need to use a new variable?

Dim TempTag As String
TempTag = Object.pProperty
Tag = [TempTag] 'everything fine again

End Sub

i found out, in my case VBA.Evaluate("1") generates an object according to

debug.print VBA.VarType(evauate("1"))

It`s just a bug? (win8.1 xl2007)

Solution

  • I put the expression into brackets and the problem disappeared; works fine now:

    Tag = Evaluate("(" & Tag & ")")