String evaluated as formula question

  • Thread starter Thread starter Gromit
  • Start date Start date
G

Gromit

Hi,

I have a formula in A1 the form of a text string, e.g.

'=1+1

If I take the RIGHT(A1,4) of this, and paste - values, I'm left with

=1+1

But the formula isn't evaluated. If I manually click in the formula bar
and hit Enter, then the result, 2, appears.

Anyone any idea how to make the cell evaluate automatically?

Thanks for your thinking effort,

Gromit
 
Maybe you could use a UserDefinedFunction:

Option Explicit
Function Eval(str As String) As Variant
Eval = Application.Evaluate(str)
End Function

then you could use:

=eval(a1)

That apostrophe gets eaten up by excel.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Thanks Dave,

I've been trying to figure that out for some time. A further question,
though, that would help me understand VBA further:

In the Help menu I see that Evaluate also applies to the Worksheet
object. When I replace Application in the function with Worksheet,
however, the function fails. When I use the collection object
Worksheets(1), the function works again.

I thought Worksheet and Worksheets(1) were essentially the same thing?

Any help much appreciated.

Thanks again,

Gromit
 
The .evaluate will work with a specific worksheet:
eval = worksheets(1).evaluate(str)

Not just the word Worksheet.

In fact, this might be a better function:

Option Explicit
Function Eval(str As String) As Variant
Eval = Application.Caller.Parent.Evaluate(str)
End Function

(application.caller is the cell that holds your formula. .parent means the
worksheet holding that cell.)

This function will use the worksheet with the formula as the "base". The old
formula will evaluate differently depending on what the activesheet was.

Try this with the old function.

two worksheets
'=b1+3
in A1 of both sheets
b1=1 in sheet1
b2=3 in sheet2

Window|new window
window|Arrange|horizontal
(show both worksheets)

Hit F9 to calculate. Depending on which sheet is active, you get different
results.

Try the same thing with the new function and it reacts the way you probably
want.

(sorry about the misdirection.)
 
Back
Top