VBA - Create empty array [duplicate]

Question

I have a function that takes an array of strings and map each string to a Date instance. The function boils down to the following code.

Private Function ParseDates(dates() As String) As Date()
  Dim res() As Date
  Dim i As Integer

  If UBound(dates) >= 0 Then
    ReDim res(UBound(dates)) As Date
  End If


  For i = LBound(dates) To UBound(dates)
    res(i) = #01/01/2000#
  Next i
  ParseDates = res
End Function

The function works just fine as long as the argument dates is nonempty. When dates is empty res is not given a dimension. As a result, the returned value is not enumerable causing to users of this function to crash if the result is enumerated in a loop.

parsedDates = ParseDates(input) 
For i = 1 To UBound(parsedDates) ' Suscription out of range
  ...

How do I instantiate and return an empty array, when dates is empty?

If you call Split("",".") you receive an object with type String(0 to -1). I need my function to return a object of type Date(0 to -1) as Date() is not an actual array.

I have tried with ReDim res(-1) this causes an Subscript out of range error.

Answer 1

I've used something like this in the past.

Public Function IS_ARRAY_EMPTY(arrInput As Variant) As Boolean

Dim lngTemp As Long

On Error GoTo eHandle

lngTemp = UBound(arrInput)

IS_ARRAY_EMPTY = False

Exit Function

eHandle:
    IS_ARRAY_EMPTY = True

End Function

Answer 2

This seems to do the trick:

Private Declare Function EmptyDateArray Lib "oleaut32" Alias "SafeArrayCreateVector" (Optional ByVal vt As VbVarType = vbDate, Optional ByVal lLow As Long = 0, Optional ByVal lCount As Long = 0) As Date()

Function emptyDate() as Date()
    emptyDate = EmptyDateArray()
End Function

Based on an answer by user wgweto for this question at VBForums.