CODEWITHSUNDEEP
vbaexcel
qbodart
qbodart

Reputation: 419

User-defined function returns non-formattable dates Excel VBA

I created a UDF in Excel VBA that returns an multi-dimensional array of dates and doubles. The problem is that I cannot format the dates that are returned.

Here is a simplified example:

Function test(dates as Range)
    Dim results
    Dim i As Integer
    ReDim results(1 To dates.Cells.Count)
    For i = 1 To dates.Cells.Count
        results(i) = dates.Cells(i).Value
    Next
    test = Application.WorksheetFunction.Transpose(results)
End Function

The transpose at the end is just for convenience to have an column output (I press Ctrl+Shift+enter). I you use this simplified example, you will not be able to format the output and it will not be considered as dates stricto sensu.

Any ideas?

Upvotes: 2

Views: 81

Answers (2)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You may try something like this.

Results(i) = CDate(dates.Cells(i).Value)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

Change the results array to doubles:

Function test(dates As Range)
    Dim results() As Double
    Dim i As Integer
    ReDim results(1 To dates.Cells.Count)
    For i = 1 To dates.Cells.Count
        results(i) = dates.Cells(i).Value
    Next
    test = Application.WorksheetFunction.Transpose(results)
End Function

Or change the dates.Cells(i).Value to dates.Cells(i).Value2 which will return the double not the date string:

Function test(dates As Range)
    Dim results
    Dim i As Integer
    ReDim results(1 To dates.Cells.Count)
    For i = 1 To dates.Cells.Count
        results(i) = dates.Cells(i).Value2
    Next
    test = Application.WorksheetFunction.Transpose(results)
End Function

Then format the cells as you desire.

Upvotes: 2

Related Questions