Evaluating formula in VBA

[Walter Briscoe]

I have a string variable containing a formula which I want to evaluate.
I can do what I want with
activeCell.FormulaLocal = fx
result = activeCell.Value

I want a function which evaluates result without writing to a cell.
i.e. I am looking for the name of a function which will do
result = name(fx) and return the value of fx.

fx might have a value like "=""prefix"" & A1 & ""suffix""".

I have failed to find such a function. ;(

 

[Gary''s Student]

Sub eval()
Dim s As String
s = "=A1+A2"
MsgBox (Evaluate(s))
End Sub

 

[Walter Briscoe]

In message <[email protected]> of Thu,
11 Jun 2009 08:04:02 in microsoft.public.excel.newusers, Gary''s Student

Sub eval()
Dim s As String
s = "=A1+A2"
MsgBox (Evaluate(s))
End Sub

I had looked at the Excel 2003 VBA help, where I found:
Evaluate Method
See AlsoApplies ToExampleSpecificsConverts a Microsoft Excel name to an
object or a value.

expression.Evaluate(Name)
expression Optional for Application, required for Chart, DialogSheet,
and Worksheet. An expression that returns an object in the Applies To
list.

Name Required String. The name of the object, using the naming
convention of Microsoft Excel.

....

It did not seem to meet my purposes.

I guess I hit a documentation bug. ;(
Thank's for the prompt, effective help.

 

[Shane Devenshire]

Hi,

A little more detail would help - if this formula were "prefix" & A1
"suffix" then if A1 was 27 the answer would be prefix27suffix.

Is that really what you want?

 

[Walter Briscoe]

In message <[email protected]> of Thu,
11 Jun 2009 22:06:01 in microsoft.public.excel.newusers, Shane

Hi,

A little more detail would help - if this formula were "prefix" & A1
"suffix" then if A1 was 27 the answer would be prefix27suffix.

Is that really what you want?

Thank you for being willing to help.

Yes! Gary''s Student pointed me at Evaluate() in another posting and has
completely answered the question from my perspective.

To put the question in context:
I have a cell which contains a hyperlink call. I want to extract the
link location, evaluate it and use it in the following pseudo-code:
set IE - CreateObject("InternetExplorer.Application")
IE.Navigate2 evaluation
Write data extracted from IE document to other cells.

If my cell is "=hyperlink(""prefix"" & a1 & ""suffix"", ""name"")",
I construct a formula to evaluate "prefix" & a1 & "suffix".
I did not know about the evaluate() function.
I was misled by VBA F1 - help.

 

[Walter Briscoe]

In message <[email protected]> of Fri, 12 Jun 2009
08:32:16 in microsoft.public.excel.newusers, Walter Briscoe

In message <[email protected]> of Thu,
11 Jun 2009 22:06:01 in microsoft.public.excel.newusers, Shane
[snip]

To put the question in context:
I have a cell which contains a hyperlink call. I want to extract the
link location, evaluate it and use it in the following pseudo-code:
set IE - CreateObject("InternetExplorer.Application")
IE.Navigate2 evaluation
Write data extracted from IE document to other cells.

If my cell is "=hyperlink(""prefix"" & a1 & ""suffix"", ""name"")",
I construct a formula to evaluate "prefix" & a1 & "suffix".
I did not know about the evaluate() function.
I was misled by VBA F1 - help.

I tried to use evaluate and got an error 2015.
I left dealing with this error for some time while I did other things.
(I could write to a cell and read the result in place of evaluate.)
Yesterday I found there is a 255 character evaluate input string limit.

My string was of the form "=""rhubarb rhubarb""&a5&""...
I calculated "rhubarb rhubarb" & evaluate("=a5&""...)
I now have code which does what I want.

Is there any technique/are there any techniques for turning error codes,
such as 2015 from evaluate above, into text?