1

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 :-/

Community
  • 1
  • 1
Guillaume
  • 11
  • 1
  • 2
  • You could use a `Range` of the spreadsheet to store the list and be accessible by all the different elements of your VBA code -- essentially from any sheet and any module. If you don't want this scratch area to be visible, start it out in column BB and/or set the font color to white so a user won't notice it. – PeterT Oct 16 '16 at 18:53
  • 1
    If you declared `students_list` as `Public` in a module, the variable **should** have been available to all procedures within the project. See http://stackoverflow.com/a/3815797/6535336. (The only exception would be if you declared another variable with the same name in some other place - then that second variable would be being referred to within the scope of the second declaration.) – YowE3K Oct 16 '16 at 19:14
  • No pb with having the scratch area visible, actually that's the way I load up values into my `students_list` collection... but that's not the issue here i'm afraid. – Guillaume Oct 18 '16 at 10:34
  • @YowE3K: yes, like you said, it should have been :) I've tried testing with minimal amount of code : * in the ThisWorkbook code, just the one line : `Public foo As Integer` * in sheet1's code : `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` – Guillaume Oct 18 '16 at 10:44
  • Ok, sorry for the comment layout, can't seem to make lists or line breaks work ok in comments. Anyway, I just wanted to add that I do not get the expected message "foo = 4". Now please yell at me and point me to my obvious mistakes. – Guillaume Oct 18 '16 at 10:56
  • I have added an answer but, in short, a variable within an Object has to be referred to with Object.Variable syntax if being accessed from outside the Object, but a variable within a Module can be referred to by just the variable name. – YowE3K Oct 18 '16 at 18:38

1 Answers1

3

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
YowE3K
  • 23,852
  • 7
  • 26
  • 40