Need help speeding this up/general advice

**ItPaysTheBills** · 01-01-2013, 02:44 PM

Hi all. First post here.

I use Excel every day at work and felt like learning VBA was a good step for me. I've written a program that I'd like you guys to critique as to its efficiency / any other points that I could learn from.
My number one problem is that it goes to slow. I've programmed in assembly with microcontrollers that operate at 4Mhz or so and I feel like with all this computing power the program takes WAY to long to run when it is dealing with large ranges.

What it's for:
I have to reconcile some large bank accounts every month, where millions of dollars move through in hundreds of different transactions. I needed something to match up values that appear in our ledger and values that appear on our bank statement, then identify values that don't match.
How the program goes about it:
It prompts me to select each range of values, then looks at each value in range A (copyRange), compares it to values in range B (findRange) and then colors values using the color in cell A1 for each time it is found in the opposite range, and vice versa.
It then uses the "ColorSum" UDF that I found on the internet to sum each cell in each range that has been highlighted, and then show the user that the values match with a message box. The user can select for the program to generate a report, which then selects a new sheet and creates an array of values from each range that has not been highlighted, and places them on the new sheet.

I know this code isn't efficient. If I have a small number of values, say, 400 in each range, it takes about 4 seconds to run. This isn't all that unreasonable, but as a matter of learning I'd like to find out how to make it faster. If I were to use this program on, say, 20,000 values in each range, it will take several minutes to run. Thanks!

Please Login or Register  to view this content.

**Richard Buttrey** · 01-01-2013, 03:29 PM

Hi,

Presenting your request in this way without also uploading your workbook makes it very difficult to understand how your code relates to your data, which is sort of a precursor to understanding these things.

Start from basics and upload your workbook. Describe your overall process requirement in narrative terms, referencing specific cells as necessary and manually add your expected results so that we can see your end goal.

Some specific points about your code.
1. Intuitively I don't like having to read cell colouring values in VBA macros, it just seems too risky to me. Try to read specific cell values.
2. Avoid GoTo line instructions. It's a bad habit, leads to 'spaghetti' code and makes reading a macro very difficult. Break your procedure down by creating other procedures which handle small specific sections of code and call these procedures instead of a GoTo.
3. Indent code blocks like For..Next, If.. End If, With..End With etc. to make code readable and leave white space to make it easier on the eye.
4. If you want to populate VBA arrays, rather than using a loop to populate cell by cell, consider the option of using a Data Advanced Filter in the Excel App to create a table of desired values and then read the whole table into VBA. There is a time overhead every time you use a loop which looks back to Excel to read a value, and then has to return to VBA.

**ItPaysTheBills** · 01-01-2013, 03:55 PM

I have attached the file (I think).

Sheet1 has already had the matching performed on it, and Sheet6 is a report that shows the values that were not matched.

In order to run it again, clear out the coloring on the cells in column A and column B. Select some color for cell A1 before you run it again.

This is just a test sheet to prove the concept.

I'll edit my original post to add more information in the comments to try and convey what I'm doing.

Responses to your points:
1: I don't understand what you mean. I need them to be highlighted for the purposes of reviewing the sheet and seeing what values didn't get matched up/what did. How is this risky? I'm not being sarcastic, I just don't understand.
2: I can see that point. I'll look into how to go about that.
3: Point taken. Thank you.
4: So you are saying I should perform the matching up procedure, and then once that's finished do something like filtering by color and then pulling out the cells that aren't colored?

**ItPaysTheBills** · 01-01-2013, 04:57 PM

Please Login or Register  to view this content.

**Pete_UK** · 01-01-2013, 06:27 PM

I've not studied your macro in detail, but your description reminds me of a post I replied to many years ago on the newsgroups. If I remember correctly, the poster had two lists, one of positive numbers and the other of negative numbers (receipts vs payments), and wanted to match on the magnitude of the number. I suggested an algorithm which combined both lists (together with an initial row index, so that they could be put back into the original order afterwards) and then sorted this list in descending order. Then you could look at the top-most and bottom-most values - if they were the same then you have a match (and as well as marking them as such you could decrement the TOP pointer and increment the BOTTOM pointer), but if they were not the same you adjust either TOP or BOTTOM depending on which has the larger magnitude, and then carry on until TOP=BOTTOM. Then the two lists could be separated out and returned to the original sequence.

This was a very efficient way of doing it - I remember it taking a couple of seconds on 2000+ values (in Excel 2003), compared with several minutes using a loop within another loop which is what you seem to be doing.

I'll see if I can find the file that I worked on.

Hope this helps.

Pete

**Richard Buttrey** · 01-01-2013, 08:20 PM

Hi,

I'm attaching a workbook which uses standard Excel functions and data filtering without macros, although you could just as easily code the functions into a macro and copy them down the two helper columns I'm showing.

If you filter all four columns, using either column C or D filtered for all values except N/A then you'll get the results you're looking for without resorting to any colouring macro.

I think using cell background colours as a test in macro is dangerous because users can too easily slightly change a colour and compromise the macro. I've had this happen to clients too many times.

**ItPaysTheBills** · 01-02-2013, 12:19 AM

Originally Posted by Richard Buttrey

Hi,

I'm attaching a workbook which uses standard Excel functions and data filtering without macros, although you could just as easily code the functions into a macro and copy them down the two helper columns I'm showing.

If you filter all four columns, using either column C or D filtered for all values except N/A then you'll get the results you're looking for without resorting to any colouring macro.

I think using cell background colours as a test in macro is dangerous because users can too easily slightly change a colour and compromise the macro. I've had this happen to clients too many times.

Very nice Richard. I may have been trying to kill a fly with a bazooka here. I'll probably end up coding that in. I'm not sure why I didn't think of that.
Just curious, do you do this type of thing professionally?

Pete:
Sounds innovative, but I'm not sure I follow it. If you come across that sheet, I'd be interested in seeing it.

**Richard Buttrey** · 01-02-2013, 06:40 AM

Hi,

Sledgehammer to crack a nut is the colloquilism used here, but I like your fly/bazooka metaphor. And I've been there myself many times in the early days and received several T shirts.

As a general principle it's always better to use standard Excel functionality wherever possible since this will always be quicker and more efficient than any VBA solution. And as an afterthought if you really want to colour the cells then a logical extension would be to use conditional formatting in columns A&B which refer to the formulae results in C&D

Yes I do this professionally and have done for a few years now after taking early retirement from the corporate world.

**Pete_UK** · 01-02-2013, 08:11 AM

Originally Posted by ItPaysTheBills

Pete:
Sounds innovative, but I'm not sure I follow it. If you come across that sheet, I'd be interested in seeing it.

I've attached the file, and here is the macro:

Please Login or Register  to view this content.

The data is in column A, starting with A1. After inserting a few columns, a simple sequence is written down column B, and then the data is sorted. The pointers are then adjusted in the loop, starting at the two ends of the list. This macro allocates 5 different colours based on the amounts in bands - a key in columns E to G shows other colour numbers that you could use (though these are for XL2003, so the colour numbers might have changed). At the end of the loop the data is sorted back into its original order, then the extra columns which were inserted get deleted - in addition to the colour banding, column B indicates matches with a Y, so you can easily filter this column to find matched or unmatched values.

You could apply the same principle to your two columns of data - insert extra columns to record the initial order, then sort each column, then the pointers will apply to the left and right columns, then sort back afterwards.

You might also be interested in the original thread, which can be found here:

http://groups.google.com/group/micro...enning+dilemma

It went on for 50 or 60 posts, with several variations.

Hope this helps.

Pete