Hi
I use Excel 2002 and am stumbling on how to return a VBA array to a
spreadsheet
I have the below function which calcs the workdays between 2 parameter
dates.
When executed it returns only the first element rather than the whole
array
Any suggestions on where I am going wrong? Thanks, Kay

Public Function get_workdays(dteStart As Date, dteEnd As Date) As
Variant

Dim dteTEST As Date
Dim i As Integer
Dim varArray() As Variant
Dim iDays As Integer

iDays = NETWORKDAYS(dteStart, dteEnd)
ReDim varArray(iDays - 1)

varArray(0) = dteStart
i = 1
For i = 1 To UBound(varArray)
varArray(i) = workday(varArray(i - 1), 1)
Next i

get_workdays = Application.WorksheetFunction.Transpose(varArray)

End Function

You need to select as many cells as the array will return and in the same
shape. Put in your formula in the formula bar and then enter with
Ctrl+shift+enter and you should get all your results spread across the cells.

--
Regards,
Tom Ogilvy


"Kay" wrote:

Hi
I use Excel 2002 and am stumbling on how to return a VBA array to a
spreadsheet
I have the below function which calcs the workdays between 2 parameter
dates.
When executed it returns only the first element rather than the whole
array
Any suggestions on where I am going wrong? Thanks, Kay

Public Function get_workdays(dteStart As Date, dteEnd As Date) As
Variant

Dim dteTEST As Date
Dim i As Integer
Dim varArray() As Variant
Dim iDays As Integer

iDays = NETWORKDAYS(dteStart, dteEnd)
ReDim varArray(iDays - 1)

varArray(0) = dteStart
i = 1
For i = 1 To UBound(varArray)
varArray(i) = workday(varArray(i - 1), 1)
Next i

get_workdays = Application.WorksheetFunction.Transpose(varArray)

End Function

Kay,

Select at least as many cells in a column as dates that you expect to have returned, then enter

=get_workdays(A1, A2)

and press Ctrl-Shift-Enter

You are returning an array of values from your UDF, and need to array enter it to have it work.

HTH,
Bernie
MS Excel MVP


"Kay" wrote in message
ups.com...
Hi
I use Excel 2002 and am stumbling on how to return a VBA array to a
spreadsheet
I have the below function which calcs the workdays between 2 parameter
dates.
When executed it returns only the first element rather than the whole
array
Any suggestions on where I am going wrong? Thanks, Kay

Public Function get_workdays(dteStart As Date, dteEnd As Date) As
Variant

Dim dteTEST As Date
Dim i As Integer
Dim varArray() As Variant
Dim iDays As Integer

iDays = NETWORKDAYS(dteStart, dteEnd)
ReDim varArray(iDays - 1)

varArray(0) = dteStart
i = 1
For i = 1 To UBound(varArray)
varArray(i) = workday(varArray(i - 1), 1)
Next i

get_workdays = Application.WorksheetFunction.Transpose(varArray)

End Function

Tom, Bernie

Thanks for your direction - this has been hurting my head all day!!



On 13 Jul, 13:57, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Kay,

Select at least as many cells in a column as dates that you expect to have returned, then enter

=get_workdays(A1, A2)

and press Ctrl-Shift-Enter

You are returning an array of values from your UDF, and need to array enter it to have it work.

HTH,
Bernie
MS Excel MVP

"Kay" wrote in message

ups.com...



Hi
I use Excel 2002 and am stumbling on how to return a VBA array to a
spreadsheet
I have the below function which calcs the workdays between 2 parameter
dates.
When executed it returns only the first element rather than the whole
array
Any suggestions on where I am going wrong? Thanks, Kay

Public Function get_workdays(dteStart As Date, dteEnd As Date) As
Variant

Dim dteTEST As Date
Dim i As Integer
Dim varArray() As Variant
Dim iDays As Integer

iDays = NETWORKDAYS(dteStart, dteEnd)
ReDim varArray(iDays - 1)

varArray(0) = dteStart
i = 1
For i = 1 To UBound(varArray)
varArray(i) = workday(varArray(i - 1), 1)
Next i

get_workdays = Application.WorksheetFunction.Transpose(varArray)

End Function- Hide quoted text -

- Show quoted text -