Having a public variable available in userform / sheet objects

Question

I've spent the last hour reading pages about variable scope in various flavours of excel vba, and could not find a definite documentation reference addressing my scope problem... even though i'm convinced it is such a classic. Oh well, here goes.

I've got a workbook that contains just one sheet and one userform. I have a list of students sitting in column 1 on my sheet. I would like to :

• load this list up into some global Collection variable named students_list (i do this using a Workbook-Open() procedure in the ThisWorkbook object)
• use the contents of students_list to initialize a listbox in my userform
• remove elements from students_list when a button on my userform is clicked on

All i need is a variable that is seen from within my userform's procedures, as well as from inside the ThisWorkbook object.

I tried declaring it as public, global, in the sheet's code, in the userform, in ThisWorkbook, in a separate module dedicated to globals... I just can't seem to find the right way to have the students_list variable visible from everywhere.

What am I missing ? My apologies for this question that should be so basic and yet beats me :-/

Answer 1

Place the declaration of your Public variables inside a Module (use Insert / Module from the menu to create one, if you don't already have one). The scope will then extend to your whole project.

So in a Module (e.g. Module1) have:

Public foo As Integer

And in the worksheet (e.g. Sheet1) code have:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    foo = 4
    MsgBox "foo set to 4"
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox "foo = " & foo
End Sub

---

If you were to place the declaration in the code for ThisWorkbook you would need to reference it as Thisworkbook.foo because, although it is accessible from any part of the code, it is a variable specific to that ThisWorkbook object.

So, in the code for ThisWorkbook have:

Public foo As Integer

And in the worksheet (e.g. Sheet1) code have:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ThisWorkbook.foo = 4
    MsgBox "foo set to 4"
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox "foo = " & ThisWorkbook.foo
End Sub