Returning full contents of an array to excel

K

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
 
T

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top