Excel coding actions to avoid
Excel lends itself to some very fast development time mostly because you can exploit the arrangement of rows and columns, build scratch sheets for sub-tasks, and so on, instead of fumbling with arrays, strange variable declarations, and other intangible objects.
Excel is commonly compared to Python and rightfully so. Python is more suited for pure data analysis as a general programming language. Whereas VBA is really geared for a high level of automation in a business world that is saturated with Microsoft Windows and O365 users. Each has its own use in its rightful place. Certainly Python will need an integrated development environment (IDE) like PyCharm or Spyder to build standalone applications. These IDE's will need configuration setups and the development of a shell program. For Excel VBA though, just open a new spreadsheet file and you're good to go in less than 10 seconds.
So, if you're talking about machine learning or AI, then Python is probably a better choice. But if you're in a Microsoft shop and working with data extracts or reporting for finance, accounting, cashflow, sales analysis, manufacturing, configuration management, pareto analysis, procurement, or inventory control, then I truly believe Excel VBA is the clear choice. Excel VBA has been around for more than 20 years and has a huge following. It's not going anywhere for the remainder of my career time.
Still, there are programming actions that I avoid:
- ActiveSheet. I try to avoid any declaration that uses activesheet such as activesheet.range("A1").value. I always aim to declare my target sheet name especially in With statements. With Activesheet... End With has had some strange results. Always declare the sheet name if you can. My only exception is my hotkey macros that are mostly all activesheet since I use it generically across many different sheet names.
- Inserting Columns. (Columns.Selection.Insert Shift:=xlToRight) The action of inserting a column is time consuming and can really drag down your runtime, especially if there is more than 100,000 rows of data. If I am extracting 6 columns of data from a database and I know I will need to match a VLOOKUP into column C, I will SQL SELECT in a dummy column as column C for example: SELECT order, line, 'xxxxxx' as partno, orderqty, unitprice, extcost from PODATA; Then, when I have to VLOOKUP into column C, the column is already in place, I just have to overwrite the xxxxxx's.
- Screen Updating. In nearly all cases, I have screen updating turned off. (Application.ScreenUpdating = False). I'll turn updating back on to update the status bar so the user knows what's going on, then immediately turn it off again.
- Automatic Calculation. Auto Recalc On can be a super drag especially if you have a lengthy column of VLOOKUP formulas. Of course you have to be careful copying formulas around when Calculation is off. But if you can, especially with lengthy columns, turn off Recalcs (Application.Calculation = xlManual), copy down your formulas, and turn Calcs back on again ( Application.Calculation = xlAutomatic)
- Leaving VLOOKUPs as Formulas. Whenever possible, I immediately convert VLOOKUP formulas in a column to their resulting values (PasteValues). VLOOKUP recalcs are incredibly slow, especially on sorts. Sort a 20 column sheet with VLOOKUP formulas in three columns that are 135,000 rows deep. Yeah... that's going to take a while. Unless the user absolutely needs the actual formulas to be present (for reference), I always dump formulas (all formulas) for values.