VBA - Create empty array

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.

Perhaps you should just check if `dates()` is empty before calling the `ParseDates` function in the first place? — CallumDA, Mar 24 '17 at 17:20
That would be a solution as well, but I would prefer `ParseDates` to work on all input of the correct type. Also I fell that the code get cluttered with all the extra If-statements. — Jonas, Mar 24 '17 at 17:43
I ended up declaring the function-returning-a-possibly-empty-array as `Variant`. This allows me to return `Split("",",")` for an empty array and an array of the type I want otherwise. _PS. How is "Create empty array" a duplicate of "How do I determine if an array is initialized in VB6?"??_ — Nickolay, Feb 13 '18 at 12:24

score 4 · Answer 1 · edited Nov 17 '18 at 04:53

4

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.

edited Nov 17 '18 at 04:53

John

540
2
15

answered Mar 25 '17 at 04:53

Rich Holton

662
5
12

Interesting trick, especially because of the way that `VbVarType` can be changed to extend this to other cases and not just `Date`. I'm not sure if I will ever use this, but it is nice to know of the possibility, so +1 – John Coleman Mar 27 '17 at 20:24

score 3 · Accepted Answer · answered Mar 24 '17 at 17:20

3

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

answered Mar 24 '17 at 17:20

Nathan_Sav

8,466
2
13
20

1

Thanks, I've made a workaround that is similar. I just never had thought that an empty array would be a problem. – Jonas Mar 24 '17 at 17:26

score 1 · Answer 3 · answered Mar 24 '17 at 18:47

1

You specifically mentioned problems in which the calling code needs to iterate over the return value and that iterating over a non-dimensioned array throws an error. Collections don't have that problem. One possibility would be to refactor your code so that it returns a collection (which might or might not have zero elements):

Private Function ParseDates(dates() As String) As Collection
  Dim res As New Collection
  Dim i As Long

  For i = LBound(dates) To UBound(dates)
    res.Add #1/1/2000#
  Next i

  Set ParseDates = res
End Function

Say the calling code has the line:

Set C = ParseDates(dates)

Even if C.Count = 0, the following loop works:

Dim d As Variant

For Each d In C
    'process d
Next d

answered Mar 24 '17 at 18:47

John Coleman

51,337
7
54
119

This seems to be a valid solution. However I ran into a problem trying to implement it. In my code I call a bunch of functions expecting to receive a `Date` object, but collections are composed of `Object` or `Variant` elements, causing the program to throw a `Type mismatch error`. Is there a good way to handle this? – Jonas Mar 27 '17 at 06:52
@Jonas Without seeing the code it is hard to be sure, but there are at least 4 possible solutions, 2 on the callee side and 2 on the caller side: 1) In the parameter list of the receiving function just change `As Date` to `As Variant` 2) Change parameters like `d As Date` to `ByVal d As Date`, 3) Leave the functions as is, but in the calling code wrap the arguments in `CDate()` before passing them, or 4) in the calling code have a `Date` variable say `d`, and assign the variant pulled form the collection to `d` and then pass `d`. – John Coleman Mar 27 '17 at 11:56

Moreno · Answer 4 · 2017-03-24T18:14:02.457

0

Try this:

Private Function ParseDates(dates() As String) As Date()
  Dim res() As Date
  Dim i As Integer
  Dim k%
  k=0
  If UBound(dates) >= 0 Then
    ReDim res(UBound(dates)) As Date
  End If


  For i = LBound(dates) To UBound(dates)
    if dates(i)<>"" then
       k=k+1      
    redim preserve res(k)
       end if
res(k) = #01/01/2000#
  Next i
    if k=0 then
     redim res(ubound(dates))
      end if
  ParseDates = res
End Function

edited Mar 24 '17 at 18:14

answered Mar 24 '17 at 17:24

Moreno

608
1
9
24

Thanks for your reply. Unfortunately, this does not solve the problem as res is only given a dimension if dates contains at least on element. – Jonas Mar 24 '17 at 17:34
So what you want is res to have dimensión either dates has element or not? @Jonas – Moreno Mar 24 '17 at 18:12

VBA - Create empty array

4 Answers4

Linked