VBA check if object exists before set

Question

What is the best way to check if object exists before using set on it ?

I have many workbooks, they contain checkboxes and optionbuttons. I have an array that contain the list of all possible names of checkboxes and optionbuttons that the different workbooks may have

to make my question clear, let us suppose that i have

sArray(i) = "CheckBox15"

when I do

Set s = .OLEObjects(sArray(i))

is giving me an error 1004 when there is no a checkbox called "CheckBox15" in the active sheet.

what I want in my below code, is to add a line that tells :

if "CheckBox15" exists on current sheet (ws) then set .... is there any command that check if an object exists ?

'ws is the worksheet
Dim s As OLEObject
Dim i As Long

with ws
For i = 0 To UBound(sArray)
Set s = .OLEObjects(sArray(i))
If s.Object.Value = True Then
GetOptionCheck = GetOptionCheck & s.Object.Caption
End If
Next i
end with

Vityata · Accepted Answer · 2019-03-01 14:23:57Z

4

You can build a custom boolean function for a quick check:

Public Function objectExists(ByRef ws As Worksheet, ByVal someName As String) As Boolean

    On Error GoTo objectExists_Error

    Dim someOle As OLEObject
    Set someOle = ws.OLEObjects(someName)
    objectExists = True

    On Error GoTo 0
    Exit Function

objectExists_Error:
    objectExists = False

End Function

The call it like this:

Sub TestMe()

    Dim s As OLEObject
    Dim i As Long
    Dim ws As Worksheet: Set ws = Worksheets(1)
    Dim someArray As Variant
    someArray = Array("CheckBox1", "CheckBox2", "CheckBox3", "CheckBox4")

    With ws
        For i = LBound(someArray) To UBound(someArray)
            If objectExists(ws, someArray(i)) Then
                Set s = .OLEObjects(someArray(i))
                Debug.Print s.object.Caption
            End If
        Next i
    End With

End Sub

answered Mar 1, 2019 at 14:23

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JustGreat Over a year ago

thanks, your solution works fine, I will choose is as selected solution

Collectives™ on Stack Overflow

VBA check if object exists before set

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related