Returning full contents of an array to excel

[Kay]

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 Ogilvy]

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.

 

[Bernie Deitrick]

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]

Tom, Bernie

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