Consulting

Results 1 to 17 of 17

Thread: Initializing variables

  1. #1
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location

    Initializing variables

    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

  2. #2
    VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  3. #3
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by geekgirlau
    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

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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

  5. #5
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    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?

  6. #6
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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

  7. #7
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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)

  8. #8
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    @ 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

  9. #9
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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

  10. #10
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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.

  11. #11
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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 ...)

  12. #12
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by Richie(UK)
    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....

  13. #13
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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,

  14. #14
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    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)

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  15. #15
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    UserSpouse is friendly???

  16. #16
    VBAX Regular
    Joined
    Jun 2004
    Location
    USA
    Posts
    10
    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
    Seb

  17. #17
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by 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)
    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...

    Quote Originally Posted by mdmackillop
    UserSpouse is friendly???
    Tee-hee-hee :rofl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •