Initializing variables

[johnske]

This is probably something very basic, but I dunno how it's done as I've never ran into this problem before....

When you have a project with a LARGE number of integer variables declared as public and you want to set them all to zero each time the sub is run. Is there some simple way of doing this like "Put all variables of type_integer = 0" instead of listing them all as: a=0: b=0: c=0: d=0: e=0: f=0: g=0: h=0:....and so on ad infinitum?

I know you could maybe do something like have integer variables with names like a1, a2, a3, a4, a5, a6,...etc and set them to zero by running a for_to_next loop at the start of the sub (e.g. something like For N = 1 to 100 a & N = 0 Next N) but not when they're "descriptive" type names...

TIA

[geekgirlau]

My first question is do they really need to be Public? If the variables are declared within a procedure, they will be reset to zero once the procedure stops running. Even if the procedure is called multiple times, each time it will start with the values set to zero.

[johnske]

My first question is do they really need to be Public? ......

The short answer is YES, they need to be public

Was kinda hoping there was a shortcut way to reset them all, it would make the code much shorter (and being the lazy type, involve much less typing) but if not, Che Sera Sera

[mdmackillop]

Hi Johnske.
If you put all the names in an array
PubVars = array("abc", "def", "ghi")
Can you do a "For each" reset? (haven't tried it so no idea if this is possible.)
MD

[SJ McAbney]

I fail to see how these variables need to be public if they are being used within a procedure and require resetting to 0 on each execution of the subroutine. Can you please explain your thinking?

[Richie(UK)]

John,

Its often possible (and preferable) to pass variables between routines as arguments rather than making use of Public variables.

This is more efficient in terms of resources consumed (Public variables are quite 'expensive') and, more importantly to my mind, makes your code easier to follow for others and easier to maintain for you.

For example, compare this:

Public iPub As Integer

Sub TestPub1()
    iPub = 123
    Call TestPub2
End Sub

Sub TestPub2()
    MsgBox iPub
End Sub[/vba]To this:[vba]Sub TestArg1()
    Call TestArg2(456)
End Sub

Sub TestArg2(iArg As Integer)
    MsgBox iArg
End Sub

You could, of course, declare a variable within TestArg1, set it and then pass the variable to TestArg2 - like this:

Sub TestArg1()
Dim iTest As Integer
    iTest = 789
    Call TestArg2(iTest)
End Sub

Sub TestArg2(iArg As Integer)
    MsgBox iArg
End Sub

HTH

[Jacob Hilderbrand]

Adding on to what MD stated.

Let's say you have an array variable

Dim x()    As Integer

Then when you want to use or reuse it just use.

Redim x(1 To 20)

[johnske]

@ SJ McAbney: They are being used THROUGHOUT the project and thus need to be declared as public. i.e. they're not just being used in a single subroutine or module.

Some controls on userforms use the variables for display and/or calculation purposes while other controls on other userforms are used to obtain further user-defined values.

Based on what the user requires at the time, there are several main subs and a number of smaller subs that then use the variables to perform calculations that are then displayed for the users decision as to what they want to be done with it next. i.e. quit, continue, populate spreadsheet etc.

However once that decision has been made and the procedure ends, the variables need to be set to zero for the next time the whole thing is started up again.

But hey, it's no big thing, I just thought I may be missing a more elegant way of setting them to zero than using a long list of friendly names saying things like amountdue = 0: cashpaid = 0: chequepayment = 0: refund = o: change = 0: etc etc etc

[johnske]

Many thanx to all for their responses, but for the purpose intended it seems I'll just have to do it the "long" way.

I suppose the old For_To_Next loop idea for resetting array variables gave away how ancient my coding skills are eh? But I will certainly keep all your suggestions like Dim/ReDim and Call in mind for future reference when using VBA.

Even so, using variables like X goes against all I was taught many years ago. My lecturers way back then used to mark us quite down severely for using variable like X1, X2, X3 etc, insisting we use longer userfriendly names like UserSpouse, HotBread, Bottles, Neighbour etc instead wherever possible.

Their argument being that these names make the code much easier to read, debug, and follow, and don't really slow down a high speed computer all that much...I tend to agree with them now and still try to avoid the "X" to keep my code as transparent as possible

[Richie(UK)]

Hi John,

I entirely agree with the naming approach that was suggested by your lecturers and adopted by you. A variable name that indicates both its data type and its intended use can make it much easier to follow the flow of a routine. I would suggest that you retain this approach.

However, having explained to you how to use Public variables I'm sure the lecturers would then have proceeded to tell you (or indeed, may already have told you) to make the scope of your variables as narrow as possible.

I feel that you should try to make each routine as self-contained as possible. For example, its far easier to understand the flow of a sub receiving two well-named arguments than it is to get half way through the sub and be confronted by two Public variables and try to remember which routine last set their values. As well as aiding readability and maintainability this also makes it easier for you to 'cut and paste' useful routines from one project to another without having to think about what Public variables you also need to establish.

We all tend to develope our own preferences as our coding skills increase - some variations make little difference, others can make a substantial difference. I would suggest that, in the long-term, a change of approach in this area would benefit you greatly. Just my 2p.

[Jacob Hilderbrand]

I have to agree with Richie. I very rarely use public variables, and just pass variables along when I call another sub/function.

Even with userforms you can load values to it before the userform initializes so you can pass variables along to it. If I have a userform that may need some value passed to it, I store it in an invisible textbox on the userform, then I can retrieve the value if I need it.

I also agree with using long user friendly names. Although I do use x, y, and z for loop counters (For x = 1 To ...)

[johnske]

Hi John,
....its far easier to understand the flow of a sub receiving two well-named arguments than it is to get half way through the sub and be confronted by two Public variables and try to remember which routine last set their values. As well as aiding readability and maintainability this also makes it easier for you to 'cut and paste' useful routines from one project to another without having to think about what Public variables you also need to establish....
.... Just my 2p.

Thanx for the remarks, and I really do understand where you're coming from. I wont argue with what you're saying, but at the same time we were also taught to be extremely free with comments...trying to remember exactly which previous routine has set the values of the public variables has never presented a problem to me when it is has comments such as: '//(this value comes from....) or simply: '//(from...) and others such as '//(required for...) inserted on the same line as the variable helps one see where it's going TO - it would also seem to be simpler and much more direct approach than using additional code to "call up" a variable...but there are always exceptions to rules that may require one to use different approaches from time to time....

[Richie(UK)]

Hi John,

Well, there's a lot to be said for the 'If it ain't broke, don't fix it!' approach. And if your code works and you are happy with it then there is certainly no need to change anything.

Do me a favour, though, if you will. When you get five minutes, try the 'passing arguments' approach on a small project instead of the 'Public variables' approach and see how you get on (still using the descriptive variable names and copious comments).

I do realise that a change of approach can be a little daunting and I well remember being in exactly the same scenario that you are currently in. I was fortunate enough to see some posts by a very talented MS MVP who was strongly advocating the 'passing arguments' approach. I tried this 'alien' approach and, although it was a little uncomfortable at first, soon came to realise its advantages.

Best wishes,

[shades]

And as John Walkenbach notes, "In general, local variables are the most efficient because VBA frees up the memory when the procedure ends." (Excel 2002 Power Programming with VBA)

[mdmackillop]

UserSpouse is friendly???

[sebastienm]

Hi,
Sorry Johnske, no shortcut for your issue.
Concerning the conversation above:
For arrays, you can re-initialize them in one shot using the Erase statement. However, you need to read closely the online help on this, 'cause the behavior of this statement depends on the type of array (dyn vs fixed) and its data type.

Regards,
Sebastien

[johnske]

And as John Walkenbach notes, "In general, local variables are the most efficient because VBA frees up the memory when the procedure ends." (Excel 2002 Power Programming with VBA)

GOOD quote - noted for future reference - hey, even though the initial question wasn't really that big a "problem" for me, it's great to see that it's inspired such a lively discussion about public vs private variables...

UserSpouse is friendly???

Tee-hee-hee :rofl