Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VBA Variables available entire workbook (2000 SR1)

    I would like to have variables declared and calculated, and then made available to every module and procedure within the workbook. I currently have the following statements set up in the 'General / Declarations' section of one of my modules; Public MainReportName As String, Public NewReportName As String, Public NewFilePath As String. Is this enough to make them global for all modules, and have a value assigned in one module carry forward to all other modules? I have other variables that are, for the sake of over-do-it redundancy, redeclared and recalculated (to the same value) over and over throughout the different modules. Would it be better to do all of the declarations in 'This Workbook' instead and remove all extra declarations?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    David,

    For a variable to be available to all procedures/modules, it must be decalred in a General Module. The ThisWorkbook module is a Private object and hence would not suffice.

    Just place your Public variable decalrations in the 'General / Declarations' of a general module.

    Andrew C

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    Public variables should be declared in a standard module, not in a class module (ThisWorkbook is a class module). There is no need to declare them more than once in a workbook. You can refer to them in any module and class module, and it doesn't matter where you assign a value to a public variable.

    Check your code carefully: it is possible to declare a module-level (private) variable or a procedure-level variable (declared within a Sub or Function) with the same name as a public variable. VB will use the lowest-level variable with a given name. So if you leave old copies of variables around, you may get unexpected results.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    Thanks. Does it matter which module the variables get declared in? Should I make sure they are in what would generally be the first accessed module, or will any module do regardless of order or frequency of access? Is this affected in any way by the 'Compile on Demand' and 'Background Compile' options?

    I never paid too much attention to handling variables in an orderly manner since most of my projects used few to none, and the ones that were used were pretty much done on the fly. This project however has grown to the point where I'm using some of the same variables repeatedly, especially the ones that are used as global constants, so it's definitely time to develop some better variable-handling conventions.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    The actual module does not matter so long as it is a general module. But for maintenace and clarity it might be a good idea to store all such declarations together in the same module. You could structure your project so that it contains a Main module, which would be the obvious place for such declarations.

    Andrew

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    Just thought I'd mention Excel's Hidden Name Space.
    Names defined in Excel's Hidden Name space belong to the Application iself - this means the names can be accessed directly anywhere in Excel.
    Any VBA module or worksheet located in any open workbook can read or modify them directly, no matter which workbook created the name. Their 'lifetime' corresponds to the current Excel session.
    You can create names and assign values with one workbook, then close it, and they will still be available until Excel is closed.

    Here's an example of what you need:

    Sub setHiddenName(Name as String, Value)
    Application.ExecuteExcel4Macro _
    "SET.NAME(""" & Name & """," & Value & ")"
    End Sub

    Function GetHiddenName(Name as String)
    GetHiddenName = Application.ExcecuteExcel4Macro(Name)
    End Function

    Sub DeleteHiddenName(Name As String)
    Application.ExcecuteExcel4Macro "SET.NAME(""" & Name & """)"
    End Sub

    Examples of use in VBA..
    -other code lines
    SetHiddenName "myVariable", 27
    ...
    SetHiddenName "strCountry", "England"

    etc
    To use hidden names in worksheets:
    To create a hidden name "TEST" containing the string "ZEDDY", use the formula
    =CALL("Xlcall32","Excel4","2JRJRR#",88,,2,"TEST"," ZEDDY")

    To retrieve the contents of the name TEST use the formula
    =CALL("Xlcall32","Excel4","2JRJR#",257,,1,"TEST")

    To delete the name TEST, use the formula
    =CALL("Xlcall32","Excel4","2JRJRR#",88,,2,"TEST")

    Well it might help someday!

    zeddy




    What I like about them is they don't get trashed if you get a VBA runtime error in some code - the latter usually resets global constants and global parameters.

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    Last I checked, one could not decalare a Public variable more than once in a Project.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    Just as a side remark. I tend to have a Public variable:

    Public bVarsOK as Boolean

    Then in my main initialisation routine I do:

    Sub Initialise()
    'blah, blah
    bVarsOK=True
    End Sub

    And in the beginning of my entrypoints (subs that fire because of user interaction, like clicking a menucontrol or a toolbar button) I simply add:

    Sub Main()
    If Not bVarsOK Then Initialise
    'Blah, blah
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    That's actually a nice idea for me too. The project that I am working on has a Workbook_Open messagebox that allows the user to either run the automatic process or enter 'edit & maintenance' mode. If I use the Workbook_Open macro to initialize the variables and set the basic global values I can then use your switch to make sure that the variables get reinitiailzed after running in 'manual' mode.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    That's correct. When I wrote "There is no need to declare them more than once in a workbook" I didn't mean "declare as public", but I admit that is not clear form the way I stated it.

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Variables available entire workbook (2000 SR1)

    Ayup, I do that too, especially when I need to make sure a class has been instantiated.

Posting Permissions

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