Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Variable help (Excel 2003)

    Hi all,
    I have a variable that I am using in one procedure and would like to carry that variable to another procedure within the same module, the variable returns a 0 value in the second procedure. Is there something that I am forgetting, or maybe a public declaration that I need to make so that the variable holds its value?
    Thanks,
    Darryl.

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

    Re: Variable help (Excel 2003)

    You can declare a variable at the top of the module, below the line Option Explicit (if you have one, and you should), but before all functions and subs. If you declare it as Private, for example

    Private strThis As String

    you can use it in all functions and subs in that module, but not in other modules. If you declare it as Public, for example

    Public lngThat As Long

    you can use it in all functions and subs in all modules in the workbook. In both cases, you must set the value of the variable in a function or sub. Thereafter, the value will be known to the other functions or subs.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Variable help (Excel 2003)

    DIM the variable in the "declaration part" of the pane (before the first SUB). it will then be available to all procedures in the module.

    Note: if you use "Option Explicit" at the start, it would have indicated that in the secondi procedure, the variable was not declared, indicating it is not carried over.

    Another way to do it is to pass the variable using a parameter (like functions do), then it does not have to be declared outside the procedures.

    Steve

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Variable help (Excel 2003)

    If you want it to be used by two procedures in the same module, then it should be defined at the top, just after Option Explicit and not in any procedure that uses it. If it is only used by procedures in the module then define it as Private (use the word Private where you would use dim). Private means that its scope is limited to the current module. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Variable help (Excel 2003)

    Rats! You guys are just too fast! <img src=/S/anigrin.gif border=0 alt=anigrin width=19 height=19>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Variable help (Excel 2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I copied most of my reply from <post:=551,383>post 551,383</post:> <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  7. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Variable help (Excel 2003)

    Thank you all......

Posting Permissions

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