Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges in Custom Functions
Hi All,
I'm trying to Calculate a Custom Function in Excel, by passing in a range of two cells rather than a cell name (which I had it working with previously). An example is below: Suppose in Excel I use: =Invt_Bal(A1:D1,A2:D2) With the function: Function Invt_Bal(Opening as Range, Purchases as Range, Var2 as Range) Dim Counter As Integer, UnitsCosted as Range For Counter = 1 to StartCount.Rows.Count Invt_Bal = Invt_Bal + Opening(Counter, 1) + Purchases (Counter,1) + Var2(Counter,1) Next Counter The Actual code is longer than this, with approx 6 variables, which worked fine when I was passing Names for the cells in, rather than the Cell references. When I had the names defined, my declarations we Set Opening = Range("Opening_Bal") Set Purchases = Range("Purchases") Set Var2 = Range("Var2") I'm not sure what to set these ranges to now or if? Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ranges in Custom Functions
You shouldn't be setting them at all in either case. They are passed in as
ranges. =Invt_Bal(Open_Bal,Purchases) =Invt_Bal(A1:D1,A2:D2) in a sample function Function Invt_Bal(Opening as Range, Purchases as Range) Invt_Bal = Opening.Address & ", " & Purchases.Address End function should return the same results. as an example. -- Regards, Tom Ogilvy "JR" wrote in message ... Hi All, I'm trying to Calculate a Custom Function in Excel, by passing in a range of two cells rather than a cell name (which I had it working with previously). An example is below: Suppose in Excel I use: =Invt_Bal(A1:D1,A2:D2) With the function: Function Invt_Bal(Opening as Range, Purchases as Range, Var2 as Range) Dim Counter As Integer, UnitsCosted as Range For Counter = 1 to StartCount.Rows.Count Invt_Bal = Invt_Bal + Opening(Counter, 1) + Purchases (Counter,1) + Var2(Counter,1) Next Counter The Actual code is longer than this, with approx 6 variables, which worked fine when I was passing Names for the cells in, rather than the Cell references. When I had the names defined, my declarations we Set Opening = Range("Opening_Bal") Set Purchases = Range("Purchases") Set Var2 = Range("Var2") I'm not sure what to set these ranges to now or if? Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using custom functions within custom validation | Excel Discussion (Misc queries) | |||
if functions with more than 7 ifs (ranges are possible?) | Excel Worksheet Functions | |||
How to access ranges in closed workbooks in custom functions | Excel Programming | |||
Functions and Ranges | Excel Programming | |||
Functions and Ranges | Excel Programming |