Windows 7, 64-bit PC... Slow macro (VBA)

[Will from London]

Hi

My company has just changed my PC from:
Windows 7 (32 bit), Office 2010 with 4GB of RAM (Intel i3-4160T 3.1GHz) to
Windows 7 (64 bit), Office 2013 (32 bit) with 8GB of RAM (Intel i3-2120 3.3GHz)

A VBA macro took about 7 minutes on my old PC but after 2 hours of running on the new PC it had only processed about half the data. The code essentially loops through each line of 7 CSV files (ranging from 10k to 200k lines), does a little bit of manipulation and then writes the line to 7 new CSVs. Even on my even older PCs it took a few minutes not hours.

I've seen that VBA code on 64-bit Excel needs to be slightly different (declared Long's etc) but I've still got 32-bit Office so does anyone have any ideas?

Thanks in advance

Will

 

[BiocideJ]

Can you post some (or all) of your code so we can have an idea of what exactly is going on.
There are probably a bunch of things you can do to speed things up, but without knowing what is going on currently, it is impossible to offer specific recommendations.

The only systemic issues I am aware of (not necessarily the only one there is) involves Protecting/Unprotecting sheets.
You could always try turning screenupdating off before the macro and back on after the macro and see if that helps.

 

[Will from London]

Thanks for your reply. The issue is more that the 64-bit PC is running at about 1/34th of the speed of the 32-bit one using exactly the same data, code, drive etc. I retested on a colleague's 32-bit and it took under 7 minutes. That sort of time is fine. 2-4 hours is not.

I can't post the code due to security rules but anyway, the code does the following (with explicit declarations):
1. Open path & existing filename For Input As #1
2. Open path & new filename For Output As #2 (after checking that new filename did not already exist)
3. Line Input #1, tempStr
4. Uses "Replace" to change some of the tempStr text
5. Print #2, tempStr
6. Application.StatusBar = j
7. j = j + 1
8. Loops through 3-8 using "Do Until EOF(1) = True"
9. Close #2
10. Close #1
11. Application.StatusBar = False

It doesn't use the Excel sheets themselves to do any processing, so switching screenupdating off is unlikely to help (pathname & filename etc are in cells for inputs). Calculation is manual and there are no other Excel files open.

Regards

Will

 

[Will from London]

The variable j is a "Long" and tempStr is "String" by the way.

 

[Eric W]

There's very little in your description of your macro that sounds like it can be sped up.

When you run a 32-bit program on a 64-bit version of Windows, it has to run under something called WoW64 (Windows on Windows). Here's a description:

https://en.wikipedia.org/wiki/WoW64

I can't say for sure, but often when you have another layer of interpreter between the OS and your program, that can slow things down. You may want to see if you can get a 64-bit version of Excel to experiment with.

Lacking that, your macro sounds pretty basic. Maybe you can try rewriting it in Java or Python. Both can be obtained for free from the Internet, and aren't too hard to learn for someone with basic programming experience.

Hope this helps.

 

[Will from London]

Thanks for the reply and link.

I will be given the opportunity to experiment on a 64-bit version of Office soon.

Also, I've not tested this Excel file on another 64-bit machine to deal with the possibility that my machine is the problem (but I've not noticed anything else be orders of magnitude slower). I've also not tested any of my other, more complicated, macros.