Looking for some direction: Use of Spreadsheet (worksheets) vs VBA functionality

[DonEB]

I have a Excel spreadsheet that consists of 6 worksheets. I include the following simply to provide a little background in preparation of asking a couple questions.

The worksheets are as follows:

1. Tennis Roster - max 20 players
2. Schedule - For Tennis Doubles (i.e., 4 people needed to make up one match) where 13 weeks and max 4 courts are computed
3. Allweeks - where player priorities are set based on many different variables (i.e., % of play, # of times played other players, days unavailable, days not scheduled to play previous week, etc.)
4. TeamSelection1 - For court # 1, determines all combinations of matches based on available players (max 1820 if 16 players available, 4 at a time)
5. TeamSelection2 - For court # 2,
   determines all combinations of matches based on available players (max 495 if 12 players available, 4 at a time)​
   <strike></strike>
6. TeamSelection3
   - For court # 3, determines all combinations of matches based on available players (max 70 if 8 players available, 4 at a time). Also, by default, identifies that final 4 players available to play on court # 4​

​

Here's my problem:

1. For computing one simple day consisting of 3 courts and 12 players, the programs takes over 24 seconds to populate the schedule.
   • In this example, the program will loop thru all potential combinations as follows:
     • Group 1 - top 10 combinations
     • Group 2 - top 5 combinations (i.e., based on four players being removed to play in first group
     • Group 3 - top 1 combinations (i.e., based on four players being removed to play in first group​
       <strike></strike>
2. For this program to compute all 13 weeks, it takes over 5 minutes to run. This is based on going through the list of potential players TWICE to ensure the best potential assignment of players to teams based on the criteria list above.

My questions are:

1. Throughout the entire process, there is a lot of sorting, copying/pasting, placing values into arrays, etc. At no point, do I clear memory. Might this have an impact on the efficiency of the program and, if so, how do I go about clearing the memory?
2. I also have a lot going on within the ALLWEEKS worksheet and the various TeamSelection worksheets (i.e., copying/pasting, sorting, lookups, etc.). ​
   • ​Is it possible to perform all these functions while using an ARRAY thus improving efficiency? If the answer to this is YES then I will have a lot more research to do on this subject matter.​
3. ​I know you don't know the details behind any of the programming I have but if anyone has any other efficiency considerations that I should investigate that it would be appreciated. ​

​

Thanks for your thoughts on this matter.

 

[Fazza]

hello

Can't really begin without details. Guess that clearing memory won't make much difference.
Great info on speed & efficiency at http://www.decisionmodels.com/index.htm

cheers

 

[pvr928]

Hi DonEB

Excel works least efficiently when it is either reading from a worksheet into memory, or the other way around.

If all you are doing is taking values from a worksheet, processing them (a combination of sorting, looking up values) and then writing the results back to the worksheet, and you don't have a lot of data, this should only take seconds.

Consequently, you are on the right track to contemplate using arrays (or collections). I also recommend looking into whether the dictionary object might be applicable (it is a very efficient and fast way of creating a set of unique values and looking up values). However, all of this will not speed up your macro if you are constantly referring back to the worksheet.

In other words, in principle to create a superfast macro: read from the worksheet once, process in memory, write the results back to the worksheet once.

As Fazza says, however, more details are needed in order to provide a more complete answer.

Cheers

pvr928

 

[Fazza]

Depending on the tasks, there may be other fast approaches.

For the nominated sorting, copying/pasting, placing values tasks Excel is efficient.
Formulas are sometimes not - especially array formulas.
Queries (such as defined by SQL) can be efficient for sorting, copying/pasting, etc operations and use no formulas.

As well as having an efficient high-level approach to the general methods used, there are elements of the low-level details within VBA that can make a big difference too. Not just the obvious control of screenupdating, calculation, events, etc.

As earlier, can't really begin to comment without knowing details.

regards, Fazza