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("A1:D1").Select

'You can edit the above line to use the name
'in lieu of the cell address as follows:-
Range("MyName:D1").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("A3:D3").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" wrote in message
...
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("A1:D1").Select

'You can edit the above line to use the name
'in lieu of the cell address as follows:-
Range("MyName:D1").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("A3:D3").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" wrote in message
...
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