Compute Named Formulas

  • Thread starter Thread starter JeremyJ
  • Start date Start date
J

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
 
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
 
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
 
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.
 
If n.name = "Min" then
Computed_Value = *Compute*(n)
Click to expand...

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
 
Back
Top