Prudent way of 'declaring' and 'setting' variables in a Userform?

Hi fellow forum members,

Introduction
I have a file in which I register all the people to whom I send a (birthday)card.
Also I register to whom, when and which text and/or poem I have sent.
To do all of that, the Excel file has 4 sheets, of which 1 is hidden (not really important now), and these are:
- Card Text (columns: year, name, country, card text, poem text)
- Poem Text (columns: poem number, poem text, country code)
- Personal credentials (columns: name, country, deceased, columns with years and poem numbers)

The Userform is my main input conduit, I can:
- select a surname
- select a first name
- select a country
- select a poem number, which gives the corresponding poem text
- add/change/delete personal credentials

Userform summary
-------------------------------------------------------------------------------------------------
► At the very start, below "Option Explicit" (total of: 31x Module-variables)
-------------------------------------------------------------------------------------------------
6x Module STRING-variables -------> (e.g. "Private m_sSurname As String")
5x Module INTEGER-variables ------> (e.g. "Private m_iFirstSurnameRow As Integer")
10x Module BOOLEAN-variables ----> (e.g. "Private m_bStartDate As Boolean")
1x Module NEW CLASS-variables ---> (e.g. "Private m_oColor As New clsColoring")
5x Module LISTOBJECT-variables ---> (e.g. "Private m_oTbl1 As ListObject")
4x Module WORKSHEET-variables --> (e.g. "Private m_WS1 as Worksheet")

-------------------------------------------------------------------------------------------------
► In the Sub "Userform_Initialize" (total of: 9x "settings")
-------------------------------------------------------------------------------------------------
4x WORKSHEET settings ------------> (e.g. "Set WS1 = cnCardsNL")
5x TABLE NAME settings ------------> (e.g. "Set m_oTbl0 = WS1.ListObjects(1)")

-------------------------------------------------------------------------------------------------
► Other (meaningless?) totals in the Userform
-------------------------------------------------------------------------------------------------
36x Private Subs
98x variables within the 36x SUBs

Question
"Is this way of programming prudent or just bad programming ?" (see: attachments)
In other words: do I 'declare' and 'set' as many variables as possible as Module variables or is it more prudent to 'declare' and 'set' them in the appropriate Subs?

Note: it seems the Userform loads (reacts) quicker when as many variables as possible are 'declared' and 'set' as Module variables....!

It would be much appreciated if someone could shed some light on programming protocols / rules / etiquette.
Thanks in advance and.... I do apologies for this rather elaborate post, sorry.

Originally Posted by Irmaxx

In other words: do I 'declare' and 'set' as many variables as possible as Module variables or is it more prudent to 'declare' and 'set' them in the appropriate Subs?

Declare them in the subs where they are used.

It is best to avoid declaring them at the top of the module, unless you have a design that requires them to be used globally throughout the module, that is, one sub sets the value, then another sub later needs to read that value that the first sub set. However, that is often done because it's easier, not because it's good design. Global variables create an undesirable degree of data coupling.

Note: it seems the Userform loads (reacts) quicker when as many variables as possible are 'declared' and 'set' as Module variables....!

I have not tested this but it doesn't make sense to me. When the form loads it has to load memory for all of those variables. Memory is not allocated for variables in Sub until the Sub is actually called at runtime. Also, allocating memory for variables when the form is loaded is extremely fast so I think you wouldn't even notice unless you were doing a large-scale performance test.

@6StringJazzer
Thanks a lot for your swift reply, much appreciated!

Okay... and what about the 'assigning' of objects in the Sub "Userform_Initialize" for the whole Module (Userform)?

Out of the 36 Subs, 7 of them use one or more of those 'assignments' which are now situated in the Sub "Userform_Initialize".
So, that means that everyone of those 7 Subs has to have one or more separate 'assignments' like:
--> Set m_oTbl0 = WS1.ListObjects(1)

This is better, yeah?

BTW
Is it necessary to end those Subs with:
--> Set m_oTbl0 = Nothing

Originally Posted by Lastejp

Pass the object variables by Reference (ByRef) into the other Subs. This doesn't require additional memory and keeps the Subs modular. Also, by doing so you will NOT set the Object = Nothing

This is the best practice. (Note: ByRef is the default mode for VBA. The other mode is ByVal which requires the keyword.)

@Lastejp and @6StringJazzer
Thank you for your reply

Pass the object variables by Reference (ByRef) into the other Subs. This doesn't require additional memory and keeps the Subs modular. Also, by doing so you will NOT set the Object = Nothing

This I already know and I use this method of programming a lot.
But my main issue here is whether or not I should 'declare' and 'set' objects every time in every Subs as they are needed OR like I did in the attachments in the beginning to be used everywhere?
And when I 'declare' and 'set' objects every time in every Subs as they are needed, do I end those Subs with Set Object = Nothing?

The data structure in the 3 sheet is very strait forward and like this:
- sheet "Card Text" contains the following columns: year, surname, first name, country, poem number, poem text, card text
- sheet "Poem Text" contains the following columns: poem number, poem text, country code
- Sheet "Persons" contains the following columns: surname, first name, country, deceased, columns with years to register the poem numbers

