In this video we're going to talk a little bit about some tips and tricks you can use to speed up your
code as you start to build longer pieces of code in VBA.
You can notice that your code could take upwards of several hours to execute properly.
And so there are a few things that we can do to help speed that up significantly and hopefully bring
that runtime down.
So starting with some quick fixes the first thing you can do is turn off the auto calculation.
And this is something that Excel does automatically.
It will anytime you change a cell's value it will find any cell that references what you just change.
And it will recalculate that cell as well.
So when you're rewriting a whole lot of data in your actual excel file this can result in a whole lot
of calculation that doesn't necessarily need to be done right at that moment.
And so what we can do instead is we can force that calculation to wait until the end of our code or
somewhere in the middle if we want to try to recalculate.
So at the very beginning of your code you can use the line application.
That calculation equals Excel calculation manual.
And what this does is it changes the auto calculation property in Excel to wait instead of doing it
automatically and only do it when we manually instruct Excel to recalculate everything and we can force
that recalculation by using worksheets name whatever the name is Dot calculate or specifying a range
and then using calculate.
And so we can control more specifically what Excel is trying to recalculate rather than letting Excel
recalculate everything.
Anytime we change a value and you'll want to reset this to be automatic calculation again at the very
end of your code.
So one of the last lines of your code should be application.
That calculation equals Excel calculation automatic which resets to automatic calculation like we had
before.
Along those same lines Excel automatically is going to try to continually update your screen.
And this happens while your code is running so as your code is rewriting data or pulling new data out.
Excel is going to try to update your screen to show you that new data being written into your file step
by step and this can cause a huge delay in the amount of time it takes for your code to run as Excel
tries to update its screen over and over again.
And it also causes really annoying flashing of your screen which can be really overwhelming to look
at.
And so we can prevent that updating by using application that screen updating equals false which will
both prevent the flashing and speed up your code significantly and you're going to want to again reset
that at the end of your code using application that screen updating equals true.
You can also turn off your status bar and turn off events to help things speed up as well so Excel actually
has a little status bar that shows up in the bottom right corner of your screen as your code runs and
you can manually code in how you want that status bar to update or you can let Excel try to predetermine
that for you.
Now when you have particularly long pieces of code your display status bar actually freezes up usually
in the middle of your code as Excel starts to get a little overwhelmed with everything else that it's
trying to do.
So it's actually better to just turn this off in the beginning of your code and that way Excel isn't
constantly trying to update a status bar so we can do that using application that display status bar
equals false and then reset it using equals true at the end of your code.
You can also turn off any events that might be set up to run in excel.
So you might have an event that executes when a value changes or when you change your worksheet kind
of similar to the fix that we talked about before with Excel auto calculating things.
So we can actually turn off this event that might show up or events that might execute as we're working
in that worksheet or in that table or just in that file in general using applications that enable events
equals false at the beginning of our code and resetting it again using applications that enable events
equals true at the end of our code and this will just prevent VBA from branching off and trying to run
all of these different events as we're walking through our code and that will save us a lot of time
in the long run outside of these quick fixes.
There are some general rules of thumb that you can follow that will help your code run much faster.
So the first general rule of thumb is that every time VBA actually gets involved in your Excel file
every time it references your file to read data or activates your file to write data that adds a good
chunk of time to your code.
So if you're constantly flipping back and forth between reading and writing data moving back and forth
between your code and your file you're going to have a code that takes a long time to run.
And so we can minimize those interactions by trying to move or read data in chunks rather than cell
by cell so we can take advantage of the range functions to reference entire ranges of data or the list
object function to reference an entire table of data and we can convert that into arrays that we can
use in our code.
And that way we basically read that data once and we pull it into our code as a big array and then we
can use that array to pull out the specific pieces of information that we need but because that array
exists in our code and we're not flipping back and forth between the code and file anymore.
We save ourselves a lot of time.
So it's best to read and write data in chunks rather than individually you should also try to use the
object hierarchy efficiently and take advantage of those with and end with statements that we've talked
about to avoid having to retype that every single time.
In this kind of goes back to if we were to step through that object hierarchy every time a it takes
us a long time to actually type that out every time we want to do something.
But it also takes Excel a minute or two to try to actually walk through that object hierarchy and figure
out exactly what you're working with.
So we want to try to avoid that as much as possible which we can do with it with and with statements
or being careful about where we're activating a particular sheet or a particular workbook.
We should also avoid the worksheet functions that Excel has so Excel has some intrinsic worksheet functions
that you can use like the max function or the main function or V look up in that sort of thing and you
can reference those in VBA but it takes VBA a long time to read that line because it has to go into
your Excel file.
It has to find the corresponding internal Excel function that you're referencing and it has to pull
that back into your VBA code and apply the information that you want.
So it takes a long time to reference worksheet functions and it's better generally to just hard code
that information in rather than using excels predetermined worksheet functions.
Because now you're not trying to switch back and forth between your code and your file to get that function.
You can stay in your code and just hardcoded in there so you don't have to reference your file at all.
We also talked about this a little bit when we looked at different variable types that there are.
It's best practice to avoid variant type variables which means you should try to declare all of your
variables at the beginning of your code as specifically whatever they are.
So if they are text you should declare them as text.
If they're an integer you should declare them as an integer rather than letting Excel try to create
a variant type variable that it's going to change every time you set that variable equal to something.
So best to avoid variant variables and the last thing and this helps a lot is don't use copy and paste.
You can't copy and paste in VBA you can reference copy and paste functions but it takes again VBA a
long time to try to access your Excel file run the copy functionality and then access your Excel file
again to run the paste functionality and it takes a long time for Excel to actually execute that from
VBA.
Instead you can set entire ranges equal to one another so if you're trying to copy a column for example
you can set column B equal to Column A and that way you're not copying and you're not piecing that data
but you can walk through and essentially copy and paste by setting each range equal to the copied range.
And that way you're not using copy and paste you're just using a range equals to a range which is all
done within VBA executed within VBA and doesn't require any access of the actual copy and paste functionality
so it runs a lot faster.
So in this video we talked a little bit about some quick fixes and some general rules of thumb that
you can use to speed up your code and I highly encourage you to use all of these to make sure your code
is running quickly and efficiently.