There are various means of adding some speed to your VBA code. Probably not the most obvious one, but try to keep track of types of variables, define them explicitly. There is an option, which you put at the beginning of the file which will help you with it
Moreover, there are different functions versions which do the same. Most of the time you do not have to define explicit types of variables, VBA does that for you. But it takes time. Some of the functions have their faster versions, for example,
Mid. It returns Variant, but if you use
Mid$ you are using the function which returns String, and no converstion has to be done later. If you want to see how big difference does it make have a look at tests performed by Shamrock Software GmbH. In some cases
Mid$ was twice faster than
Mid. On the same site we read that, functions with a W (for wide) do not have to convert the VB-internal 16-bit Unicode characters to 8-bit ANSI codes or vice versa, they are a bit faster. Keep in mind that there are many function, which have their $ versions:
Chr$ ChrB$ *Command$ CurDir$ Date$ Dir$ Error$ Format$ Hex$ Input$ InputB$ LCase$ Left$ LeftB$ LTrim$ Mid$ MidB$ Oct$ Right$ RightB$ RTrim$ Space$ Str$ String$ Time$ Trim$ UCase$.
Another idea of speeding up the code execution is to turn off all the side code executions like updating screen, status bar, etc. A nice list of those was provided on Microsoft’s blog, see code snippets below. The idea is to save the state of sheet at the beginning of code execution, turn them off, and restore at the end. They do not mention what should happen in case of error, but you should keep that in mind and add a piece of code that would revert the previous state of Excel in case of error.
'' Get current state of various Excel settings; '' put this at the beginning of your code screenUpdateState = Application.ScreenUpdating statusBarState = Application.DisplayStatusBar calcState = Application.Calculation eventsState = Application.EnableEvents '' Note this is a sheet-level setting displayPageBreakState = ActiveSheet.DisplayPageBreaks
'' Turn off some Excel functionality so your code runs faster Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.Calculation = xlCalculationManual Application.EnableEvents = False '' Note this is a sheet-level setting ActiveSheet.DisplayPageBreaks = False
'' After your code runs, restore state; '' put this at the end of your code Application.ScreenUpdating = screenUpdateState Application.DisplayStatusBar = statusBarState Application.Calculation = calcState Application.EnableEvents = eventsState '' Note this is a sheet-level setting ActiveSheet.DisplayPageBreaks = displayPageBreaksState
This is probably only a tip of an iceberg, but for sure it is a good place to start.