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.

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.