using a range in a custom function

[tad_wegner]

Tom-

i want to feed a range into a function in VBA. lets say the range is cells(A1:B10), 20 cells conservatively. i named the range "grid".

now i want to write a function in VBA, lets call it "Mz", and feed it "grid" as an input. so when i write the worksheetfunction "=Mz(grid)" in any cell, the workbook understands this. (i want to feed the function this range rather than have to feed it 20 individual cells).

my question is, how do i break the range down inside the function so that i can address each of the cells individually? like perhaps into an array?

lets say array dat1... (option base 1)

dim dat1() as double
redim dat1(10,2) - or - redim dat1(20,1) probably the latter here.

or if there is a better way of breaking these down besides into an array can you please enlighten me?

thanks for your help,
-tad

[Tom Ogilvy]

Function MZ(rng as Range)
Dim v as Variant
v = rng.Value
' gives an array of 1 x n rows in the first dimension and 1 x n columns in
the second dimension
'or you can use
for i = lbound(v,1) to ubound(v,1)
for j = lbound(v,2) to ubound(v,2)
v(i,j)


for each cell in rng

' or
for i = 1 to rng.count
rng(i)

--
Regards,
Tom Ogilvy

"tad_wegner" <[email protected]> wrote
in message news:[email protected]...
>
> Tom-
>
> i want to feed a range into a function in VBA. lets say the range is
> cells(A1:B10), 20 cells conservatively. i named the range "grid".
>
> now i want to write a function in VBA, lets call it "Mz", and feed it
> "grid" as an input. so when i write the worksheetfunction "=Mz(grid)"
> in any cell, the workbook understands this. (i want to feed the
> function this range rather than have to feed it 20 individual cells).
>
> my question is, how do i break the range down inside the function so
> that i can address each of the cells individually? like perhaps into an
> array?
>
> lets say array dat1... (option base 1)
>
> dim dat1() as double
> redim dat1(10,2) - or - redim dat1(20,1) probably the latter
> here.
>
> or if there is a better way of breaking these down besides into an
> array can you please enlighten me?
>
> thanks for your help,
> -tad
>
>
> --
> tad_wegner
> ------------------------------------------------------------------------
> tad_wegner's Profile:
http://www.excelforum.com/member.php...o&userid=27770
> View this thread: http://www.excelforum.com/showthread...hreadid=475079
>

[Jim Thomlinson]

Here is a really simple example. This needs to be in a regular module in
order to be accessible as a worksheet function

Public Function MZ(ByVal Target As Range) As Double
Dim rngCurrent As Range
Dim dblReturnValue As Double

For Each rngCurrent In Target
dblReturnValue = dblReturnValue + rngCurrent.Value
Next rngCurrent
MZ = dblReturnValue
End Function
--
HTH...

Jim Thomlinson


"tad_wegner" wrote:

>
> Tom-
>
> i want to feed a range into a function in VBA. lets say the range is
> cells(A1:B10), 20 cells conservatively. i named the range "grid".
>
> now i want to write a function in VBA, lets call it "Mz", and feed it
> "grid" as an input. so when i write the worksheetfunction "=Mz(grid)"
> in any cell, the workbook understands this. (i want to feed the
> function this range rather than have to feed it 20 individual cells).
>
> my question is, how do i break the range down inside the function so
> that i can address each of the cells individually? like perhaps into an
> array?
>
> lets say array dat1... (option base 1)
>
> dim dat1() as double
> redim dat1(10,2) - or - redim dat1(20,1) probably the latter
> here.
>
> or if there is a better way of breaking these down besides into an
> array can you please enlighten me?
>
> thanks for your help,
> -tad
>
>
> --
> tad_wegner
> ------------------------------------------------------------------------
> tad_wegner's Profile: http://www.excelforum.com/member.php...o&userid=27770
> View this thread: http://www.excelforum.com/showthread...hreadid=475079
>
>