Function IsEmpty() for Array

Question

I would like test if my array is empty or not.

I tried to use the function IsEmpty(), but the function returns always False. And don't enter in the if condition.

My code below :

Sub Freeze()

    Dim tab_freeze() as variant

    If IsEmpty(tab_freeze) Then
        ReDim tab_freeze(0)
    Else
        ReDim Preserve tab_freeze(UBound(tab_freeze) + 1)
        tab_freeze(UBound(tab_freeze)) = "As you want here"
     End If

End Sub

I would like function to know if my array tab_freeze is empty or not. Or understand why IsEmpty(tab_freeze) returns False the first time.

score 4 · Answer 1 · edited Jun 11 '19 at 14:12

IsEmpty is a standard library function that returns True when a variable of type Variant has an Empty subtype (Variant/Empty), which happens to be the case for Range.Value when there is no content in a cell.

In your case, you are not dealing with a Variant, you are dealing with an dynamic array of Variants. Dynamic means that you first have to allocate space for a specific number of elements using the command ReDim. You could use IsEmpty(tab_freeze(1)) to check if the first element was initialized - but that's probably not what you want.

Unfortunately, there is no build-in function on VBA that checks if a dynamic array is already allocated. There are quite some suggestions how to check, see for example How to check for empty array in vba macro. Personally, I use the following routine. It checks if a variable is declared as Array and its bounds. If an array is not allocated, LBound and UBound return a runtime error (and therefore the function will not get True). But it also handle the return-Value of split("") - which will give an LBound of 0 and UBound of -1

Function IsArrayAllocated(arr As Variant) As Boolean
    On Error Resume Next
    IsArrayAllocated = isArray(arr) _
                   And Not IsError(LBound(arr, 1)) _
                   And LBound(arr, 1) <= UBound(arr, 1)
End Function

Thanks, this routine is good, but it's not what I want, pass by `On Error Resume Next` isn't clean enough. — Hippolyte BRINGER, Jun 11 '19 at 13:44
FWIW you're much better off using `On Error Resume Next` in its own scope and across a single executable statement than [sacrificing compile-time binding and validation with making everything `Variant`](https://stackoverflow.com/a/56545140/1188513). — Mathieu Guindon, Jun 11 '19 at 14:55

score 2 · Answer 2 · answered Jun 11 '19 at 13:20

Depending on what you understand under "empty array", there could be different answers. If you consider Dim tab_freeze() as variant as an empty array, as far as it has no dimensions in it, then this could work:

Public Function IsArrayEmpty(myArray As Variant) As Boolean

    On Error GoTo IsArrayEmpty_Error

    Dim tempVar As Variant
    tempVar = myArray(0)

    IsArrayEmpty = False

    On Error GoTo 0
    Exit Function

IsArrayEmpty_Error:

    IsArrayEmpty = True

End Function

There will be problems, if someone is using Option Base 1, thus the first element is not a 0, but 1. In general, this is considered, undimensionised array, but you may call it as you like (if you program alone, which is probably the case in VBA :) ).

If you consider Dim testArray(10) as Variant as an "Empty" array, then this would work:

Public Function IsArrayWithValuesAtAll(myArray As Variant) As Boolean
    IsArrayWithValuesAtAll = IsError(Application.Match("*", (myArray), 0))
End Function

score 0 · Answer 3 · answered Jun 11 '19 at 13:26

For testing whether or not an array is empty, I usually Join() it with no delimiter and test its length.

Sub Freeze()

    Dim tab_freeze() As Variant

    Dim jArr As String

    jArr = Join(tab_freeze, "")

    If Len(jArr) = 0 Then
        ReDim tab_freeze(0)
    Else
        ReDim Preserve tab_freeze(UBound(tab_freeze) + 1)
        tab_freeze(UBound(tab_freeze)) = "As you want here"
    End If

End Sub

Although, it's good to note that this would return the same for an Empty array as it would an undimensioned one.

score 0 · Answer 4 · answered Jun 11 '19 at 13:42

0

Thanks for all answers.

