external range in VBA (user defined formula)
 

One possibility:

Dim wkb as Workbook
Set wkb = Workbooks("BookName.xls")
Msgbox wkb.Names("Forecast").RefersToRange.Cells(1, 1).Value


"Gord" wrote in message
om...
Hello. I was wondering if it is possible to reference an external
range
in VBA. I tried to do it two different ways, as shown in the code
below
but neither of them worked. I put the error messages I got in the
comments
above the particular line in which that error occurred.

Background: What I'm ultimately trying to do is make a user defined
formula
to replace an ugly formula(that takes two or three times longer to
calculate than it needs to)
that has a whole bunch of IsError() and VLookup() If(), and match()
functions,
many of which take external ranges as parameters.


Thanks for any insight,

Gord.



On Error GoTo zero

'// Forecast is an External Range. TableNames is a local Range

'// Works
MsgBox " contents of cell in local range: " &
Range("TableNames").Cells(1, 1).Value

'// doesn't work: Method 'Range' of Object '_Global' failed
MsgBox " contents of cell in external range: " &
Range("Forecast").Cells(1, 1).Value

'// doesn't work: application defined or object defined
error
MsgBox " contents of cell in external range: " &
Names("Forecast").RefersToRange.Cells(1, 1).Value

GoTo the_end

zero:
MsgBox "error: " & Err.Description

the_end: