It looks like you are not referencing the range fully. Perhaps it
should be something like :-

Workbooks("MyWorkBook.xls").Worksheets("Sheet1").R ange("Forecast").Cells(1,
1).Value

It is a bad programming habit to leave out the Workbook and Worksheet
identifiers. Ok, if everything is in one Workbook you can omit this
bit safely. Although you may get away with it most of the time, it can
lead to problems as your macros get more complicated. You become more
advanced and don't have to use .Select very often. I notice that you
are, at least, putting the .Value <grin. Omitting this can sometimes
produce unpredictable results - very difficult to find & debug.


Regards
BrianB
================================================== ==



(Gord) wrote in message . com...
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: