0

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?

braX
  • 11,506
  • 5
  • 20
  • 33
Stuart L
  • 51
  • 6

1 Answers1

2

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
FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Thankyou very much for your comprehensive response. *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)* Is this not exactly what my code snippet does? I declare the variant variable (ThisArray) and then immediately test for IsEmpty, which returns False. – Stuart L Oct 01 '21 at 08:50
  • No, you declare it as *Array of Variant*. You could use `ThisArray As Variant` as a variant can hold an array, but if you write `ThisArray() As Variant`, it's already an array (but without any elements). – FunThomas Oct 01 '21 at 08:52
  • Ahhhhh! Got it. Thankyou. – Stuart L Oct 01 '21 at 08:56
  • @StuartL - if you think of `ThisArray() As Variant` as being multiple variables the behaviour of `IsEmpty` is then the same as checking multiple cells at once. In fact, another way of describing a range of cells is *an array of cells*. – Timothy Rylatt Oct 01 '21 at 11:01