Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 4 of 4

Thread: Global Variable Declaration

  1. #1
    Join Date
    25th December 2007

    Global Variable Declaration

    Hi, long time reader, first time poster.

    I have begun using Excel VBA. I have a smattering of experience within various programming languages, but am still coming to terms with the basics of VBA.
    I am trying to declare a global variable, assign it a value, then use that global variable.

    Within 'ThisWorkbook' I have the following...

    Public myText As String
    Private Sub Workbook_Open()
      myText = "Hi There"
    End Sub
    ...and in the Microsoft Worksheet Object Sheet1 (Sheet1) I have...

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      MsgBox myText
    End Sub
    Now, my understanding of this code is that upon opening the workbook, myText variable will be declared, and then assigned the value "Hi There". Then, once I have clicked anywhere on Sheet1, a message box will appear stating "Hi There". Problem is, the message box is blank.

    This is all fine, except I want the message box to state "Hi There". What am I doing wrong?
    Is the variable declared (publically) correctly?
    Am I assigning the public variable the value correctly?
    Am I referencing the public variable correctly in the Worksheet_SelectionChange procedure correctly?

    Auto Merged Post;

    *sigh* Disregard please.

    After 3 straight days of trying out innumerable different things, then finally posting here, the first thing I tried worked.

    Public myText as String
    ...must be entered in a Module, not a Microsoft Excel Object, in order for the variable to have global (public) scope.

    Last edited by Kjartan; December 25th, 2007 at 08:43. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    11th August 2006
    Outside St. Louis, MO

    Re: Global Variable Declaration

    It would have worked the way you wrote it originally had you prefaced your variable name in the "Worksheet_SelectionChange" event sub with "ThisWorkbook" like so:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        MsgBox ThisWorkbook.myText 
    End Sub
    That will save you from having to create/insert a module.

  3. #3
    Join Date
    25th December 2007

    Re: Global Variable Declaration

    Thanks Mavyak, I did not know you could access variables declared in different objects in this manner. I'll definitely be trying it out.


    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    7th December 2007

    Re: Global Variable Declaration

    Your code , declaration and everything is proper.
    Still you are seeing the Message Box as blank.
    You just need to this ,
    1) Insert a module , from insert - module , on the VBA window.
    2) And in it declare the public variable.
    3) Click on Debug - Compile VBA Project.
    Now , you will not see the message box Empty.

    You need to declare public variables inside module, to make it available globally.


    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 10
    Last Post: April 25th, 2013, 21:50
  2. Variable Declaration
    By twisternz41 in forum EXCEL HELP
    Replies: 3
    Last Post: January 7th, 2008, 18:43
  3. Variable Declaration - When to use what
    By Goppi in forum EXCEL HELP
    Replies: 5
    Last Post: November 29th, 2005, 17:02
  4. Variable Declaration
    By iwrk4dedpr in forum EXCEL HELP
    Replies: 4
    Last Post: November 29th, 2005, 16:57
  5. Public variable declaration
    By klonteig in forum EXCEL HELP
    Replies: 10
    Last Post: August 18th, 2005, 03:33


Posting Permissions

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