syntax to refer to a range as an array?

[david cassain]

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 are: 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(A11,B2)

That formula syntax doesn't errors and Im stuck as to the proper way
to convert a range (A11) 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({A11},B2)

any hints? tips? thanks.
dave

 

[Harlan Grove]

david cassain wrote...
....

this works ---> = MyFunct({23,34,25,13},B2)
this doesn't --> = MyFunct({A11},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(A11,B2)

?

 

[david cassain]

That formula syntax doesn't errors and Im stuck as to the proper way
to convert a range (A11) 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 (A11) to call my function

 

[david cassain]

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(A11,B2)

Thanks for the reply Harlan --

I get a #VALUE! error.
dave

 

[Alan Beban]

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

 

[Harlan Grove]

david cassain wrote...

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.

 

[david cassain]

david cassain wrote...

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(A11,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

 

[david cassain]

here's expected result:
A3 should equal 6 ---> (0+1+2+3)/2

sorry the result should end up 3 not 6!

 

[Harlan Grove]

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(A11+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.

 

[david cassain]

here's my forumla in cell A3:
= MyFunct(A11,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(A11,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 "A11" is the wrong formula syntax.

anyone know the proper syntax to convert an excel range ---> a vba
array so I can call a UDF?

 

[david cassain]

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(inSheet.Range(inStartCell
& ":" & inEndCell)))

End Function

Thanks again for your time, I appreciate it very much.

dave

 

[Harlan Grove]

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

 

[david cassain]

david cassain wrote...
...
...

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