CODEWITHSUNDEEP
arraysvbams-word
Stuart L
Stuart L

Reputation: 51

Word VBA - IsEmpty always returns false

I swear this bit of code worked yesterday, but today IsEmpty is always returning False, even right after declaration:

Sub TestSub()
Dim ThisArray() As Variant

MsgBox IsEmpty(ThisArray)

End Sub

I've found another solution (How to check for empty array in vba macro and thanks @ahuth) so this is just out of curiosity - why does IsEmpty always return False for me?

Upvotes: 0

Views: 1048

Answers (1)

FunThomas
FunThomas

Reputation: 29181

IsEmpty is a function that checks if a Variant variable holds any value. A Variant can hold any kind of data (a number, a string, an object reference, a boolean, an array of whatever...). If you assign something to a variant variable, the variant knows which data type it holds at that very moment. However, if you just declare a variant variable, it is initialized with the value Empty, and you can check for that with IsEmpty (or you can use the function Vartype, if it returns 0, that means Empty).

All other variable types are never empty because the have an initial value in VBA (0 for numbers, "" for strings, Nothing for objects). All of this initial values are not empty. Think about it that whenever VBA knows the data type, isEmpty returns False. An array of Variant is not empty (its data type is "Array of..").

The main reason for IsEmpty is to check the content of a cell in Excel. It returns true if the cell has no value. However checking multiple cells at once will always return false, even if all cells are empty.

Dim s As String, l As Long, v As Variant, a() As Variant, r As Range

Debug.Print "String:  ", IsEmpty(s)
Debug.Print "Number:  ", IsEmpty(l)
Debug.Print "Variant: ", IsEmpty(v)
Debug.Print "Array:   ", IsEmpty(a)
Debug.Print "Object:  ", IsEmpty(r)
    
Set r = ActiveSheet.Range("A1")
Debug.Print "Cell:    ", IsEmpty(r)

Set r = ActiveSheet.Range("A1:A3")
Debug.Print "Cells:   ", IsEmpty(r)

shows (assuming an empty worksheet)

String:       False
Number:       False
Variant:      True
Array:        False
Object:       False
Cell:         True
Cells:        False

Upvotes: 2

Related Questions