PDA

View Full Version : Need help in accelerating a process(too many loops)



FrancisZheng
02-23-2017, 03:41 AM
Hello everyone,

I have been given 2 macros to proceed a process everyday. They work independently. There's no bug in the code. The code is commented.

However, it takes too much time when running(an hour in total, and it blocks the computer sometimes). I've been told that there are some unnecessary loops in each piece of code that speed down the process. Unfortunately, I'm really not specialised in VBA. So I hoped you guys could give me some insight in simplifying the code.

18452
18453

The instructions of the process are explained in detail in the first sheet of each file.

Thank you in advance.

Regards,
Francis

SamT
02-23-2017, 08:56 AM
Way, way, way too many Select...Selections.
It is boring, tedious, basic beginner "Gee, what's a macro" noobie work to eliminate those.


A total lack of With ... End With'es
It is boring, tedious, basic beginner noobie work to eliminate those.


Way, way too many Active Whatever'es.
It is boring, tedious, interested party, getting better, but still a noob, work to eliminate those.





Fixing those three issues should improve the speed by 30% - 50%

FrancisZheng
02-23-2017, 10:01 AM
Hi Sam,

I'll try to reduce the frequency of Select and Active and replace them with With instead. Are Select and Active really so slow that I can speed up by 30%-50%?

Paul_Hossler
02-23-2017, 11:01 AM
There's a lot of code in the 2 workbooks

Which part runs slowly?

SamT
02-23-2017, 12:44 PM
While not technically true, think of it this way


Workbooks("XYZ").Sheets("Sheet1").Range("a1)).Font.Bold = True
Requires the computer to read the hard drive and load the Workbook into memory, Look at Sheet1, Range A1, Then refer to the Font Object of that Range and set the Bold Property. Then it will unload all that from memory.

If the Next line merely changes the Font Size to 10, it must repeat all that. Again
Next, Change the Font.Name. Repeat again, all over again.

This is virtually three time faster.

With Workbooks("XYZ").Sheets("Sheet1").Range("a1").Font.
.Bold = True
.Size = 10
.Name = "Times New Roman"
'For every reference to Font, remove yet another load into and unload from memory
End With

While eliminating Select... Selection's doesn't give as drastic an advantage, it is still worthwhile, especially in loops, where the speed up is present in every iteration of the loop.



When speed is a concern, setting frequently accessed Objects to Variables helps because, once set, they stay in memory until the Sub is Ended.

For example, suppose you refer to Workbooks("XYZ") and several of its Sheets

Set Masterbook = Workbooks("XYZ.xlsx")
Masterbook.Sheets(1)
Masterbook.Sheets(2)
Is better

Referring to sheet1 even just twice

Set Sht1 = Masterbook.Sheets("Sheet1")
Is faster

The only time an Object Variable is not faster is when the code in a sub only refers to the Object one time.

FrancisZheng
02-24-2017, 02:09 AM
Hi Paul,

The piece of code that runs the slowest would be the file "CHECK REORG", in which both the import and the analysis are quite slow.

Plus, when I import the no.5 file : ".2016.12.21 (Post-UAT4) GBIS7 INT2 SUF - ALL.xlsx", if I import the file in .xlsb(which saves space), it bugs. However, I can import the other files in .xlsb with no problem. Do you know what the reason would be?

Francis

FrancisZheng
02-24-2017, 04:13 AM
Thank you Sam.

I had no idea how Excel proceeds when compiling a code. I've learnt a lot. I will try to simplify the way you did.


Francis

Paul_Hossler
02-24-2017, 06:24 AM
Hi Paul,

The piece of code that runs the slowest would be the file "CHECK REORG", in which both the import and the analysis are quite slow.

Plus, when I import the no.5 file : ".2016.12.21 (Post-UAT4) GBIS7 INT2 SUF - ALL.xlsx", if I import the file in .xlsb(which saves space), it bugs. However, I can import the other files in .xlsb with no problem. Do you know what the reason would be?

Francis

What I meant was "Is there / Are there particular subs that run too slow?"

That XLSB has hundreds of lines of code and dozens of subs/functions and since there is no test data, any thoughts would have to be by Desk Check

SamT
02-24-2017, 09:11 AM
When you import an xlsx as an xlsm, Excel first converts the file, then opens it. Then when you save the xlsb as xlsx, Excel has to convert it back before writing to disk.

FrancisZheng
02-24-2017, 09:33 AM
What I meant was "Is there / Are there particular subs that run too slow?"

That XLSB has hundreds of lines of code and dozens of subs/functions and since there is no test data, any thoughts would have to be by Desk Check

I think the sub "ReformatFPV" and "AddStatus" in the module "Action_TREEVIEW" are the ones that run the slowest.

Francis.

FrancisZheng
02-24-2017, 09:37 AM
When you import an xlsx as an xlsm, Excel first converts the file, then opens it. Then when you save the xlsb as xlsx, Excel has to convert it back before writing to disk.

Is there a default file type when importing files? I don't know why the type .xlsb works for the other files when importing.

SamT
02-24-2017, 10:39 AM
Have you got a copy of the code that you have "cleaned up?"


I don't know why the type .xlsb works for the other files when importing.No Code in them? Maybe?

FrancisZheng
02-27-2017, 06:50 AM
Hi Sam,

I didn't work during the weekend. I'm still trying to "clean up" the code of "CHECK REORG" by adding as many "with"s as possible.

Francis

FrancisZheng
03-16-2017, 03:30 AM
Got it

SamT
03-16-2017, 12:08 PM
Gonna Share?

FrancisZheng
03-20-2017, 09:35 AM
Sorry Sam, I thought I got it. It turned out that the code got faster because some procedures were skipped because of "On Error Resume Next"...

mdmackillop
03-20-2017, 10:30 AM
There is a timer example (http://www.vbaexpress.com/kb/getarticle.php?kb_id=1068) here for accurately testing the speed of code elements

FrancisZheng
03-21-2017, 04:23 AM
Thank you. I'm looking into that.

SamT
03-21-2017, 07:01 AM
Well? Let's see what you have now.