+ Reply to Thread
Results 1 to 3 of 3

More efficient way to handle a large number of variables

  1. #1
    Forum Contributor
    Join Date
    08-03-2009
    Location
    Cary, NC
    MS-Off Ver
    Excel 2016
    Posts
    109

    More efficient way to handle a large number of variables

    It's been about 7 years since I've used this forum so please forgive any lapse in decorum.

    I have a worksheet with 50+ variables. In each cell of one of the columns, I have a drop-down list with 30+ selections to choose from. Each of those selections has a macro attached to it. Each macro, when run, can change from 1 to 10 of the variables in the worksheet. Each macro will change its assigned variables, then copy a selection from the row above it, paste it into the current row replacing any changed variables with the new variable values.

    This is my question: each macro I build has to have all 50+ variables defined in the individual macro. Given that I have to build 30+ macros this seems like a lot of overhead. Is there a way to make the 50+ variables global so that each individual macro only has to define the variables it will be changing?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    42,912

    Re: More efficient way to handle a large number of variables

    You could create a separate module and define all the variables there OUTSIDE of any subroutines and functions. That would make them global/public.

    You might need to reset them as required for each of your subroutines otherwise you might get unexpected results (carried forward from the previous subroutine(s)).

    Maybe consider defining an array of variables so you can loop through them?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    08-03-2009
    Location
    Cary, NC
    MS-Off Ver
    Excel 2016
    Posts
    109

    Re: More efficient way to handle a large number of variables

    Thank you for the suggestions, Trevor. I have a more pointed follow-up question.

    This how I envision this working: Say a macro runs and changes the values of 5 variables. Then the macro copies the row above and pastes in the current row. Then the macro goes through the current row and replaces the old values of the changed variables with the new values. Now given this order of operations, your array idea seems like the most feasible solution.

    I can probably figure out how to make an array of variables and create a for/if loop to replace the new variables but I'm having trouble seeing how this fits in a VBA project. Is the variable array in one module and the macro in a separate module? If so, how do I read the array from the macro module?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Data Entry - 4 variables how to handle neatly
    By sargan in forum Excel General
    Replies: 23
    Last Post: 04-14-2015, 06:31 PM
  2. Replies: 45
    Last Post: 12-19-2013, 10:17 AM
  3. Efficient way to animate changing colors for a large number of shapes?
    By nannerdw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2011, 08:08 AM
  4. Procedure too large, can be more efficient?
    By scubadiver007 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-15-2011, 08:35 AM
  5. Best way to set a large number of variables?
    By starcrwzr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-27-2009, 06:58 PM
  6. [SOLVED] Why can't Excel handle very large numbers 500 digits- kid RSA ridd
    By Robert Bigdowski in forum Excel General
    Replies: 0
    Last Post: 07-17-2006, 04:05 AM
  7. [SOLVED] Passing large number of variables to function
    By RB Smissaert in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-31-2005, 07:05 PM

Tags for this Thread

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