VBA object variables

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
Derek27
Posts: 23476
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

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?
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

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.

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
User avatar
Derek27
Posts: 23476
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

Cheers Nigel. :)
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”