Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
david cassain
 
Posts: n/a
Default 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   Report Post  
Harlan Grove
 
Posts: n/a
Default

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   Report Post  
david cassain
 
Posts: n/a
Default

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   Report Post  
david cassain
 
Posts: n/a
Default

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   Report Post  
Alan Beban
 
Posts: n/a
Default

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   Report Post  
Harlan Grove
 
Posts: n/a
Default

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   Report Post  
david cassain
 
Posts: n/a
Default

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   Report Post  
david cassain
 
Posts: n/a
Default

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   Report Post  
Harlan Grove
 
Posts: n/a
Default

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   Report Post  
david cassain
 
Posts: n/a
Default

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   Report Post  
david cassain
 
Posts: n/a
Default

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   Report Post  
Harlan Grove
 
Posts: n/a
Default

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   Report Post  
david cassain
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error Chrism Excel Discussion (Misc queries) 4 May 4th 05 04:06 PM
range and cells syntax mango Excel Worksheet Functions 0 February 22nd 05 01:03 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
List Box refer to an array Andrew Heath Excel Discussion (Misc queries) 2 December 14th 04 03:43 PM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM


All times are GMT +1. The time now is 07:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"