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

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" <(E-Mail Removed)> wrote in message
news:2AD80316-7FC5-4E6B-BF11-(E-Mail Removed)...
> 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

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

> 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