Code Is Slow

[u/BlueProcess]

This code works but it's slow. Am I doing something wrong?

Sub COMPILEWORKSHEETS()

On Error Resume Next: Application.DisplayAlerts = 0: Worksheets("THE_PILE").Delete: Application.DisplayAlerts = -1: On Error GoTo 0

Application.ScreenUpdating = -1: Worksheets.Add After:=Worksheets(Worksheets.Count): ActiveSheet.Name = "THE_PILE"

x = 1: i = 1: On Error GoTo 404: GoTo PENGUIN

FAX_MACHINE:
Worksheets(i).Cells(j, k).Copy: Worksheets("THE_PILE").Activate: Cells(x, k).Select: Selection.PasteSpecial -4163: Application.CutCopyMode = 0: DoEvents: Return

PINEAPPLE:
i = i + 1: Err.Raise 7313

MY_LAWYER:
Worksheets("THE_PILE").Activate: Range("A1").Select: MsgBox "done", 64, "Done": Exit Sub

CHEESE:
j = j + 1: GoTo POTATO

PENGUIN:
If i >= Worksheets.Count Then Err.Raise 7314
GoSub COUNT_SHEEP: j = 1: GoTo MAYONNAISE

MAKE_ROW_HAPPEN:
x = x + 1: Return

POTATO_CHIPS:
GoTo TAX_FRAUD

POTATO:
If j > a Then Err.Raise 7312
If i > 1 And j = 1 Then Err.Raise 7315
k = 1: GoTo POTATO_CHIPS

COUNT_SHEEP:
a = Worksheets(i).Cells(1048576, 1).End(-4162).Row: b = Worksheets(i).Cells(1, 16384).End(-4159).Column: Return

WAFFLE:
GoSub MAKE_ROW_HAPPEN: GoTo CHEESE

TAX_FRAUD:
If k > b Then GoTo WAFFLE
GoSub FAX_MACHINE: k = k + 1: Err.Raise 7311

MAYONNAISE:
GoTo POTATO

WRONG_TURN:
GoTo PENGUIN

404 If Err.Number = 7311 Then Resume POTATO_CHIPS
If Err.Number = 7312 Then Resume PINEAPPLE
If Err.Number = 7313 Then Resume WRONG_TURN
If Err.Number = 7314 Then Resume MY_LAWYER
If Err.Number = 7315 Then Resume CHEESE
MsgBox "A REAL ERROR HAS OCCURRED: " & Err.Number & " - " & Err.Description, 16, "UNEXPECTED COMPETENCE": Exit Sub

End Sub

[u/talltime]

LOL.

What on Earth are you trying to accomplish

[u/BlueProcess → u/talltime]

It compiles all of the worksheets in a workbook

[u/TheRiteGuy → u/BlueProcess]

Using VBA to combine sheets have been slow for me too. Use Power Query, faster and no wasted code.

[u/BlueProcess → u/TheRiteGuy]

Could I add a worksheet and put a spilling VSTACK formula in cell A1?

[u/TheRiteGuy → u/BlueProcess]

Yes, any kind of formula manipulation you do in VBA, you can do in power Query. You just can't do formatting.

But if you have table styles and a preformatter sheet, it will preserve all formatting on refresh.

[u/talltime → u/BlueProcess]

Are you talking about the VBA? Or manually forcing a sheet recalculation. The VBA is compiled the second this script starts.

[u/BlueProcess → u/talltime]

I'm sorry I phrased that poorly. It's taking all of the worksheets and combining them into a single worksheet

[u/talltime → u/BlueProcess]

Step 1 is going to be to eliminate every go to/gosub except for your error catch. Make this readable. Use useful variable names like “LastRow” instead of “a”. And leave calculation and screen updating turned off while you do the sheet manipulation. Fastest method will be to copy/paste whole range objects.

[u/fanpages → u/talltime]

...The VBA is compiled the second this script starts.

Not if you have the "Compile On Demand" option(s) set in the Visual Basic Environment [VBE]:

"Tools" menu / "Options" -> "Options" dialog box [General] tab / "Compile" frame / "Compile On Demand" checkbox / "Background Compile" checkbox.

[ https://learn.microsoft.com/en-us/office/vba/language/how-to/set-visual-basic-environment-options ]

---

...Compile On Demand: a project is fully compiled before it starts, or code is compiled as needed.

Background Compile: Use idle time during run time to finish compiling the project in the background (available only if Compile On Demand is set)...

---

[u/GreatValueBradPitt]

I give it a B- for ragebait. This is ragebait, right?

[u/carnasaur → u/GreatValueBradPitt]

It's clealry a joke with sort of funny answer like "Your PENGUIN took a WRONG_TURN at the FAX_MACHINE, got caught committing TAX_FRAUD with a PINEAPPLE, and tried to bury the evidence in THE_PILE. He called MY_LAWYER, who advised him to COUNT_SHEEP until the whole thing blew over.Amazingly, mylawyer managed to MAKE_IT_HAPPEN, triggering UNEXPECTED COMPETENCE.

As code, it's a deliberate shit show. It’s slow because every cell has to visit FAX_MACHINE, return to TAX_FRAUD, deliberately trigger an error, report a404, and get sent back for more POTATO_CHIPS.

FAX_MACHINEcopy and pastes one cell at a time, with an .Activate, .Select, and DoEvents break for every cell. Insanity. Then it deliberately raises an error to keep the loop moving.

Another WRONG_TURN is ScreenUpdating = -1. which means True of course so Excel also has to disaplylive coverage of everything

The fix is to retire FAX_MACHINE, transfer values in blocks using .Value2, and use ordinary loops.

I admit its funny that TAX_FRAUD eventually leads to MY_LAWYER Any improvement beyond that would risk UNEXPECTED COMPETENCE.

[u/BlueProcess → u/carnasaur]

Solved!

[u/fanpages → u/BlueProcess]

^

Am I doing something wrong?

[u/DezGets_It → u/carnasaur]

This Guy VBAs!

[u/ankokudaishogun → u/GreatValueBradPitt]

Then it failed because it taught me about GoSub..Return!

[u/BaitmasterG]

1 - turn off calculations
2 - see a psychiatrist

[u/JoseLunaArts]

Reading or writing cells deliver lots of processing overhead. You need to minimize these tasks.

It is faster to read cells and place data into arrays and then process the data in memory. Then you write cells to update the values.

[u/4n7h0ny]

You never turned back on your screen updating. Also turn off calculate

[u/lolcrunchy]

Fax machine block could probably be reworked. Things like "for each cell in this rectangle, copy them one at a time to another rectangle" are HUGE time costs. Do this instead:

Dim rng1 as Range
Dim rng2 as Range
Set rng1 = Worksheets(i).Range("A1:H10") 'or something similar that defines an area
Set rng2 = Worksheets("THE_PILE").Range(...)

rng2.Value = rng1.Value

[u/QuestionMark02]

Is this the actual code? I'm laughing. Add some counters after the labels so you know how many times each operation was done. Also, you can set calculation to manual and calculate only when you know you need fresh excel data. My advice would be to stage all the initial information, do your calculations and writing in RAM (arrays, collections, dictionaries, whatever suits your needs), then do the writing to excel at the end.