Formula Evaluate Teaser

[JJ]

I need to evaluate the result of a modelled cell through VBA. Can someone
give me an idea on how to do this?

E.g

Cell A1 Formula: ="A"&LEFT(H14,1)
Cell H14 Text: World
Cell A1 result: AW

Through VBA I need to get the result for LEFT(H14,1). This would go
something like below:

Sub Evaluate()
Dim strForm As String
strForm = ActiveCell.FormulaLocal
strForm = Right(strForm, Len(strForm) - InStr(1, strForm, "&"))
Debug.Print strForm
End Sub

How can I evaluate the strForm part of the formula to return the result "W"?

Any help would be appreciated

 

[Don Guillett]

Would this work just as it is?
msgbox "A"&left(range("H14"),1)

 

[JJ]

The custom function needs to evaluate Microsoft functions for the result.

 

[JJ]

We have found another way to do this. Thanks for the assistance.

 

[Dana DeLouis]

Don't know if this will help, but this returns "W"

Debug.Print Evaluate(Split([A1].FormulaLocal, "&")(1))

HTH
Dana DeLouis

 

[JJ]

Thanks Dana.

Don't know if this will help, but this returns "W"

Debug.Print Evaluate(Split([A1].FormulaLocal, "&")(1))

HTH
Dana DeLouis

We have found another way to do this. Thanks for the assistance.