Excel Hints

Results 1 to 12 of 12

Thread: Solved: variables carrying over from userform to Module

  1. #1

    Solved: variables carrying over from userform to Module

    All - I am using Excel 2007 on a PC and am trying userforms to enter data into a spreadsheet.

    I have 4 variables that are specified in a Userform. I am able to write code to do what I want as long as the code is inside the Userform I created.

    What I would like to do is to execute a specific Macro placed in a Module so the working parts of this effort would be under Module rather than with the Userform. For the code I want, I can make it more efficient and cleaner by placing it into a Module.

    I would have code similar to this below in the Userform to run the Macro I want:

    [VBA]
    Application.Run "Filename.xlsm'!ModuleName.MacroName"
    [/VBA]

    When I write the code in MacroName, I use info from the user that was completed in the UserForm. The variable names below are the names of the Listbox or Textboxes which are used for the variable names of the data:
    shtname which is a worksheet name
    BName which came from a Listbox
    Calendar1 which came from a Calender on the Userform
    Amount which came from a TextBox
    Comments which came from a TextBox

    I can code this to work correctly as long as it is inside the Userform code but I cannot use these variables inside the Macro. It appears as if the variables are not recognized.

    I did not close the Userform but did Unload the userform to remove it from view of the user.

    Do I have to Dim the variables in the Macro ?

    Any help or suggestions would be appreciated!

    thanks!

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,321
    Location
    I can code this to work correctly as long as it is inside the Userform code but I cannot use these variables inside the Macro. It appears as if the variables are not recognized.
    You may want to rethink doing what you are suggesting. I'm not sure why you think it will help to begin with.

    To do what you want, you will have to declare all of your variables publicly, in a standard module and you will have to reference every userform, control, etc. specifically.

    Just my two cents.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    thanks for the advice lucas. I am continuing with the project you helped with earlier. The help you provided earlier is really helping.

    I thought it may be easier to keep the main part of the code in a Module but it is looking like it is not that easy to do that.

    I am still learning all the ins and outs here so advice like this is helpful.

    To declare the variables publicly specifying the userforms and controls is that done through the Dim statement or some other method ?

    A sample of that might be informative here if one is easily available ?

    Thanks!

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,321
    Location
    On public variables from the help file:
    Public Statement Example
    This example uses the Public statement at the module level (General section) of a standard module to explicitly declare variables as public; that is, they are available to all procedures in all modules in all applications unless Option Private Module is in effect.
    [VBA]Public Number As Integer ' Public Integer variable.
    Public NameArray(1 To 5) As String ' Public array variable.
    ' Multiple declarations, two Variants and one Integer, all Public.
    Public MyVar, YourVar, ThisVar As Integer[/VBA]

    to reference a textbox from a standard module you would have to fully qualify it. For example:
    [VBA]Sub a()
    UserForm1.Show
    UserForm1.TextBox1.Text = "Test"
    End Sub
    [/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    12,503
    Location
    Something like this
    Userform code
    [VBA]
    Option Explicit
    Private Sub CommandButton1_Click()
    Number = TextBox1
    Call Test(Number)
    End Sub
    [/VBA]

    Standard Module Code
    [VBA]
    Option Explicit
    Public Number As Integer

    Sub Test(Num As Integer)
    MsgBox 3 * Num
    End Sub
    [/VBA]
    MVP (Excel 2008-2010)

    "Provide sample data and layout if you want a quicker solution." - MD


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    thanks lucas and mdmackillop! I am learning much from both of you and I appreciate it! :-)

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    12,503
    Location
    No problem. If you need help with the specifics, let know.
    MVP (Excel 2008-2010)

    "Provide sample data and layout if you want a quicker solution." - MD


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    VBAX Expert Tinbendr's Avatar
    Joined
    Jun 2005
    Location
    North Central Mississippi (The Pines)
    Posts
    991
    Location
    Quote Originally Posted by bdsii
    I did not close the Userform but did Unload the userform to remove it from view of the user.
    Don't unload it, just Hide it.

    For example:
    [vba]Private CommandButton1_click

    userform1.Hide
    Application.Run "Filename.xlsm'!ModuleName.MacroName"
    Unload Me
    End Sub[/vba]Then you have access to the contents of the userform.

    David


  9. #9
    Thanks Tinbendr - will give that a whirl. :-)

  10. #10
    Hey,
    I'm kinda new to VBA and was reading these forums to get help with some of my code.

    I'm running a code containing 3 standard modules. Till now they were independant, in the sense that no variables needed to be passes from one module to another. Now I have a requirement to pass a few variables between modules.

    Declaring variables as public in a standard module allows it to be accessed only in that module and doing the same in the ThisWorkbook module doesn't allow access to it from any standard module .

    I was initially trying to use the "friend" variable declaration but was completely lost.

    Any help will be greatly appreciated.

  11. #11
    hey i m also looking for same code
    as i hve to enter data in excel
    for that i hv made userform and entry will go through userfrom to excel sheet

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,092
    Location
    Quote Originally Posted by paragrc
    hey i m also looking for same code
    as i hve to enter data in excel
    for that i hv made userform and entry will go through userfrom to excel sheet
    Start your own thread, do not hijack others.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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