But I found the solution. Vba consider that for Dim tab_freeze() As Variant the tab_freeze is not empty, but UBound(tab_freeze) call an error.

So I just removed the parenthesis in the Dim :

Sub Freeze()

    Dim tab_freeze as variant

    If IsEmpty(tab_freeze) Then
        ReDim tab_freeze(0)
    Else
        ReDim Preserve tab_freeze(UBound(tab_freeze) + 1)
        tab_freeze(UBound(tab_freeze)) = "As you want here"
     End If

End Sub

answered Jun 11 '19 at 13:42

Hippolyte BRINGER

792
1
8
30

3

By removing the parentheses in the `Dim` statement, you are moving the goalpost: you're not looking at a dynamic array anymore, but a `Variant`. – Mathieu Guindon Jun 11 '19 at 14:13
Not at all, `Dim tab1() As Variant` and `Dim tab2 As Variant` are different but when you `redim` there are the same type. After `ReDim tab2(0)` the TypeName are the same `MsgBox TypeName(tab2)` – Hippolyte BRINGER Jun 11 '19 at 14:38
1

You're missing the point: a `Variant` can hold *anything*, including an array. A `Variant()` is statically declared as being an array holding `Variant` items. By removing the parens you made the array no longer be understood by VBA at **compile time** as an array. – Mathieu Guindon Jun 11 '19 at 14:41
@MathieuGuindon I don't understand why is it very different ? In `dim tab() as variant` each items will be variant, no ? So why is it so better than `dim tab as variant` for the compilator ? – Hippolyte BRINGER Jun 11 '19 at 15:51
1

Every single answer you received has tried to maintain the declared type as an array, so that the compiler knows `tab` is an array. By declaring it as a `Variant`, the compiler no longer knows `tab` is an array - any call against `Variant` is late-bound (resolved at run-time), and the compiler will be happy with `tab = "hi"` *and* `tab = 42`, even in the same scope, and the variant subtype will change accordingly at run-time: you can't do that with an *actual* array. – Mathieu Guindon Jun 11 '19 at 15:55

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Why not use a little VB magic the user Merri talked about in this vbforums thread instead of using a Variant to replace an Array of type Variant.

One of the common problems when dealing with arrays is to know when the array has been initialized and when it isn't. Luckily there is an easy native VB code solution:

Not Not ArrayName

What this does is to take the 32-bit pointer value in the array variable, mirror the bits, and then mirror them again. You now effectively know the pointer to the safe array structure. As a side effect you also know whether you can access the array with LBound and UBound.

Private Sub ArrayTest()
    Dim testArr() as Variant
    ' Prints False
    Debug.Print Not Not testArr

    ReDim testArr(0)
    ' This prints True
    Debug.Print Not Not testArr
End Sub

Merri also talked about problems that could occur, which I haven't stumbled upon yet, and we have to keep in mind that the post is about Visual Basic and not Visual Basic for Applications. (Mind you, I'm paraphrasing)
Apparently in some situations an error will be raised when using this method, which can be circumvented by running a simple Dim IDEbug() As Long: Debug.Assert Not IDEbug Or App.hInstance once before in the VB IDE.
Quoting again:

What happens here is that first we make a call to push up the possible error condition. Then we make a call to App.hInstance: we could make a call pretty much to any VB method, but since hInstance returns a Long number we use that. This, for whatever reason, makes all the following Not ArrayName calls work flawlessly. After the line has once executed in the IDE you can even comment the line and it's effects still remain. Only closing VB and opening it again will reset the condition so that you need to make the call again.

I want to emphasize that I did not come up with this solution, but it helped me tremendously. I also want to point out that I never executed the Debug.Assert Not someArray Or Application.Hinstance and have yet to encounter any issues. This might obviously be related to the fact that VBA is a tiny bit different from VB, but I can't say that for sure.

Thanks for this answer, `If (Not Not tab_freeze) = False Then` works very fine for me ! — Hippolyte BRINGER, Jun 11 '19 at 16:08

Function IsEmpty() for Array

5 Answers5

Linked

Related