Array UDF
 

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...

Array UDF
 

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)

"John" wrote in message
...
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...

Array UDF
 

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

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

"Bob Phillips" wrote:

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)

"John" wrote in message
...
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...

Array UDF
 

ok, got it thanks Bob

"Bob Phillips" wrote:

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)

"John" wrote in message
...
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...

Array UDF
 

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" wrote:

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)

"John" wrote in message
...
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...

Array UDF
 

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)

"John" wrote in message
...
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" wrote:

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)

"John" wrote in message
...
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...

Array UDF
 

that was it, thanks again....

"Bob Phillips" wrote:

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)

"John" wrote in message
...
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" wrote:

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)

"John" wrote in message
...
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...