+ Reply to Thread
Results 1 to 14 of 14

Define Variable within a module

  1. #1
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Ventura, united States
    MS-Off Ver
    Excel 2010
    Posts
    346

    Define Variable within a module

    How can you define a variable that will be recognized in every sub within a module?

  2. #2
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Define Variable within a module

    define it at the top of the module, before any sub

  3. #3
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Define Variable within a module

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Define Variable within a module

    Declare the variable with Public at the top of the module, after Option Explicit (if you use that) and before any Subs or Functions.
    Example:
    Please Login or Register  to view this content.
    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Ventura, united States
    MS-Off Ver
    Excel 2010
    Posts
    346

    Re: Define Variable within a module

    Hmmm. I'm still not getting it. Here is the sub that defines my variable. The other subs in the same module didn't recognize the variable.

    Please Login or Register  to view this content.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Define Variable within a module

    Remove the declaration in the sub.

    See Help for Understanding Scope and Visibility
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Ventura, united States
    MS-Off Ver
    Excel 2010
    Posts
    346

    Re: Define Variable within a module

    OK. What do you mean by declaration? You mean the DIM lines? And what do you mean by help? Help where?

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Define Variable within a module

    What do you mean by declaration? You mean the DIM lines?
    Yes
    Help where?
    Help in the Visual Basic Editor

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Define Variable within a module

    Hi,

    As shg has suggested you should understand the separate and distinct concepts of Scope & Lifetime (or visibility)

    You have declared
    1. A Public variable called myVariable. This is available across all procedures in all modules in the project.
    2. A Module level variable called RNG. This is available in all procedures in the Module in which it resides.
    3. A Procedure level variable called RNG. This is only available in the Define_Rng procedure

    Which procedures in which Module are not recognising which variable?

    Incidentally it's generally considered good practice to preface your variable name with an indicator as to the type of variable it is. We all have our own favourites, but generally something like

    Dim stMonthName As String ' using st to indicate a string variable
    Dim rMyRange As Range ' using r to indicate a range variable
    Dim lLastRow As Long ' using l to indicate a Long variable
    Dim bMyFlag As Boolean ' using b to indicate a True/False boolean variable.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  10. #10
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Ventura, united States
    MS-Off Ver
    Excel 2010
    Posts
    346

    Re: Define Variable within a module

    Why do I still not get this. The Excel help menu states that the default for a variable is Private. Can be used within the module. I even copied the examples they give into my module and the variables are only local to the sub.

    Please Login or Register  to view this content.

  11. #11
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Define Variable within a module

    if you declare a variable within a procedure it is only visible to that procedure
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Define Variable within a module

    A public variable is declared at the top of a standard code module, above all procedures (just below Option Explicit), using the key word Public instead of Dim. It is visible to all procedures in the VBA project unless (1) a module-level variable of the same name is declared in another module, or (2) a procedure declares a variable of the same name. Once initialized, its value(s) persist until the VBE is reset manually or via an End statement.

    A module-level variable is also declared at the top of a standard code module, also above all procedures (just below Option Explicit), using the key word Private or Dim. It is visible to all procedures in the module unless they declare a variable of the same name. Once initialized, its value(s) persist until the VBE is reset manually or via an End statement.

    A local variable is declared in the body of a procedure, always using the key word Dim. It can be declared anywhere in the module prior to its first reference. It is visible only within the procedure, and is destroyed when the procedure exits.

    This is all pretty well explained in Help.
    Last edited by shg; 03-02-2013 at 05:55 PM.

  13. #13
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Ventura, united States
    MS-Off Ver
    Excel 2010
    Posts
    346

    Re: Define Variable within a module

    For some reason the excel help explanations were confusing to me. Your explanation is completely clear and I thank you shg and JosephP. And to everyone early when I was being dense.

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Define Variable within a module

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1