evaluation a variable in an expression

[andrewsacher]

Hi,
Is there any way for me to evaluate a string variable as if it were
code. For example, say I have an input string=".Font.ColorIndex = 4".
Is there anyway for me to get VBA to evaluate the expression:

"Selection" & string

and have it run as if it were the command "Selection.Font.ColorIndex =
4"?

Thanks,
Andrew

 

[Chip Pearson]

I don't believe there is any way to do this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

You can do a few things with CallByName. E.g,.

Debug.Print CallByName(CallByName(Selection, "Font", VbGet),
"ColorIndex", VbGet) = 4

You can nest CallByName's to get the property value you need.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[andrewsacher]

Thanks for your response. I don't quite understand it. Could I use it
to change the colorindex to a different value or just to check what the
current color index is?

Thanks,
Andrew

 

[Chip Pearson]

You could use it to change the font colorindex.

CallByName CallByName(Selection, "Font", VbGet), "ColorIndex",
VbLet, 3

This will set the ColorIndex of the Font of the Selection to 3
(red).

The inner CallByName returns (VbGet) the "Font" object property
of Selection object to the outer CallByName, which sets (VbLet)
the "ColorIndex" property to 3 (red).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[andrewsacher]

Thanks so much! Below is a quick function I wrote. It takes as an
input a string with up to one dot that would come after Selection in
VBA code and then performs the action.

Sub testEval()
a = runEvalSelection("Font.ColorIndex=12")
b = runEvalSelection("IndentLevel=1")
End Sub

Function runEvalSelection(strInput)

'This function evaluates a dot delimited string with 2 methods

splitted = Split(strInput, ".")
nmArgs = UBound(splitted) + 1

If nmArgs = 1 Then
secondArg = splitted(0)
whereEqual = InStr(1, secondArg, "=", vbTextCompare)
If whereEqual > 0 Then
justMethod = Mid(secondArg, 1, whereEqual - 1)
wantNm = Mid(secondArg, whereEqual + 1, 100)
CallByName Selection, justMethod, VbLet, wantNm
End If
End If


If nmArgs = 2 Then
secondArg = splitted(1)
whereEqual = InStr(1, secondArg, "=", vbTextCompare)
If whereEqual > 0 Then
justMethod = Mid(secondArg, 1, whereEqual - 1)
wantNm = Mid(secondArg, whereEqual + 1, 100)
CallByName CallByName(Selection, splitted(0), VbGet),
justMethod, VbLet, wantNm
End If

End If


runEval = True
End Function