Unresolved local named range

[Andre Louw]

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!

 

[Guest]

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

 

[Peter Huang]

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.