Conditional guard IF Not (x = Empty) Then

Question

I have the following

Public Sub BreakAllLinks(ByRef aWkBook As Excel.Workbook)
Dim Link As Variant
Dim myLinks As Variant

myLinks = aWkBook.LinkSources(Type:=Excel.xlLinkTypeExcelLinks)
If Not (myLinks = Empty) Then
    For Each Link In myLinks
        aWkBook.BreakLink Name:=Link, Type:=Excel.xlLinkTypeExcelLinks
    Next Link
End If

End Sub  'BreakAllLinks

If myLinks is empty then it works well and avoids the For Each loop but if myLinks contains some links then I get the following error

Runtime error '13'

What is wrong with If Not (myLinks = Empty) Then?

score 5 · Accepted Answer · edited May 23 '17 at 11:59

LinkSources returns either Empty or an array.

You cannot compare an array with Empty using the equality operator.

The documentation shows you the right way of checking the result - using the IsEmpty function.

The function succeeds regardless of the value type stored in your Variant.
If e.g. you had a Nothing in there, you would get error 91.
Or, if you had an object reference in there, your comparison would try to fetch the default property of the stored object and compare that to Empty.

Which is why you should never check for = Empty really, and only use IsEmpty.

Conditional guard IF Not (x = Empty) Then

1 Answers1