Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a range in a custom function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a range in a custom function
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a range in a custom function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use a range in a custom function? | Excel Worksheet Functions | |||
Custom Function: Detecting the cell the function is used in | Excel Programming | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Translate range name passed as string to a custom function to range addresses! | Excel Programming | |||
Adding a custom function to the default excel function list | Excel Programming |