+ Reply to Thread
Results 1 to 5 of 5

Public Variable

Hybrid View

  1. #1
    Registered User
    Join Date
    09-29-2014
    Location
    TIjuana, Mexico
    MS-Off Ver
    MSO 2010, MSO 2013
    Posts
    53

    Public Variable

    Hi, is it possible to set the value of a variable on the Workbook_Open event? How should I do it?

    Thanks guys!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,041

    Re: Public Variable

    Declare your Public variable in a normal module before any code in that module (ideally, in its own module with any other public variables).

    Then just refer to it in the Workbook Open event handler.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-22-2013
    Location
    Philippines & Australia
    MS-Off Ver
    Excel 2016, Office 365
    Posts
    73

    Re: Public Variable

    I was interested in this thread because I have always had issues with Public variables, for that reason I actually use named cells on a worksheet to hold, have values manipulated and referred to in my modules
    Take the following code example ("Test=here") lines are break points to see the results. After running the sub Setvars I can run either of the proceeding subs and get the expected result, but only once, for example if I run the first sub FetchVariables I get the right result, if I then run the second sub TestAgain I get the result Empty ???? what am I doing wrong?

    Public Firstval: Public SecondVal
    Sub Setvars()
        Firstval = 20
        SecondVal = "Testing"
    End Sub
    
    Sub FetchVariables()
        x = Firstval
        y = SecondVal
        test = here
    End Sub
    Sub TestAgain()
        Z = Firstval * 10
        w = SecondVal & " Again"
        test = here
    End Sub

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: Public Variable

    Any state loss (caused for example by unhandled errors or resetting your project) will clear public variables.
    Last edited by romperstomper; 07-23-2015 at 03:07 AM.
    Everyone who confuses correlation and causation ends up dead.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,041

    Re: Public Variable

    @Catman50: Not sure what you mean. You're not actually changing the values in subsequent subroutines. However, if you cause a break to check the values it will reset those values.

    Option Explicit
    
    Public Firstval: Public SecondVal
    
    Sub main()
        Setvars
        FetchVariables
        TestAgain
        FetchVariables
        TestAgain
        FetchVariables
        TestAgain
        FetchVariables
        TestAgain
    End Sub
    
    Sub Setvars()
        Firstval = 20
        SecondVal = "Testing"
        Debug.Print "1 ", Firstval, SecondVal
    End Sub
    
    Sub FetchVariables()
    Dim x, y, test, here
        x = Firstval
        y = SecondVal
        test = here
        Debug.Print "2 ", x, y
        Firstval = Firstval + 10
    End Sub
    Sub TestAgain()
    Dim Z, w, test, here
        Z = Firstval * 10
        w = SecondVal & " Again"
        test = here
        Debug.Print "3 ", Z, w
    End Sub
    output:

    1              20           Testing
    2              20           Testing
    3              300          Testing Again
    2              30           Testing
    3              400          Testing Again
    2              40           Testing
    3              500          Testing Again
    2              50           Testing
    3              600          Testing Again

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to use a public variable in many modules?
    By joe55555 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2015, 05:09 AM
  2. Define a public variable
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2013, 06:48 AM
  3. Public variable losing value
    By Ned Gallagher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2012, 11:25 PM
  4. Public Variable value
    By nfpaccounting in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2011, 02:34 PM
  5. Public Variable
    By Digitborn.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2007, 03:54 PM
  6. Public variable
    By Jack in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-18-2006, 05:40 PM
  7. [SOLVED] declaring public variable value
    By Damon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2005, 10:05 AM

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