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 ?

 

[Michael Simpson]

Thank you for your explanation Rorya (Rory ?). As you can read from my append, I don't believe I've ever seen an explanation of how/why declaring a public variable in a module should suddenly "take effect" even though the module isn't run. Be that as it may, this is how I changed my code (to me, it makes sense logically)

In a module called public_variables I created the following (please feel free to correct any of my inaccuracies)

Public sw_update_data_running As Boolean
Sub define_public_variables(dummy as string)
sw_update_data_running = False
End Sub

I include the dummy as string for 2 reasons (is there a better way ?):-

1. Defining the procedure as private sub failed in the call below
2. I don't want users to be able to see the macro as an option if they should choose Developer/macros

In ThisWorkbook, I included the following

Sub Workbook_Activate()
    Call hide_technical_sheet
    Call public_variables.define_public_variables("")

 

[RoryA]

You don't need that sub at all unless you specifically want to reset the variable to false whenever you activate the workbook (the default value of a Boolean at startup is False anyway), but adding

option private module

to the top of the module will make all its routines invisible to the macros dialog.
And yes, my name is Rory.

 

[Norie]

Michael

Perhaps slightly off topic but what does the SelectionChange event actually do?

Is that the part of the overall code that does the colour 'toggling'?

If it is, does the 'colour' toggling need to be done using event code?

If it isn't, what is doing that part of the code?

 

[Michael Simpson]

Norie

Perhaps slightly off topic but what does the SelectionChange event actually do? Kicks in if you select any cell/row/column on the relevant sheet (also does this if you do a VBA select on the sheet. For that reason, you need to do a Application.EnableEvents = False before any VBA code that might select something on that sheet)

Is that the part of the overall code that does the colour 'toggling'? Yes. The way I've designed it is that the user can select one/multiple rows that will be sent to a word document. To determine which rows are selected, I look at the coloring of the row. This is where my original question came in. I need to know whether they're running the userform as a front-end or not. If they are, then on a row selection, I don't colour the row and simply "transfer" the contents to the user form. If they are selecting rows for the word doc, I toggle the row colour for in/exclusion in the document

If it is, does the 'colour' toggling need to be done using event code? I think so - the event code is the only (?) method of knowing the user selected a row

If it isn't, what is doing that part of the code? Explained above ??

 

[Michael Simpson]

Thanks Rory for the option private module tip. My VBA is on a learning curve all the time.

 

[Norie]

Michael

Have you tried disabling events when the userform opens and enabling them again when it closes.

Something like this perhaps:

Private Sub UserForm_Initialize()
Application.EnableEvents ' disable events
' rest of initialize code
End Sub
 
[code]
Private Sub UserForm_Terminate()
     Application.EnableEvents = True ' enable events
End Sub

 

[Michael Simpson]

That wouldn't (?) work (note that as a newbie to VBA, I often include a "?" in my comments - I'm never 100% certain that what I say is correct).

I can't disable events when I start the userform. If I did, that wouldn't allow the user to select a row from the sheet (remember, I'm running the form modeless) and have the even code kick in and transfer the data from that row to the form

 

[Michael Simpson]

....(the default value of a Boolean at startup is False anyway) .....

I always like to specify values for whatever I'm doing even if what I'm doing is the default. For example, DB2 order bys have a default of ASCending. I still include the word ASC so anyone reviewing the code knows that the results are sorted ascending (doesn't matter if they're unsure - it's there, in front of them)

In the same way, setting a boolean variable to false tells you the starting value is false (as well as allowing me to find various places where I specifically set the value to something)

 

[Michael Simpson]

Something I still don't understand with the public variables.

Unfortunately, I can't pin it down, but I notice that at times, the public variables I mentioned earlier "lose" their content. I understand that people would think immediately that I've redimmed it somewhere else or I've simply set it to a new value. However .....

in the VBA code, the variable in question is sheet_selection_colour and is defined as 5296274 in define_public_variables.

Nowhere else is it redimmed or reset, and yet, at times, it suddenly becomes empty. Closing/reopening the workbook sets it back to its expected value.

Does anyone have a possible suggestion as to why/where/how it could lose its value ? (I admit I'm doing a lot of debugging with breakpoints, stopping and resting debugging etc - no idea if that would screw things up)

 

[RoryA]

Public variables will lose state if an unhandled error occurs, or if you use End in your code or do anything that causes a reset (you will usually be warned).