Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: VBA - Public variable not persisting

  1. #1
    Join Date
    9th March 2011
    Posts
    5

    VBA - Public variable not persisting

    Hi there,

    I'm going absolutely nuts trying to figure out what the problem is with the following. I've declared a public string variable called "password". A form is used to populate this variable. Using a watch I see that the variable persists even after the SetPassword sub finishes. However, on starting the sub Test, the variable is reinitialized. Does anyone know why this is so?

    Thanks for any help!

    Code:
    Public password As String
    Sub GetPassword()
        If password = "" Then frmPassword.Show
    End Sub
    
    'The UserForm calls setPassword and assigns the value from a textbox to the variable
    Sub setPassword(myTextBoxValue As String) 
        password = myTextBoxValue
    End Sub
    
    Sub Test()
        MsgBox password
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    2,136

    Re: VBA - Public variable not persisting

    What is the full code of the userform?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th March 2011
    Posts
    5

    Re: VBA - Public variable not persisting

    Here it is...
    Code:
    Private Sub CommandButton1_Click()
        If txtPassword.Value <> "" Then
            Call Telnet.setPassword(txtPassword.Value)
            Unload Me
        End If
    End Sub
    The form consists of a textbox called txtPassword and a button called CommandButton1

    Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    2,136

    Re: VBA - Public variable not persisting

    Have you compiled and saved the workbook?

    I would suggest you change the GetPassword sub to:
    Code:
    Sub GetPassword()
       Dim frm As frmPassword
        If password = "" Then
          Set frm = New frmPassword
          frm.Show
          Set frm = Nothing
       End If
    End Sub
    As a general rule you should avoid using the default instancing of userforms - they are classes and should be treated as such.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    9th March 2011
    Posts
    5

    Re: VBA - Public variable not persisting

    Hi Rory,

    I shut and opened the workbook and now I have no problems. Grrrr... I can't believe I wasted so much time on this and only had to do that. Thanks for all the help and the tip on calling an instance of the UserForm.

    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. Dim Public Variable
    By timbo180 in forum EXCEL HELP
    Replies: 1
    Last Post: February 20th, 2008, 01:58
  2. Set Public Variable
    By Obelix in forum EXCEL HELP
    Replies: 2
    Last Post: February 12th, 2008, 08:00
  3. Public Variable
    By minitman in forum EXCEL HELP
    Replies: 2
    Last Post: December 22nd, 2005, 05:58
  4. Public variable
    By Neiluk66 in forum EXCEL HELP
    Replies: 1
    Last Post: May 26th, 2005, 19:58
  5. Public variable
    By robert_a_hudson in forum EXCEL HELP
    Replies: 2
    Last Post: August 19th, 2004, 00:31

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