external range in VBA (user defined formula)
 

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:

external range in VBA (user defined formula)
 

I don't know if this will work for your particular situation, but there is a
method documented at John Walkenbach's site:
A VBA Function to Get a Value From a Closed File
http://j-walk.com/ss/excel/tips/tip82.htm

In your first post you were using defined names, in this post, absolute
ranges. You may have to play around to get what you want. For example for
defined ranges the following returns the value in 'rng' for a closed
workbook:

MsgBox ?ExecuteExcel4Macro("'D:\Data\[Book1.xls]Sheet1'!rng")

where 'rng' is a single-cell defined name. I don't know yet if there is a
way to get the top-leftmost cell in a multi-cell defined range.

Tim


"Gord D" wrote in message
...
Thanks for your suggestions guys. I gave them a shot, both with the
other sheet opened, and closed. (Ultimately, it has to work with the
other sheet closed)

I figured that if the workbook is closed, I'd have to get at it via the
full path (otherwise how would excel know where to look?)

At first glance, it doesn't look like you can get at ranges of closed
workbooks.

As you can see, I tried putting the full path, sheet, and range all
inside range(), and also tried specifying them in
Workbooks().worksheets().range()




'// doesn't work: Method 'Range' of Object '_Global'
failed

MsgBox " contents of cell in external range: " &
Range("\\hqserver01\rfp_accounti\TABLES\2003\[SCDCM.xls]SC_EL2_CM!A1:P60
00").Cells(1, 3).Value


'// works when SCDCM.xls is open, otherwise same as above
MsgBox " contents of cell in external range: " &
Range("[SCDCM.xls]SC_EL2_CM!A1:P6000").Cells(1, 3).Value




'// doesn't work: Subscript out of range

MsgBox " contents of cell in external range: " &
Workbooks("\\hqserver01\rfp_accounti\TABLES\2003\S CDCM.xls").Worksheets(
"SC_EL2_CM").Range("A1:P6000").Cells(1, 1).Value


'// works when SCDCM.xls is open, otherwise same as above
MsgBox " contents of cell in external range: " &
Workbooks("SCDCM.xls").Worksheets("SC_EL2_CM").Ran ge("A1:P6000").Cells(1
, 1).Value



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

external range in VBA (user defined formula)
 

In your first post you were using defined names, in t
his post, absolute ranges.


Yes, the reason behind that is that I couldn't get at the external
ranges defined (from closed books) in the names, so I started trying to
get at them any way possible.

It puzzles me that you can use these ranges in functions within formulas
(such as VLookup), but you can't get at them in VBA at all.

I don't *actually* just want the top left cell. That
was pretty much just for test purposes to see if I could get at anything
in the range at all.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!