Compute Named Formulas
 

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

Compute Named Formulas
 

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
--
Gary''s Student - gsnu200826


"JeremyJ" wrote:

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

Compute Named Formulas
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"JeremyJ" wrote in message
...
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

Compute Named Formulas
 

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200826


"JeremyJ" wrote:

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

Compute Named Formulas
 

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


JeremyJ wrote:
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