Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help text for user defined formula | Excel Worksheet Functions | |||
User-defined range for graph | Excel Discussion (Misc queries) | |||
Passing a range to a user defined function | Excel Discussion (Misc queries) | |||
"User-defined type not defined" message in Excel | Excel Discussion (Misc queries) | |||
User defined functions - text for formula palette | Excel Programming |