Need help in accelerating a process(too many loops)

[FrancisZheng]

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.

CHECK GAP ANALYSIS.xlsb
CHECK REORG.xlsb

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

Thank you in advance.

Regards,
Francis

[SamT]

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]

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]

There's a lot of code in the 2 workbooks

Which part runs slowly?

[SamT]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Got it

[SamT]

Gonna Share?

[FrancisZheng]

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]

There is a timer example here for accurately testing the speed of code elements

[FrancisZheng]

Thank you. I'm looking into that.

[SamT]

Well? Let's see what you have now.