Hi All,

I have a defined name with a formula as its source, and it works in
worksheet functions. How can I retrieve its value in VBA?
For names with a normal range as source this line works:
ThisWorkbook.Names("mydefname").RefersToRange.Item(1, 1).Value
but I can't figure out a similar way for formula sources.

Thanks,
Stefi

Hi Stefi,

A little more than you requested but thought it might help you to understand
that a named cell/range is something you can use in lieu of a cell/range
address.

'The following is the recorded macro for inserting a name:-
Range("A1").Select
ActiveWorkbook.Names.Add Name:="MyName", _
RefersToR1C1:="=Sheet1!R1C1"

'Below is the above code which has been edited to name
'the active cell rather than a specified cell/range:-
ActiveWorkbook.Names.Add Name:="MyName", RefersToR1C1:=ActiveCell

'If you record selecting a range you get the following:-
Range("A11").Select

'You can edit the above line to use the name
'in lieu of the cell address as follows:-
Range("MyName1").Select

'The following retrieves the value in the named cell:-
MyValue = Range("MyName")
MsgBox MyValue

'The following names a range of cells and retrieves
'the actual address of the range.
Range("A33").Select
ActiveWorkbook.Names.Add Name:="MyNamedRnge", _
RefersToR1C1:="=Sheet1!R3C1:R3C4"

MyRange = Range("MyNamedRnge").Address
MsgBox MyRange

Regards,

OssieMac

Activesheet.Evaluate("mydefname")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Stefi" <(E-Mail Removed)> wrote in message
news:35A809EA-7F0F-440A-ABCE-(E-Mail Removed)...
> Hi All,
>
> I have a defined name with a formula as its source, and it works in
> worksheet functions. How can I retrieve its value in VBA?
> For names with a normal range as source this line works:
> ThisWorkbook.Names("mydefname").RefersToRange.Item(1, 1).Value
> but I can't figure out a similar way for formula sources.
>
> Thanks,
> Stefi
>

Hi OssieMac,

Thanks for your reply, but it didn't answer my question. If I name a cell,
then I can retrieve its value with
range("MyName")

But if I insert a name "MyName" with the Insert/Names menu and enter a
formula instead of a cell reference in the source field, I can refer "MyName"
in the worksheet (in formulae, etc.) and get its current value, but I can't
retrieve its current value in VBA (because "MyName" doesn't have any cell
reference).

Is there any way in VBA to get this value other than place it (redundantly)
in a cell?

Regards,
Stefi

„OssieMacâ€� ezt Ã*rta:

> Hi Stefi,
>
> A little more than you requested but thought it might help you to understand
> that a named cell/range is something you can use in lieu of a cell/range
> address.
>
> 'The following is the recorded macro for inserting a name:-
> Range("A1").Select
> ActiveWorkbook.Names.Add Name:="MyName", _
> RefersToR1C1:="=Sheet1!R1C1"
>
> 'Below is the above code which has been edited to name
> 'the active cell rather than a specified cell/range:-
> ActiveWorkbook.Names.Add Name:="MyName", RefersToR1C1:=ActiveCell
>
> 'If you record selecting a range you get the following:-
> Range("A11").Select
>
> 'You can edit the above line to use the name
> 'in lieu of the cell address as follows:-
> Range("MyName1").Select
>
> 'The following retrieves the value in the named cell:-
> MyValue = Range("MyName")
> MsgBox MyValue
>
> 'The following names a range of cells and retrieves
> 'the actual address of the range.
> Range("A33").Select
> ActiveWorkbook.Names.Add Name:="MyNamedRnge", _
> RefersToR1C1:="=Sheet1!R3C1:R3C4"
>
> MyRange = Range("MyNamedRnge").Address
> MsgBox MyRange
>
> Regards,
>
> OssieMac
>
>

That is it, thanks, Bob!
Stefi


„Bob Phillipsâ€� ezt Ã*rta:

> Activesheet.Evaluate("mydefname")
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "Stefi" <(E-Mail Removed)> wrote in message
> news:35A809EA-7F0F-440A-ABCE-(E-Mail Removed)...
> > Hi All,
> >
> > I have a defined name with a formula as its source, and it works in
> > worksheet functions. How can I retrieve its value in VBA?
> > For names with a normal range as source this line works:
> > ThisWorkbook.Names("mydefname").RefersToRange.Item(1, 1).Value
> > but I can't figure out a similar way for formula sources.
> >
> > Thanks,
> > Stefi
> >
>
>
>