There are NO functions, just plain text and numbers AND I am using Excel Tables.
The Userform just helps out by filling the sheet in a uniform way (the layout of the data) and to maintain sheet protection without the help of the user.

BTW, how do I create a Class for declaring objects which I then can pass?

I am not foreseeing any expansion, this file is just for registering the events of sending a card and what has been written on those cards and to whom.
So no complicated setup or structure.

I do use very simple classes, but I am not expert enough to begin to understand your quote:

Looks like you could create three classes: Person Class, Card Class, and Poem Class. The Person class could contain a collection of the Card class, and the Card class could contain the Poem class.

I am sorry, I am an autodidact and I am programming (as a hobby) for the better part of 16 years and
I have just recently 'discovered' classes.... I am still struggling with the concept and especially how to make use of them, implement them.

I am not going to ask you to enlighten me in how to do that, because that would probably take 'for ever'...
I will take '6StringJazzer' and your earlier advise, being:

Declare them in the subs where they are used.

This is the original way (see quote above) I programmed it, I just thought it would be more readable and less code if I did it the other way.... my bad!

@6StringJazzer
I forgot to ask: "What does your earlier remark, see quote below, entail?"

Global variables create an undesirable degree of data coupling.

Originally Posted by Irmaxx

@6StringJazzer
I forgot to ask: "What does your earlier remark, see quote below, entail?"

Global variables create an undesirable degree of data coupling.

Coupling is how tightly different modules are connected together by virtue of how they communicate. The most desirable coupling is loose, to keep modules as independent as possible from other modules. This minimizes the chance that making a change in one module will require a change in another module, and also makes tracing data easier in case of a bug.

The loosest coupling is passing variables as arguments. This creates a kind of a contract with a Sub that says, "Here is the data you will give me, and here is the data I will give you back." Nothing else happening outside the Sub should affect it.

Common data coupling is tight coupling. When several subs share the same data, a change in how one Sub uses data that could affect any other sub that shares the data.

There are many other types of coupling.

This I already know and I use this method of programming a lot.
But my main issue here is whether or not I should 'declare' and 'set' objects every time in every Subs as they are needed OR like I did in the attachments in the beginning to be used everywhere?

If you declare everything once globally, then use them everywhere, then there is no contract. The sub could get something that it wasn't expecting. Here is an example.

Please Login or Register  to view this content.

The programmer's original intent was to read a value, calculate using it, then write it back. But there is nothing that enforces that is the way the code has to be used. If another line of code is added

Please Login or Register  to view this content.

Now an unexpected value appears in the cell, and you have to read every line of code in every sub to figure out what may have happened.

Please Login or Register  to view this content.

This is kind of an oversimplified example. When you get more complex code that uses global variables it can be a nightmare to diagnose when you get a result you don't expect.

And when I 'declare' and 'set' objects every time in every Subs as they are needed, do I end those Subs with Set Object = Nothing?

You should not have to set an Object to Nothing at the end of a Sub but many people feel it is a good practice. When you create an object, space is allocated dynamically, and the variable points to that space. When the Sub exits, the space is still allocated, but the variable is gone so nothing points to it anymore. When the system discovers this condition it will deallocate the space. This process is called "garbage collection." There is some discussion about how good garbage collection is in VBA. I have never had a problem but with complex programs it is possible that space is not deallocated in a timely manner, or deallocated ever. When unused space is not deallocated it creates what is known as a memory leak.

So it never hurts to set your objects to Nothing.

@6StringJazzer

First of all: WOW!
This is exactly the kind of explanation I was hoping for, very illuminating. Thank you very much.

Secondly:

If you declare everything once globally, then use them everywhere, then there is no contract. The sub could get something that it wasn't expecting. Here is an example.

I understand what you are saying in the above quote and in the examples you presented, but what I was talking about
was specifically global Module variables declaring only(!) 'Worksheet Codenames' and corresponding 'ListObjects' (Excel Table names).

Questions
#1. Is using those kind of declarations also risky?
#2. And why is declaring at the top of the Module NOT good design? And I am referring to my examples in the attachments about declaring 'Worksheet Codenames' and corresponding 'ListObjects'.

It's hard for to comment just seeing declarations and not how they are used. I see a screenshot but I don't see an actual file attached, so I can't see all your code.

I can't make a blanket statement that x is always bad or y is always good. It all depends on context.

One other thing I will add that is not directly mentioned in this thread. Using ActiveSheet, ActiveWorkbook, Selection, and ActiveCell tend to attract errors, since those are built-in global objects. I have seen many bugs because code in one place assumed something will be active but code in another place had a different idea.

Do you need an actual file with all the sheets and all of its code?
Or is a TXT file with the code of only the Userform sufficient?

One other thing I will add that is not directly mentioned in this thread. Using ActiveSheet, ActiveWorkbook, Selection, and ActiveCell tend to attract errors, since those are built-in global objects. I have seen many bugs because code in one place assumed something will be active but code in another place had a different idea.

I am aware of this and I am trying to avoid those properties. Not an easy task.

Ideally I would like to have the file, if you feel you can share it. That is the easiest way to provide all the code with the appropriate context.

Can I do that privately?
It is quite extensive and contains a lot of privacy sensitive data.... I would have to 'clean' the data up and also I have to translate some data from Dutch to English.