+ Reply to Thread
Results 1 to 11 of 11

declaring a variable

  1. #1
    Registered User
    Join Date
    09-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    declaring a variable

    I have a variable in my macro. and then i have a userform that uses the same variable. i want the item picked in the userform to be added to the variable and called out again from the macro....but it's not working as intended. the item picked from the userform does not show up when i recall the variable from the macro. i tried declaring it as public in the decleration section and i also tried declaring it as global. but neither one of these methods works.

    here is my code from the macro

    Please Login or Register  to view this content.

    and here is my code in the userform

    Please Login or Register  to view this content.

    is there a way to get this variable to add items from the userform and show it in the macro when i call the variable back?
    Last edited by squeekee35; 10-06-2011 at 03:37 PM. Reason: solved

  2. #2
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: declaring a variable

    This link will clarify it for you.
    http://www.ozgrid.com/VBA/variable-scope-lifetime.htm

    Where is this variable declared, in what module?

  3. #3
    Registered User
    Join Date
    09-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: declaring a variable

    Basically from this link you gave me i am getting that it is impossible to do what i want because the variable resets to empty when you use the end statement....so when i end my userform the variable ends as well.

    am i reading this correctly?

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: declaring a variable

    Yes. So you would need to declare the variable in your ThisWorkbook module, or in a general module. That way you can access the variable as long as the workbook is open.

    This link may also be informative to you:
    http://www.mrexcel.com/forum/showthread.php?t=50019
    Last edited by Whizbang; 10-06-2011 at 01:39 PM.

  5. #5
    Registered User
    Join Date
    09-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: declaring a variable

    Declaring it in this workbook didn't work either. i listed it as public in the workbook decleration.....

  6. #6
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: declaring a variable

    I guess I was wrong about the ThisWorkbook module, but it does work if you declare it in a general module. See the attached.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: declaring a variable

    That is nice but i am unable to see the code attached with it so i am not sure how to proceed. but i will figure something out...thanx

  8. #8
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: declaring a variable

    Why are you unable to see the code? The project is not protected. I just downloaded the attached workbook and was able to see the code fine. Did you click the "Enable Editing" warning? The project will not be viewable until you do.

  9. #9
    Registered User
    Join Date
    09-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: declaring a variable

    yes i clicked enable. i don't know why but when i go to view macros it doesn't show any macros in there. it's no big deal. thanx anyway.

  10. #10
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: declaring a variable

    Because there aren't any "macros". Go to the VBE by hitting ALT+F11.

    The "macros" you are referring to are procedures, specifically subroutines, that are in general modules (ie, not in a worksheet module or in a userform). This workbook does not contain any independant subs. The code is all in the Sheet1 module, and the UserForm1 module. The only code in the Module1 is the line that declares the variable.

    See this link for a description of the different module types.

    http://www.cpearson.com/excel/codemods.htm

  11. #11
    Registered User
    Join Date
    09-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: declaring a variable

    oh------hehe i found it. thanx.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1