Home |
Search |
Today's Posts |
#1
|
|||
|
|||
syntax to refer to a range as an array?
hi all --
simple one for those of you who use formulas or arrays regularly. In a module I have a public function -- Myfunct( inArray as variant, inNum as Integer) params a an array and an int It works fine. Now I want to call it from a worksheet formula using a range as the array parameter. e.g.: =MyFunct(A1:D1,B2) That formula syntax doesn't errors and Im stuck as to the proper way to convert a range (A1:D1) to call my function. Ive checked through the Excel array formula dox, but I don't see how to refer to a simple range as an array. this works --- = MyFunct({23,34,25,13},B2) this doesn't -- = MyFunct({A1:D1},B2) any hints? tips? thanks. dave |
#2
|
|||
|
|||
david cassain wrote...
.... this works --- = MyFunct({23,34,25,13},B2) this doesn't -- = MyFunct({A1:D1},B2) .... The latter shouldn't work - it's a syntax error. You can't put braces around range addresses. What do you get if you use =MyFunct(A1:D1,B2) ? |
#3
|
|||
|
|||
On Thu, 26 May 2005 18:41:12 GMT, david cassain
wrote: That formula syntax doesn't errors and Im stuck as to the proper way to convert a range (A1:D1) to call my function. sorry that typo should read : That formula syntax errors and Im stuck as to the proper way to convert a range (A1:D1) to call my function |
#4
|
|||
|
|||
On 26 May 2005 11:49:25 -0700, "Harlan Grove" wrote:
The latter shouldn't work - it's a syntax error. You can't put braces around range addresses. What do you get if you use =MyFunct(A1:D1,B2) Thanks for the reply Harlan -- I get a #VALUE! error. dave |
#5
|
|||
|
|||
david cassain wrote:
On 26 May 2005 11:49:25 -0700, "Harlan Grove" wrote: The latter shouldn't work - it's a syntax error. You can't put braces around range addresses. What do you get if you use =MyFunct(A1:D1,B2) Thanks for the reply Harlan -- I get a #VALUE! error. dave Perhaps you could include the relevant portion of the function, including the line that produces the error. Alan Beban |
#6
|
|||
|
|||
david cassain wrote...
On 26 May 2005 11:49:25 -0700, "Harlan Grove" wrote: The latter shouldn't work - it's a syntax error. You can't put braces around range addresses. What do you get if you use =MyFunct(A1:D1,B2) I get a #VALUE! error. Did you enter this as an array formula, using [Ctrl]+[Shift]+[Enter] rathre than just [Enter]? For some reason, Excel's formula parser doesn't require array entry for formulas containing array constants, but it does require array entry for derived arrays, including derived directly from ranges. If your udf still returns #VALUE!, you're going to have to show us the VBA code if you want further assistance. |
#7
|
|||
|
|||
On 26 May 2005 12:16:16 -0700, "Harlan Grove" wrote:
david cassain wrote... On 26 May 2005 11:49:25 -0700, "Harlan Grove" wrote: The latter shouldn't work - it's a syntax error. You can't put braces around range addresses. What do you get if you use =MyFunct(A1:D1,B2) I get a #VALUE! error. Did you enter this as an array formula, using [Ctrl]+[Shift]+[Enter] rathre than just [Enter]? For some reason, Excel's formula parser doesn't require array entry for formulas containing array constants, but it does require array entry for derived arrays, including derived directly from ranges. If your udf still returns #VALUE!, you're going to have to show us the VBA code if you want further assistance. thanks again Harlan, I get the error either way I enter it. here's my forumla in cell A3: = MyFunct(A1:D1,A2) here's the sheet data -- 3 rows, 4 vals in 1st row --------------------------------- ....0.....1.......2......3 ....2 ....Formula ---------------------------------- here's the trivial test function in module1: Public Function MyFunct(inArr, inInt) '~~ test passing range from worksheet to UDF. Dim i, tmp For i = LBound(inArr) To UBound(inArr) tmp = tmp + inArr(i) Next MyFunct = tmp / inInt End Function here's expected result: A3 should equal 6 --- (0+1+2+3)/2 I still get the #VALUE! error in the worksheet when I enter my formula with either [Ctrl]+[Shift]+[Enter] OR [Enter]. dave |
#8
|
|||
|
|||
On Thu, 26 May 2005 19:56:44 GMT, david cassain
wrote: here's expected result: A3 should equal 6 --- (0+1+2+3)/2 sorry the result should end up 3 not 6! |
#9
|
|||
|
|||
david cassain wrote...
.... here's the trivial test function in module1: Public Function MyFunct(inArr, inInt) '~~ test passing range from worksheet to UDF. Dim i, tmp For i = LBound(inArr) To UBound(inArr) tmp = tmp + inArr(i) Next MyFunct = tmp / inInt End Function .... I still get the #VALUE! error in the worksheet when I enter my formula with either [Ctrl]+[Shift]+[Enter] OR [Enter]. The udf fails on the LBOUND call because inArr is a Range object, so it doesn't have dimensions directly. Even if you got clever and forced it to be an array, e.g., =MyFunct(A1:D1+0,x) you'd still fail on the inArr(i) expression since all ranges are 2D. If your actual udf were as simple as your sample udf, you should rewrite it as Public Function MyFunct(a As Variant, n As Double) As Double Dim x As Variant For Each x In a If IsNumeric(x) Then MyFunct = MyFunct + CDbl(x) Next x MyFunct = MyFunct / n End Function If your actual udf is more complicated, you really do need to show us the code if you want help. |
#10
|
|||
|
|||
On Thu, 26 May 2005 19:56:44 GMT, david cassain
here's my forumla in cell A3: = MyFunct(A1:D1,A2) here's the sheet data -- 3 rows, 4 vals in 1st row --------------------------------- ...0.....1.......2......3 ...2 ...Formula ---------------------------------- here's the trivial test function in module1: Public Function MyFunct(inArr, inInt) '~~ test passing range from worksheet to UDF. Dim i, tmp For i = LBound(inArr) To UBound(inArr) tmp = tmp + inArr(i) Next MyFunct = tmp / inInt End Function here's expected result: A3 should equal 3 --- (0+1+2+3)/2 I still get the #VALUE! error in the worksheet when I enter my formula with either [Ctrl]+[Shift]+[Enter] OR [Enter]. dave working formula -- =MyFunct({0,1,2,3},B2) non-working formula -- =MyFunct(A1:D1,B2) It does *not* work when entered as an array formula , or a regular formula. sigh. the error excel is giving me is: " a value in the formula is of the wrong data type" --- so probably "A1:D1" is the wrong formula syntax. anyone know the proper syntax to convert an excel range --- a vba array so I can call a UDF? |
#11
|
|||
|
|||
On 26 May 2005 13:57:51 -0700, "Harlan Grove" wrote:
If your actual udf were as simple as your sample udf, you should rewrite it as Public Function MyFunct(a As Variant, n As Double) As Double Dim x As Variant For Each x In a If IsNumeric(x) Then MyFunct = MyFunct + CDbl(x) Next x MyFunct = MyFunct / n End Function If your actual udf is more complicated, you really do need to show us the code if you want help. Sadly, the code in the MyFunct function is 300+ lines of handicapping business rules that are unit tested and work flawlessly, and return a single value for any vba array passed in. {big sigh} So I wouldn't make anyone parse through it, but thanks. The udf fails on the LBOUND call because inArr is a Range object, so it doesn't have dimensions directly. Even if you got clever and forced it to be an array, e.g., Thanks for the due dilligence on finding the exact error for the UDF call, Harlan. Would it be possible to use the transpose worksheet function in any way to do this? Would that force the range object to a vba array in some way? I have another UDF function to convert a range to a vba array but I have no idea how to call it from a worksheet function: Public Function GetSheetSingleRowToArray(ByRef inSheet As Worksheet, ByVal inStartCell As String, ByVal inEndCell As String) As Variant '~~ pass in a start and end cell, and get the "ROW" range of cell values back as an array. GetSheetSingleRowToArray = Application.Transpose(Application.Transpose(inShee t.Range(inStartCell & ":" & inEndCell))) End Function Thanks again for your time, I appreciate it very much. dave |
#12
|
|||
|
|||
david cassain wrote...
.... Sadly, the code in the MyFunct function is 300+ lines of handicapping business rules that are unit tested and work flawlessly, and return a single value for any vba array passed in. {big sigh} So I wouldn't make anyone parse through it, but thanks. .... OK, generalities. If the ranges you'd pass to this udf were always effectively 1D, then you could put the following near the top of your function's code. I'll use 'a' to denote the argument that should be processed as an array. Dim t() As Double, n As Long, x As Variant If TypeOf a Is Range Then ReDim t(1 To a.Cells.Count) For Each x In a n = n + 1 t(n) = CDbl(x.Value) Next x a = t 'makes a contain the values as a 1D array Erase t ElseIf Not IsArray(a) Then ReDim t(1 To 1) t(1) = a a = t Erase t End If 'at this point the variable a contains and array no matter if it started 'off containing a range reference or a scalar |
#13
|
|||
|
|||
On 26 May 2005 14:49:00 -0700, "Harlan Grove" wrote:
david cassain wrote... ... Sadly, the code in the MyFunct function is 300+ lines of handicapping business rules that are unit tested and work flawlessly, and return a single value for any vba array passed in. {big sigh} So I wouldn't make anyone parse through it, but thanks. ... OK, generalities. If the ranges you'd pass to this udf were always effectively 1D, then you could put the following near the top of your function's code. I'll use 'a' to denote the argument that should be processed as an array. Dim t() As Double, n As Long, x As Variant If TypeOf a Is Range Then ReDim t(1 To a.Cells.Count) For Each x In a n = n + 1 t(n) = CDbl(x.Value) Next x a = t 'makes a contain the values as a 1D array Erase t ElseIf Not IsArray(a) Then ReDim t(1 To 1) t(1) = a a = t Erase t End If 'at this point the variable a contains and array no matter if it started 'off containing a range reference or a scalar Wow! thanks so much! When I asked the original question I assumed it was merely a syntax issue that needed correction. But you've solved the problem AND provided some branching code as workaround. It still strikes me as a bit weird that I can pass a literal array {1,2,3,..,n} from a worksheet function successfully, but there is no equivilent way to pass a range reference that would work. I still have to wrap my head aroud that... anyways, thanks again Harlan. You probably saved me from 2 days of trial and error agony. dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error | Excel Discussion (Misc queries) | |||
range and cells syntax | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
List Box refer to an array | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions |