Accelerate VBA work in Excel

Foreword

It so happened that today there are a lot of people who have to work (write macros) on VBA in Excel. Some macros contain hundreds of lines of code that have to be executed every day (weekly, monthly, quarterly, and so on) and, at the same time, they take a fair amount of time. It seems that the process is also automated and human intervention is not needed, but the time taken to complete the macro can span tens of minutes, or even several hours. Time, as they say, is money, and in this post I will try to significantly speed up the execution time of your macro, and perhaps this will have a positive effect on your business, and in the end, on money.

Before starting work

Before I get straight to the point, I would like to draw attention to the post: A few tips on working with VBA in Excel . In particular, in the block “Macro Acceleration” there are useful code examples that should be used along with my tips on speeding up work to achieve maximum results.

We accelerate macro work

So, to the point ... In order to really speed up the work of VBA in Ecxel, you need to understand that turning to a cell on a sheet takes much time. If you want to write one value into a cell, it will not take much time, but if you need to write (read, access) thousands of cells, then it will take much longer. What to do in such cases? Arrays come to the rescue. Arrays are stored in memory, and operations in memory VBA performs hundreds or even thousands of times faster. Therefore, if you have thousands, hundreds of thousands of values ​​in your data, then the macro execution time can take from several minutes to several hours, and if this data is transferred to an array, the execution of the macro can be reduced to several seconds (minutes).

I will bring the sample code and explain in the comments what's what, it will be clearer. In addition, some lines of code that are not directly related to the acceleration process can be useful.
')

Example

Suppose that we have data on “Sheet1” (“Sheet1”). The data is contained in 50 columns (the columns contain names) and 10,000 lines. For example, we need to add a value to the last column, which is equal to the value in the second column divided by the value in the third column (starting from the 2nd line, since the first one contains the title). Then we take the first 10 columns and copy them to “Sheet2” (“Sheet2”), for further processing (for other needs). Let an example and banal, but, as it seems to me, it can display the whole essence of this post.

'   ,    '     Option Explicit Sub Test() '      Dim Sheet1_WS, Sheet2_WS As Worksheet '      ( ) Dim i As Long ',       Dim R_data As Variant '     Dim FinalRow, FinalColumn As Long '     ,     'Set Sheet1_WS = Application.ThisWorkbook.Worksheet("Sheet1") Set Sheet1_WS = Application.ThisWorkbook.Sheets(1) Set Sheet2_WS = Application.ThisWorkbook.Sheets(2) '        ',      ,         '   ,   ,     . ,       .        . FinalRow = Sheet1_WS.Cells(Rows.Count, 1).End(xlUp).Row '=10 000 '        FinalColumn = Sheet1_WS.Cells(1, Columns.Count).End(xlToLeft).Column '=50 '      1 R_data = Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) For i = 2 To FinalRow '     . ',       . ',    2  3    '    If R_data(i, 3) <> 0 Then R_data(i, FinalColumn) = R_data(i, 2) / R_data(i, 3) End If Next i '      1 '      (    ,   Sheet1_WS.Cells.ClearContents) Sheet1_WS.Cells.Delete Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) = R_data '   2,   10 . Sheet2_WS.Range(Sheet2_WS.Cells(1, 1), Sheet2_WS.Cells(FinalRow, 10)) = R_data '     Workbooks(Application.ThisWorkbook.Name).Close SaveChanges:=True End Sub 

In this example, the array is filled with the specified range. If we have an explicit two-dimensional array, then its value can be copied to the sheet in the following way:

 Dim R_new() As Variant ............................................ '     ReDim R_new(1 To FinalRow, 1 To 50) As Variant ........................................... Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, 50)) = R_new() 

Conclusion

Most data operations can be performed in an array, while displaying only the result on a sheet. Sometimes it is advisable to show the result on a sheet, then perform some actions (for example, sorting) and reload the data into an array.

For me, it was a big surprise to accelerate the operation of the macro due to arrays, since the data on the sheets, in fact, so represent a two-dimensional array. But it turns out that memory access is much faster than to the cells on the sheet.

In the future, I plan to write tips (examples) on a quick search for data on a sheet, but this will be another post. If you have questions, comments, please write.

Thanks for attention. Successful developments.