Public variables

[Michael Simpson]

First of all, my apologies for my question. I know that if I google public + variables + vba I will get 1000's of answers, but based on the way my code "doesn't" work, I'm misunderstanding it.

I am an experienced programmer in other languages, but self-taught in VBA. One of the comments I often see about public variables is, basically, try and avoid using them. Let me explain first why I think I need to use them in this particular case.

I have a Worksheet_SelectionChange event for sheet 1. I can start a data update userform (modeless) and the idea is that, when the user starts this userform via a ribbon-defined program, the code will automatically fill in the controls on the form based on the current row selected on sheet 1. Since it's modeless, they can then select any other row from sheet 1, and its values will be "transferred" and shown on the user form.

I have also defined another userform that allows them to select (via a listbox) which rows from sheet 1 should be included in a word document. For each item in the listbox they select, I then toggle the colouring for the relevant row in sheet 1.

Here's the problem. If they're running the listbox userform, I want to toggle the colouring for the selected row. If they're running the data update userform, I only want to transfer the contents of the row to the userform. I don't want to toggle the possible colouring of the row.

Okay. Logical solution (?) would be to have a global variable indicating whether I'm running the data update form or not, that way, when the Worksheet_SelectionChange kicks in, I can tell whether I'm running the data update form or not.

To my problem (and original question).

I understand (?) that a public variable has to be placed in a module, but what isn't clear to me is whether any/all public variables in that module only are defined when any procedure in the module is run or whether they are "extracted" automatically and defined when the workbook is activated. If the former, then, again logically to me, they definitions should be placed in This Workbook with the initial setting of the variable in (say) Workbook_Activate.

What am I not understanding here ?

 

[RoryA]

It doesn't really make any difference whether you put the variable in ThisWorkbook or in a normal module, except that if you place it in ThisWorkbook, it becomes a public member of ThisWorkbook, so in your code elsewhere you have to refer to ThisWorkbook.VariableName rather than just VariableName.
Given that you are using a userform, I would suggest that you should use a public userform variable and simply test if that Is Nothing rather than using an additional Boolean.

 

[Michael Simpson]

Thanks rorya. I never realized that I had to prefix the variable name with ThisWorkbook.VariableName

 

[Michael Simpson]

A follow-on question (now that my code isn't working as expected). At the top of my userform code I have

Option Base 1
Option Explicit
Public access_currencies As Variant
Public country_col As String
Public tech_sheet As String, pink As String, white As String
Public bLoading As Boolean
[COLOR=Red][B]Public sw_update_data_running As Boolean[/B][/COLOR]
Public old_limit As Currency, old_settlement As Currency
Public sw_close_window As Integer

and in the actual UserForm_Initialize I have

Private Sub UserForm_Initialize()

  ' etc etc etc 
  sw_update_data_running = True
End Sub

In my Worksheet_SelectionChange I have (I assume this is what you meant)

If sw_update_data_running Is Empty Then
        MsgBox "NOT running update data"
    Else
        MsgBox "RUNNING update data"
    End If

but the above fails with run-time error 424 "Object required".

What am I doing wrong ?

 

[ScottR]

Assuming your Userform is called Userform1 it should be

If Userform1.sw_update_data_running=False Then
    MsgBox "NOT running update data"
Else
    MsgBox "RUNNING update data"
End If

</pre>Note the =False rather than is empty as its a boolean which defaults to False. Is Empty will give you a type mismatch.

As for object required that error makes no sense given what you have posted. Where exactly does it break if you debug?

 

[RoryA]

Your Boolean variable should not be declared inside the userform module otherwise when you refer to it a new instance of the form will be created if it isn't already loaded. Put the declaration in a normal module.

 

[shg]

... otherwise when you refer to it a new instance of the form will be created if it isn't already loaded.

What's the analog of that behavior when the variable is in ThisWorkbook or a sheet module?

 

[RoryA]

There isn't one, since you can't unload those.

 

[Michael Simpson]

Hmmmm. Thanks for the help so far, all, but I'm still in the woods.

I changed the code (in Worksheet_SelectionChange) so it became

If Agenda_data_entry.sw_update_data_running = False Then
        MsgBox "NOT running update data"
    Else
        MsgBox "RUNNING update data"
    End If

which worked fine as long as I was running the userform Agenda_data_entry. As soon as I exited the userform and selected a row in the "normal" manner, Agenda_data_entry.sw_update_data_running was False and suddenly Agenda_data_entry's UserForm_Initialize kicked in (which isn't what I want and I suspect is what rorya is referring to).

Going back to what Rorya wrote

Put the declaration in a normal module.

is the part I don't understand and I refer to my first append.

If I put the definition in, say, module1, when/where/how does it get defined.

Is it irrelevant if I put the def. in module1, module2 or anywhere whatsoever ?

Do I have to run "any" procedure in module1 to ensure the variable is defined and available as a public variable to all procedures/modules/userforms/events or are the definitions automagically created/defined at workbook activate time.

This is my stumbling block in understanding public variables.

 

[RoryA]

When you declare it as public in a normal module it is essentially loaded when any code runs in the workbook for the first time (I believe - no way of testing if it actually loads before that!) so it is then available to all procedures in the project.