0

Is it possible to share a single variable amongst various modules within a Excel VBA Macro? I am trying to write a code in which I read a value from a user form text box and then parse the text in a another module. The code below is a simplified version of what I want to do:

The first part is the user form module:

Public text As String
Public Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Enter As Integer)
If KeyCode = 13 Then
     text = TextBox1.Value 'Name of text box.
     Logincode
     Unload Me
End If
End Sub

While in the second module my code is as follows:

Sub Logincode()
MsgBox text
End Sub

However when I run the code my message box comes up blank. Is it possible to have data for the variable "text" shared by both modules.

Marco Soto
  • 61
  • 1
  • 11
  • 2
    Place the line `public text as string` in a module and **not** on a user form. Then it will work. For more information read here https://support.microsoft.com/en-us/help/141693/scope-of-variables-in-visual-basic-for-applications or here http://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba – Ralph Mar 13 '17 at 18:20
  • Possible duplicate of [How do I declare a global variable in VBA?](http://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba) – Ralph Mar 13 '17 at 18:21
  • @ShaiRado - as currently written, it will occur no matter what the textbox contains – YowE3K Mar 13 '17 at 18:30
  • If you want to leave the variable declaration in the UserForm, you would need to say `MsgBox UserForm1.text` in the other module (where `UserForm1` is whatever form name you are using. – YowE3K Mar 13 '17 at 18:32
  • @ShaiRado A global variable must be declared in a module. If you declare it on a user form (module) then it is **not** a public variable. Just give it a try: (1) open a new empty Excel file (2) insert a form an write on it `Public text As String` (3) create a module with `Option Explicit` and create a new sub with `MsgBox text` only in it. When you try to run it you will get `Variable not defined` because `text´ is **not** a global variable as long is it is not declared in a module (not on a sheet nor on a form). – Ralph Mar 13 '17 at 18:33
  • @ShaiRado - the OP didn't mention it, but that is what happens with the code as currently posted. A UserForm is a "class" and so "Public" variables in that class need to be treated as a "property" of the "class". – YowE3K Mar 13 '17 at 18:33

0 Answers0