+ Reply to Thread
Results 1 to 5 of 5

Method to define a variable across multiple Modules?

  1. #1
    Registered User
    Join Date
    02-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Method to define a variable across multiple Modules?

    I'm working on a project that converts a large dump of raw data(10k rows) into usable information and eliminates about 50% of inputs. I am a novice when it comes to VBA, but I want my scripts to be flexible if I move around the columns or add more. I'm currently using the following to find which range/column should be adjusted, but I feel like it's redundant.

    Please Login or Register  to view this content.
    I have over 25 Subs that each do a different function such as correcting "Jr" to "Jr.", "iii" to "III", etc. Right now, I have basically the same first 11 lines in every single one of them. I then use one master macro that calls all of these to run in the correct order.

    My question is, is there a way that I can make "r" be a global value that will move across the different subs, or do I need to Find then Set "r" in each and every one?

    Would it be better if I created another function to do the finding and just called that?

    If this is a stupid question, I'd appreciate if you could point me in the direction of a good resource to learn from. Most of my learning has been through trial and error thus far.

    Thanks
    Last edited by texas32; 02-23-2013 at 03:24 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,837

    Re: Method to define a variable across multiple Modules?

    1. Declare the variable as PUBLIC at the module level (i.e. at the top of the module before any subs)
    Please Login or Register  to view this content.
    2. Pass the variable to each macro as a parameter:
    Please Login or Register  to view this content.
    Last edited by protonLeah; 02-23-2013 at 03:33 PM.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Method to define a variable across multiple Modules?

    If you declare r at the top of the code module above all the procedures, it will be global to all the procedures in that module.
    Dim r As Range

    If you do that and use the Public keyword, it would be global to all the procedures in any module within the workbook
    Public r As Range

    Scope Of Variables And Procedures
    Last edited by AlphaFrog; 02-23-2013 at 03:33 PM.

  4. #4
    Registered User
    Join Date
    02-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Method to define a variable across multiple Modules?

    Thank you! That was exactly what I was looking for.

    EDIT: Too hasty of a response, can I do something like this:

    Please Login or Register  to view this content.
    Or do I still have to do that in each Sub?
    Last edited by texas32; 02-23-2013 at 04:01 PM.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,837

    Re: Method to define a variable across multiple Modules?

    Please Login or Register  to view this content.
    Ben Van Johnson

+ 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