Perplexing Macro Issue

**Digger442** · 09-16-2007, 10:43 PM

Calling all experts!!

I am working on a project that needs to do some heavy data manipulation to approx 500,000 records per month. I have created a number of macros that are manually run by a user (a single user whom I have trained) on each worksheet within a workbook. The essential functions for each of the 5 macros are:

1. Find and Replace macro that searches for a corrects incorrect or common mispellings.
2. Location macro that separates out the two-letter State abbreviation from a column and creates a new column for it. This macro also 'Paste Special - Values' into a new column and then deletes the column with the Trim formula.
3. Split Cell macro that trims out specific data contained in one column and divides it into 3 columns.
4. Paste Values macro that does the 'Paste Special - Values' function to the above newly created columns and deletes the columns with the Trim formulas.
5. New Sheets macro that cleans up unused rows and hidden columns.

Note: The reason for the 'Paste Special - Values' functions are that this data is imported into an Access Database following manipulation in Excel.

Now the problem.....I have created these macros on my machine in my home office and copied them onto the target machine at the business where it is to be used. Both machines are relatively new running XP and Office 2003. The target machine is on a local, hardwired network.

While I had unqualified, initial success on the target machine for one monthly cycle....now the macros run unacceptably slow on the target machine. I am able to run all five macros through 7 worksheets on my machine in about 30 minutes...perfectly acceptable considering the amount of data! While I have not done a complete run-through on the target machine....it appears that it would take many hours for one worksheet.

I have:

Turned off network connections (and even unplugged the LAN cable) on the target machine - no help
Made sure that I have a self-certified digital certificate on the target machine for myself and the user
Re-created the macros on the target machine by cutting and pasting the code into new macros

The perplexing issue for me is why is works so flawlessly on my machine, but is slower than a glacier on the target machine.

I have seen the "Screen Updating/Application Calculation" sub that I may try....but I am not overly hopeful!

Any thoughts/advice would be greatly appreciated!!

Thanks!!

**Digger442** · 09-16-2007, 10:52 PM

A few more thoughts:

The macros are stored in a Personal.xls file that resides in the XLSTART folder in documents&settings/[user]/applications/microsoft/Excel

I would be happy to post the macros, but was reluctant to do so as the post may seem overly complex and scare off some good thoughts!

Thanks!

**FrankBoston** · 09-16-2007, 11:35 PM

Turning off Screen Updating and automatic Recalculation while your macros are running can speed things up greatly. Consider that to display things correctly, the spreadsheet may be recalculating.

For such a long data process, you may want to turn on Screen Updating to cause a re-display every 30 seconds or so, to show that things are still processing.

**rylo** · 09-17-2007, 12:10 AM

Hi

1) Presumably the machines are memory size compatible, and virtual memory and other "Physical" type issues have similar characteristics.
2) Are there any other macros in the user's personal.xls that may be impacting? Event macros???
3) Check the priority of Excel on both machines. Open up the task manager, select processes, right click on Excel, set priority and see what both machines show.
4) Is the delay in the processing, or in the opening / closing of the source data files?

Maybe a look at the code would offer some suggestions on speed enhancements, so perhaps create a small workbook, with some of the data, and the code and paste it for review.

HTH

rylo

**avveerkar** · 09-17-2007, 04:56 AM

" Page Break " view can play havoc. When Page Break view is on, the print driver re-evaluates the view at the every line of code where print view changes ( such as deleting a row, hiding rows and columns ). Just swith off the Page Break View ( Tools > Options > View > Page Break ticked off ). Page Break View will make print driver re-evaluate the view even if screen update is off.

A V Veerkar

**Digger442** · 09-17-2007, 10:06 PM

Thanks for the replies and the advice!

I’ll embed some of the macro coding below for your review. I’ll definitely add this screenupdating/calculation code to each of my macros:

Please Login or Register  to view this content.

I’ll have to check the hardware properties of the target machine next time I’m out there. I know that there are no other macros/events running in Excel, though checking the priority is an excellent idea….thanks. The delay does occur during processing. I’m pretty sure that page break view is off, but I’ll check that too.

Find/Replace Macro (I’ve pasted only one piece of the code as there are hundreds of similar entries):

Please Login or Register  to view this content.

Location Macro:

Please Login or Register  to view this content.

Split Cells Macro:

Please Login or Register  to view this content.

Paste Values Macro:

Please Login or Register  to view this content.

New Sheets Macro:

Please Login or Register  to view this content.

Whew! If you’ve read through all that….Thanks!!!! Any thoughts would be appreciated.

**rylo** · 09-18-2007, 12:20 AM

Hi

A couple of thoughts.

1) You don't need to select ranges to action them.
Your findreplace code can then be changed to

Please Login or Register  to view this content.

Unfortunately when used this way, the _ separation won't work.

2) In a similar vein, the separateState can be changed to

Please Login or Register  to view this content.

It only operates on the filled range and doesn't put formulas in that have to be recalculated, so this should cut down on some processing time.

3) With the splitcells macro, do you really need the output in columns E:H, or could they be in A:D? No real drama, but this can be done without formulas. You also have a lot of formulas being created (for every row) and this could be cut down to only action cells in column A that have data. As I read it you have space separated text in column A. If A:D can be used for output then something like

Please Login or Register  to view this content.

Will look further as time permits.

rylo

**avveerkar** · 09-18-2007, 07:43 AM

Digger442,

All the suggestions that are coming will certainly improve the performance of the macro, no about it. But the primary issue is why so much of difference between the two machines which are basically of same config. Do share the when you eventually find out.

A V Veerkar

**Digger442** · 09-18-2007, 09:48 AM

Thanks for the feedback.

Rylo - I'll have to examine your suggestions a bit later and will let you know....suggestions look great at first look though!

A V - Bingo! That is the most puzzling thing for me too. I'll certainly let you know after I get a chance to definitively compare machines, check priorities, review processes, etc

Ðigger

**rylo** · 09-18-2007, 08:40 PM

Hi

Bit more. The Paste Values macro can be reduced to

Please Login or Register  to view this content.

rylo

**Digger442** · 09-19-2007, 08:25 PM

Good evening folks,

It appears I resolved the issue today....I won't be satisfied for another couple of monthly cycles though!

It seems that one, or more, of three different programs were causing problems. In a previous job within a very large University network, I recall that the Google Toolbar was causing some conflict issues....I deleted it from the target machine. I also deleted Google Desktop on a hunch that similar issues may exist.

But my money is on control/conflict issues with WordPerfect Office 12 she had installed in addition to w/ MS Office Pro 2003. I had also run up against this issue before....These two suites don't play well together!

After deleting these three programs (and making some of the suggested code changes), the macros seemed to run at an acceptable speed. BTW, monitoring CPU usage never jumped above 53%, changed priority to high (still didn't affect slow running macros)...and while my machine is a bit nicer

(Dell Precision 390), the target machine is an up-to-date Dell Dimension.

Thanks for all your help!!

Rylo, I'd like to ask you about some of your suggestions, though it would be easier via e-mail.....if you care to, send a msg to [removed].

Thanks Again!!