Announcement

Collapse
No announcement yet.

Global Variable Declaration

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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...

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

    Code:
    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?

    Regards,
    Kjartan
    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.

    Code:
    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.

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

  • #2
    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:

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

    Comment


    • #3
      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.

      Regards,
      Kjartan

      Comment


      • #4
        Re: Global Variable Declaration

        Hi,
        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.

        Thanks,
        Niket

        Comment

        Working...
        X