Newbie question.

Working in Excel 2000.
I have a workbook with a range called 'ABC'. I reference this range within a
VBA function as:
x = Application.Range("ABC")
This works well as long as it is the active workbook, when switching to
another workbook and forcing a calculation the range is not found. The way
to do this of course is to qualify it as:
x = Application.Range("'[Workbook.xls]Sheet1'!ABC")
Great, my problem is that I am not doing it in VBA but via a COM interface
where I only have the range name, not the workbook, or the sheet name!

My question: Is it not possible when defining a Range to include the
Workbook qualification in some way? When I try to do this via the menu
system, Excel just 'loses' this extra information?

Any help appreciated!

Hi
as the other workbook has to be open to access the defined name you may
cycle through all workbooks. Something like

dim wbk as workbook
dim oName as name
Dim rng as range
on error resume next
for each wbk in application.workbooks
set oname=wbk.names("ABC")
if not oname is nothing then
exit for
end if
next wbk

set rng = oname.referstorange
msgbox rng.value
end sub


"Andre Louw" wrote:

Newbie question.

Working in Excel 2000.
I have a workbook with a range called 'ABC'. I reference this range within a
VBA function as:
x = Application.Range("ABC")
This works well as long as it is the active workbook, when switching to
another workbook and forcing a calculation the range is not found. The way
to do this of course is to qualify it as:
x = Application.Range("'[Workbook.xls]Sheet1'!ABC")
Great, my problem is that I am not doing it in VBA but via a COM interface
where I only have the range name, not the workbook, or the sheet name!

My question: Is it not possible when defining a Range to include the
Workbook qualification in some way? When I try to do this via the menu
system, Excel just 'loses' this extra information?

Any help appreciated!

even using COM, i assume you've opened it
so you know the wb name = thus you can set a variable.

ELSE you must rely on the workbook that's ACTIVE
(last opened)

ELSE you must know (or store) the workbook name :(


BUT you can dispense with the sheetname IF
the name object has the workbook as parent...

Range( "'" & strWBNAME & "'!abc")
(Note NO brackets)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Andre Louw" wrote:

Newbie question.

Working in Excel 2000.
I have a workbook with a range called 'ABC'. I reference this range
within a VBA function as:
x = Application.Range("ABC")
This works well as long as it is the active workbook, when switching
to another workbook and forcing a calculation the range is not found.
The way to do this of course is to qualify it as:
x = Application.Range("'[Workbook.xls]Sheet1'!ABC")
Great, my problem is that I am not doing it in VBA but via a COM
interface where I only have the range name, not the workbook, or the
sheet name!

My question: Is it not possible when defining a Range to include the
Workbook qualification in some way? When I try to do this via the menu
system, Excel just 'loses' this extra information?

Any help appreciated!

Hi,

If you have any more concerns on it, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.