Array UDF

[Guest]

I have a variable in a UDF that is called cash() ... this variable has 2,3,4
values based on the number of dates input into the function. How do I get
all of those cash values to be output from my UDF?

myfunction = cash() and entering the UDF with Control+Shift+Enter doesn't do
it...

 

[Bob Phillips]

Here is an example

Function myfunc()
Dim ary
Dim rng As Range
Dim i As Long

Set rng = Application.Caller
ReDim ary(1 To rng.Count)
'<=== add your code to generate array of data
ary = Array(1, 2, 3)
If rng.Count > UBound(ary) Then
For i = UBound(ary) + 1 To rng.Count - 1
ary(i) = ""
Next i
End If
If rng.Columns.Count > 1 Then
myfunc = ary
ElseIf rng.Rows.Count > 1 Then
myfunc = Application.Transpose(ary)
Else
myfunc = ary(1)
End If
End Function


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

thanks Bob, what is the line ary=array(1,2,3)

do I want that to say ary = array(cash())?

 

[Guest]

ok, got it thanks Bob

Here is an example

Function myfunc()
Dim ary
Dim rng As Range
Dim i As Long

Set rng = Application.Caller
ReDim ary(1 To rng.Count)
'<=== add your code to generate array of data
ary = Array(1, 2, 3)
If rng.Count > UBound(ary) Then
For i = UBound(ary) + 1 To rng.Count - 1
ary(i) = ""
Next i
End If
If rng.Columns.Count > 1 Then
myfunc = ary
ElseIf rng.Rows.Count > 1 Then
myfunc = Application.Transpose(ary)
Else
myfunc = ary(1)
End If
End Function


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Bob, if I set ary(i) = "" I get an error if I have ary(i) = 0 then it works
fine... any idea why I cannot use "" ???

 

[Bob Phillips]

Have you declared a data type of Long or Integer for your array? I let mine
default to Variant, so it can have numbers or text, and blank.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

that was it, thanks again....

Have you declared a data type of Long or Integer for your array? I let mine
default to Variant, so it can have numbers or text, and blank.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)