Hi All, a quick question to VBA coders.
When you declare an object variable and assign it, for example:-
Dim cboDemo as ComboBox
Set cboDemo = Sheets(1).Demo_ComboBox
< .............. code ...................>
Set cboDemo = Nothing
Obviously it's necessary if you intend to change the value of cboDemo and run the same code, but is there any point if it's always assigned to the same combo box as opposed to accessing the combo box directly?
VBA object variables
Once the object has been set you can just refer to it by name either in the same routine or another sub.
Run the below then uncomment the "OtherRoutine Demo_ComboBox" and re-run. The contents will change.
Run the below then uncomment the "OtherRoutine Demo_ComboBox" and re-run. The contents will change.
Code: Select all
Sub test()
Dim Demo_ComboBox As Object
Set Demo_ComboBox = Sheets(1).Shapes("Demo_ComboBox")
With Sheets(1).Demo_ComboBox
.Clear
.AddItem "This"
.AddItem "Is"
.AddItem "A"
.AddItem "Test"
End With
'OtherRoutine Demo_ComboBox
Set Demo_ComboBox = Nothing
End Sub
Sub OtherRoutine(Demo_ComboBox)
With Sheets(1).Demo_ComboBox
.Clear
.AddItem "Runner 1"
.AddItem "Runner 2"
End With
End Sub