Check if variant is null in VBA

Question

I am passing a variant as parameter into a function

For simplicity I have put up this sample functions to explain my situation

Public Function test()
Dim arr() As String
testing (arr)
End Function

Public Function testing(ar As Variant)
If ar = Empty Then
    MsgBox ("Hello")
End If
End Function

Sometimes the FilesDetails will be null, during that time the function is returning "For loop not initialized" error

How can I check if the variant is null?

I tried UBound(FilesDetails), FilesDetails.Count,IsNull(FilesDetails) but no luck

Answer 1

I solved it by using the below method

Public Function test()
Dim Arr(1) As String
Arr(0) = "d"
Dim x As Boolean
x = IsArrayAllocated(Arr)
End Function


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

The isArrayAllocated function returns true if its not null

Answer 2

Try this :-)

Dim var As Variant

If var = Empty Then
    MsgBox "Variant data is Empty"
End If

or

If IsEmpty(var) Then
   MsgBox "Variant data is Empty"
End If