Compute Named Formulas

[JeremyJ]

Is there a way to grab the "Computed Value" or "Result" of a Named Formula
with in a Worksheet with VBA?

WorkSheet Name
Min=((HOUR(Time_End)*60+MINUTE(Time_End))-(HOUR(Time_Start)*60+MINUTE(Time_Start)))-Time_Break

Sub Grab_Minute()
Dim n As Name
Dim Computed_Value As Long
i = 1
For Each n In Names
If n.name = "Min" then
Computed_Value = *Compute*(n)
End if
Next n
End Sub

 

[Gary''s Student]

Interesting - I have never seen this question before.

Say the B10 contains 10 and B11 contains 11.
Say we have a Defined Name - alpha which is:

=Sheet1!$B$10+Sheet1!$B$11

so in the worksheet if we enter =alpha in some cell, we see 21

In VBA:

Sub servient()
For Each n In ActiveWorkbook.Names
MsgBox (Evaluate(n.Name))
Next
End Sub

will output the 21 in the msgbox

 

[Bernard Liengme]

Here is a very unsophisticated workaround while you (actually, while we)
await a better answer

Sub tryme()
Range("D1") = "=Myform"
myvariable = Range("D1")
Range("D1").Delete
MsgBox myvariable
End Sub

I defined Myform as =Sheet1!$A$1+Sheet1!$A$2
And the sub displayed a message box with the correct value

I experimented with getting a single member of a collection as in
Names("Myform") but this return the formula not the computed value
best wishes

 

[JeremyJ]

Perfect! Thank you. The "Evaluate" function even got rid of the Equals sign
and quotes for all of the other names as well which did not have formulas. I
was removing those "manually". This works much better.

 

[Dana DeLouis]

If n.name = "Min" then

Computed_Value = *Compute*(n)

Hi. I see you have a solution. Just to add...if you have a specific
name in mind, here is my preference:

Sub Demo()
Dim MyMin

'// If you have this formula...
ActiveWorkbook.Names.Add "Min", _

"=((HOUR(Time_End)*60+MINUTE(Time_End))-(HOUR(Time_Start)*60+MINUTE(Time_Start)))-Time_Break"

'// Then later in your code...
MyMin = [Min]
End Sub


Another interesting variation to Gary's solution in some situations is
to use Relative Addressing (no $ in address)
Where this points to depends on where the active cell is when this named
formula was added.

=Sheet1!B10+Sheet1!B11

The answer to this evaluation depends on where the active cell is in the
same relationship as when the named formula was generated.

Sometimes, that can be really cool...
- - -
HTH :>)
Dana DeLouis