1 00:00:01,240 --> 00:00:06,190 In this video we're going to talk a little bit about some tips and tricks you can use to speed up your 2 00:00:06,190 --> 00:00:10,700 code as you start to build longer pieces of code in VBA. 3 00:00:10,780 --> 00:00:15,850 You can notice that your code could take upwards of several hours to execute properly. 4 00:00:15,850 --> 00:00:20,410 And so there are a few things that we can do to help speed that up significantly and hopefully bring 5 00:00:20,410 --> 00:00:22,180 that runtime down. 6 00:00:22,180 --> 00:00:27,690 So starting with some quick fixes the first thing you can do is turn off the auto calculation. 7 00:00:27,700 --> 00:00:30,370 And this is something that Excel does automatically. 8 00:00:30,460 --> 00:00:37,070 It will anytime you change a cell's value it will find any cell that references what you just change. 9 00:00:37,150 --> 00:00:39,950 And it will recalculate that cell as well. 10 00:00:39,970 --> 00:00:45,550 So when you're rewriting a whole lot of data in your actual excel file this can result in a whole lot 11 00:00:45,550 --> 00:00:50,010 of calculation that doesn't necessarily need to be done right at that moment. 12 00:00:50,020 --> 00:00:55,750 And so what we can do instead is we can force that calculation to wait until the end of our code or 13 00:00:55,750 --> 00:00:58,300 somewhere in the middle if we want to try to recalculate. 14 00:00:58,660 --> 00:01:02,620 So at the very beginning of your code you can use the line application. 15 00:01:02,640 --> 00:01:07,390 That calculation equals Excel calculation manual. 16 00:01:07,420 --> 00:01:13,270 And what this does is it changes the auto calculation property in Excel to wait instead of doing it 17 00:01:13,270 --> 00:01:19,480 automatically and only do it when we manually instruct Excel to recalculate everything and we can force 18 00:01:19,480 --> 00:01:27,820 that recalculation by using worksheets name whatever the name is Dot calculate or specifying a range 19 00:01:27,850 --> 00:01:29,410 and then using calculate. 20 00:01:29,410 --> 00:01:35,530 And so we can control more specifically what Excel is trying to recalculate rather than letting Excel 21 00:01:35,590 --> 00:01:37,340 recalculate everything. 22 00:01:37,340 --> 00:01:42,760 Anytime we change a value and you'll want to reset this to be automatic calculation again at the very 23 00:01:42,760 --> 00:01:43,540 end of your code. 24 00:01:43,540 --> 00:01:46,870 So one of the last lines of your code should be application. 25 00:01:46,880 --> 00:01:54,640 That calculation equals Excel calculation automatic which resets to automatic calculation like we had 26 00:01:54,640 --> 00:01:55,960 before. 27 00:01:55,960 --> 00:02:01,850 Along those same lines Excel automatically is going to try to continually update your screen. 28 00:02:01,900 --> 00:02:08,470 And this happens while your code is running so as your code is rewriting data or pulling new data out. 29 00:02:08,470 --> 00:02:13,990 Excel is going to try to update your screen to show you that new data being written into your file step 30 00:02:13,990 --> 00:02:20,650 by step and this can cause a huge delay in the amount of time it takes for your code to run as Excel 31 00:02:20,650 --> 00:02:23,490 tries to update its screen over and over again. 32 00:02:23,530 --> 00:02:28,930 And it also causes really annoying flashing of your screen which can be really overwhelming to look 33 00:02:28,930 --> 00:02:29,170 at. 34 00:02:29,170 --> 00:02:36,160 And so we can prevent that updating by using application that screen updating equals false which will 35 00:02:36,160 --> 00:02:42,970 both prevent the flashing and speed up your code significantly and you're going to want to again reset 36 00:02:42,970 --> 00:02:48,670 that at the end of your code using application that screen updating equals true. 37 00:02:48,750 --> 00:02:54,780 You can also turn off your status bar and turn off events to help things speed up as well so Excel actually 38 00:02:54,780 --> 00:03:00,120 has a little status bar that shows up in the bottom right corner of your screen as your code runs and 39 00:03:00,120 --> 00:03:05,910 you can manually code in how you want that status bar to update or you can let Excel try to predetermine 40 00:03:05,910 --> 00:03:07,100 that for you. 41 00:03:07,110 --> 00:03:13,230 Now when you have particularly long pieces of code your display status bar actually freezes up usually 42 00:03:13,230 --> 00:03:17,400 in the middle of your code as Excel starts to get a little overwhelmed with everything else that it's 43 00:03:17,400 --> 00:03:18,470 trying to do. 44 00:03:18,480 --> 00:03:23,220 So it's actually better to just turn this off in the beginning of your code and that way Excel isn't 45 00:03:23,220 --> 00:03:28,890 constantly trying to update a status bar so we can do that using application that display status bar 46 00:03:28,920 --> 00:03:33,720 equals false and then reset it using equals true at the end of your code. 47 00:03:33,810 --> 00:03:38,070 You can also turn off any events that might be set up to run in excel. 48 00:03:38,070 --> 00:03:44,190 So you might have an event that executes when a value changes or when you change your worksheet kind 49 00:03:44,190 --> 00:03:49,780 of similar to the fix that we talked about before with Excel auto calculating things. 50 00:03:49,890 --> 00:03:55,500 So we can actually turn off this event that might show up or events that might execute as we're working 51 00:03:55,500 --> 00:04:02,160 in that worksheet or in that table or just in that file in general using applications that enable events 52 00:04:02,190 --> 00:04:07,950 equals false at the beginning of our code and resetting it again using applications that enable events 53 00:04:07,950 --> 00:04:13,620 equals true at the end of our code and this will just prevent VBA from branching off and trying to run 54 00:04:13,650 --> 00:04:18,150 all of these different events as we're walking through our code and that will save us a lot of time 55 00:04:18,210 --> 00:04:21,730 in the long run outside of these quick fixes. 56 00:04:21,750 --> 00:04:26,600 There are some general rules of thumb that you can follow that will help your code run much faster. 57 00:04:26,940 --> 00:04:32,760 So the first general rule of thumb is that every time VBA actually gets involved in your Excel file 58 00:04:33,090 --> 00:04:40,170 every time it references your file to read data or activates your file to write data that adds a good 59 00:04:40,170 --> 00:04:41,760 chunk of time to your code. 60 00:04:41,760 --> 00:04:46,530 So if you're constantly flipping back and forth between reading and writing data moving back and forth 61 00:04:46,560 --> 00:04:51,300 between your code and your file you're going to have a code that takes a long time to run. 62 00:04:51,330 --> 00:04:58,380 And so we can minimize those interactions by trying to move or read data in chunks rather than cell 63 00:04:58,380 --> 00:05:05,280 by cell so we can take advantage of the range functions to reference entire ranges of data or the list 64 00:05:05,310 --> 00:05:11,130 object function to reference an entire table of data and we can convert that into arrays that we can 65 00:05:11,130 --> 00:05:12,600 use in our code. 66 00:05:12,600 --> 00:05:18,270 And that way we basically read that data once and we pull it into our code as a big array and then we 67 00:05:18,270 --> 00:05:23,640 can use that array to pull out the specific pieces of information that we need but because that array 68 00:05:23,640 --> 00:05:28,470 exists in our code and we're not flipping back and forth between the code and file anymore. 69 00:05:28,470 --> 00:05:29,810 We save ourselves a lot of time. 70 00:05:29,820 --> 00:05:35,750 So it's best to read and write data in chunks rather than individually you should also try to use the 71 00:05:35,750 --> 00:05:40,730 object hierarchy efficiently and take advantage of those with and end with statements that we've talked 72 00:05:40,730 --> 00:05:44,540 about to avoid having to retype that every single time. 73 00:05:44,700 --> 00:05:49,940 In this kind of goes back to if we were to step through that object hierarchy every time a it takes 74 00:05:49,940 --> 00:05:53,540 us a long time to actually type that out every time we want to do something. 75 00:05:53,540 --> 00:05:59,360 But it also takes Excel a minute or two to try to actually walk through that object hierarchy and figure 76 00:05:59,360 --> 00:06:01,010 out exactly what you're working with. 77 00:06:01,010 --> 00:06:05,750 So we want to try to avoid that as much as possible which we can do with it with and with statements 78 00:06:05,780 --> 00:06:11,260 or being careful about where we're activating a particular sheet or a particular workbook. 79 00:06:11,350 --> 00:06:16,870 We should also avoid the worksheet functions that Excel has so Excel has some intrinsic worksheet functions 80 00:06:16,870 --> 00:06:22,360 that you can use like the max function or the main function or V look up in that sort of thing and you 81 00:06:22,360 --> 00:06:28,630 can reference those in VBA but it takes VBA a long time to read that line because it has to go into 82 00:06:28,630 --> 00:06:29,900 your Excel file. 83 00:06:29,980 --> 00:06:36,100 It has to find the corresponding internal Excel function that you're referencing and it has to pull 84 00:06:36,100 --> 00:06:40,450 that back into your VBA code and apply the information that you want. 85 00:06:40,480 --> 00:06:45,370 So it takes a long time to reference worksheet functions and it's better generally to just hard code 86 00:06:45,370 --> 00:06:49,770 that information in rather than using excels predetermined worksheet functions. 87 00:06:49,900 --> 00:06:54,960 Because now you're not trying to switch back and forth between your code and your file to get that function. 88 00:06:55,000 --> 00:07:00,630 You can stay in your code and just hardcoded in there so you don't have to reference your file at all. 89 00:07:00,640 --> 00:07:06,130 We also talked about this a little bit when we looked at different variable types that there are. 90 00:07:06,190 --> 00:07:11,020 It's best practice to avoid variant type variables which means you should try to declare all of your 91 00:07:11,020 --> 00:07:15,220 variables at the beginning of your code as specifically whatever they are. 92 00:07:15,220 --> 00:07:17,750 So if they are text you should declare them as text. 93 00:07:17,920 --> 00:07:22,570 If they're an integer you should declare them as an integer rather than letting Excel try to create 94 00:07:22,570 --> 00:07:28,210 a variant type variable that it's going to change every time you set that variable equal to something. 95 00:07:28,210 --> 00:07:35,680 So best to avoid variant variables and the last thing and this helps a lot is don't use copy and paste. 96 00:07:36,040 --> 00:07:42,700 You can't copy and paste in VBA you can reference copy and paste functions but it takes again VBA a 97 00:07:42,700 --> 00:07:49,420 long time to try to access your Excel file run the copy functionality and then access your Excel file 98 00:07:49,420 --> 00:07:55,000 again to run the paste functionality and it takes a long time for Excel to actually execute that from 99 00:07:55,000 --> 00:07:55,960 VBA. 100 00:07:56,080 --> 00:08:02,050 Instead you can set entire ranges equal to one another so if you're trying to copy a column for example 101 00:08:02,380 --> 00:08:08,770 you can set column B equal to Column A and that way you're not copying and you're not piecing that data 102 00:08:09,100 --> 00:08:15,910 but you can walk through and essentially copy and paste by setting each range equal to the copied range. 103 00:08:15,910 --> 00:08:20,890 And that way you're not using copy and paste you're just using a range equals to a range which is all 104 00:08:20,890 --> 00:08:28,300 done within VBA executed within VBA and doesn't require any access of the actual copy and paste functionality 105 00:08:28,300 --> 00:08:30,640 so it runs a lot faster. 106 00:08:30,640 --> 00:08:35,320 So in this video we talked a little bit about some quick fixes and some general rules of thumb that 107 00:08:35,320 --> 00:08:40,870 you can use to speed up your code and I highly encourage you to use all of these to make sure your code 108 00:08:40,870 --> 00:08:42,460 is running quickly and efficiently.