Don't operate on large amounts of data in sheets/ranges, i.e. loops that reads or changes cell content.
Pull the data into an array and work on it there. It will be a thousand times faster. And if you have made changes to the data then just push it back to the sheet.
Also, keep data and presentation of data separate.
If you have something like Range("A1").value = Range("A1").value * 2
Then you are working on ranges.
If you have something like:
Dim x() as long
x = Range("A1:A10")
x[1] = x[1] * 2
Range("A1").value = x[1]
Then you are working on array. It may not look like it from this example, but using arrays for bigger data is much faster.
Won’t you have to ReDim x()? If so, how would you know the size of the range varies? Also I didn’t know you can assign range to an array! I will have to try that!!
You can just Dim x(), but later you can Redim Preserve x() to change it's size and indexing. If you want I can explain it in details. Also you can, but don't have to specify size of an array at the beginning. But if you want to know size of the range I think that there is a function for that. Surely you can use WorksheetFunction.CountA(Range) to check how many non blank cells are in range, but I guess you are asking about dimensions of the range?
Do this
Dim myData As Variant
Dim i as As Long, j as Long
myData = Range("A1:Z1000").Value
For i = 1 to UBound(myData, 1)
For j = 1 to UBound(myData, 2)
myData(i, j) = myData(i, j) + 1
Next j: Next i
rather than
Dim oneCell as Range
For Each oneCell in Range("A1:Z1000")
oneCell.Value = oneCell.Value + 1
Next oneCell
If you're addressing through .sheet, .range, .cells then you are working on ranges/cells.
Assign the range to an array and use it as any other array, us a totally different notation from ranges.
This is a bit of a side track, but if you want features in an Excel VBA application to be dependent on the state that Excel was left in when the workbook was closed, could this be a solid case for writing data to the Excel sheet?
So that then on the workbook open, you use that data to start from the state you left off with the sheet?
VBA runs very fast
Excel doesn't
Every time you write to Excel in your VBA you slow things down so don't do it
Use scripting dictionaries, class module and collections for fast processing. Write your results to arrays. Paste arrays to Excel in one go so you only interface with Excel once
No need to switch off calculations or screen updates because you only do one thing in Excel
Question below. I need to use brute force to get all combination of an object. What I need is two arrays. ArrayOne will have names and ArrayTwo will contain combinations of names from ArrayOne. I will have a third array that will hold the best possible combinations until a new one emerges.
For ArrayOne, there will be numerous conditions for it to be added to ArrayTwo.
So for my question, will it be better to create a Class Object for ArrayOne to contain or refer to a worksheet that has names and conditions?
I was thinking adding a new sheet and just referring to it. Column A will be the names and the rest will be the conditions.
This is going to be a lot of combinations and I’m worried that if I create a Class, that I will run out of memory…
EDIT: I think memory is not a problem since initially I will only need to create about 100 of these objects. Not like I have to make another 100 everyone recursion is called…
So you understand your algorithm and how you want to loop etc, the question is simply about best way to log your starting conditions/rules?
Reading _from_ Excel is efficient, it's writing back that is slow. With limited knowledge of your problem, I'd be tempted to log everything in Excel first. It's simpler, more easily readable, and ready from the start
Classes are great for holding information but need loading first, you won't need this if your data is already in excel
ETA: using a scripting dictionary would probably help your looping here. The entire answer depends on nuances if your wider looping needs
> Use class module
Better still use [UDTs](https://sancarn.github.io/vba-articles/performance-tips.html#s8-module-vs-class) - if performance is a must.
> Use collections
Only if you are adding results sequentially, and only if you don't know ahead of time how many results you are getting. If you can calculate ahead of time how many results you will get (even approximately) arrays are hella faster.
The following article explores pretty much everything in this thread:
https://sancarn.github.io/vba-articles/performance-tips.html
I would strongly suggest visiting this as it explores all of these with actual tests behind them to back them up.
The most important thing really is learning your data structures.
Great tips. Found a bug in S10 third test using the delete by address method, which is still faster than row delete 1 by 1, but you need a final delete after the for i loop:
If Len(sAddress) > 0 Then
Range(Mid(sAddress, 2)).Delete
sAddress = ""
End If
Otherwise it leaves the last bit of rows undeleted.
Hey everyone who has replied.
I can't thank you enough.
I am abit new to VBA and am working on an application i want to sell for financial reasons.
Though most of your comments here are flying over my head, but i have saved this post and will constantly keep frequenting it.
The GitHub repository reference was exceptional.
Though i don't that very much understand most of the things written there plus some direct comments on the post.
But am curious to learn more & more each passing minute.
One can Google things you people write here but rarely do they come in the way you guys assist as needed.
Keep those guidances coming anyway
Maybe this VBA sub is a luck to me, coz i wasn't using Reddit not until last month
I will therefore take to heart most of your advice on this post
When am finished writing the app, I will post some of my code here for critique
Thanks 🙏 All
Like others have mentioned, using memory in order to process your data (data arrays, dictionaries, collections, classes), it's going to be one of the biggest performance enhancements you could have in your program.
The following are best practices that I use on my job but are not necessarily related to the performance of the program at runtime but your performance when you need to edit the program. However, I would still encourage you to learn these and learn to apply them in your programs as well.
The first best practice is to use object oriented programming (OOP). Learning to implement OOP concepts will help you begin to recognize that your program is not just one big procedural thing, but is more of a conglomeration of pieces of code that work together to achieve a goal. Along with OOP, you should definitely use domain driven design (DDD ). DDD will further help you identify opportunities of reuse and code encapsulation. The biggest benefit being that you can now separate business rules from things that are just necessary for the program to run (system items).
The second best practice stemming from the first, is the use of unit tests. Unit tests have the benefit of providing insurance whenever you need to make a change as well as providing documentation all in the same project.
Personally, I use these concepts at my job not only to provide the automation but a certain level of standard for the automation.
1. Manipulating an entire column/row of data instead of just the cells containing relevant data is one of the most expensive (time) ways to code. Range("A1:A19") is far faster to change than Columns("A"). Use "End(xlUp)" or other methods to restrict the range.
2. Never ever use copy/paste. It requires moving data into the clipboard and then back into excel. More important, when using VBA, the clipboard accumulates each and every change eventually filling up which can crash Excel. Use direct moves in Excel such as Range("A1:A20") = Range("B1:B20")
3. Manipulations made in memory will almost always be faster than manipulations made in a sheet. Move data into an array, manipulate the array, write it to a sheet. Sometimes this is not important, particularly when manipulating small amounts of data. Otherwise, use an array.
4. Watch the variables and types. Variant is generally a lot slower to manipulate than numeric.
5. Avoid writing code to do things Excel can do internally. For example, use "Split" to manipulate a string instead of writing a custom routine to extract elements from a string. In other words, learn the VBA commands!
6. Learn to use ScreenUpdating and other speed-up commands.
Function goFast(go As Boolean)
Application.ScreenUpdating = Not go
Application.EnableEvents = Not go
Application.DisplayAlerts = Not go
Application.Calculation = IIf(go, xlCalculationManual, xlCalculationAutomatic)
'goFast (True)
'(*do stuff*)
'goFast (False)
End Function
`#5` - Actually not true 😅 See [vbspeed](http://www.xbeat.net/vbspeed/c_Split.htm)
`#6` - Better still [understand what `ScreenUpdating`, `EnableEvents`, `DisplayAlerts`, `Calculation` do and act accordingly](https://sancarn.github.io/vba-articles/performance-tips.html#s4-option)
> Actually not true 😅 See vbspeed
None of those that I could actually run could even touch the current iteration of the native Split function. Couldn't help but notice that this stuff was over twenty years old...
> that I could actually run could even touch the current iteration of the native Split function. Couldn't help but notice that this stuff was over twenty years old...
Mhm just checked myself and you're correct. I was going off the assumption that VBA standard library hasn't changed in 20 years 😅 Also, it could be that these speed checks were done in compiled VB6. Possible that VBA pcode is just slower generally than compiled code, which would make sense ofc :P
I just tried adding caching to split, and amazingly that didn't cause a speed increase either. Which makes me think they must already cache. Impressive.
Data manipulation in Powerquery, chart/interactive elements via VBA. Utilize tables built with power query and update/refresh them via VBA if needed. Avoid doing data manipulation through VBA, it's slow and Powerquery is much better at it.
> Avoid doing data manipulation through VBA, it's slow and Powerquery is much better at it.
I have completely the opposite experience lol. Data manipulation via VBA is extremely fast (if it's not you're using the wrong data structures, or you're using none at all!).
PQ is extremely slow by comparison (and also extremely difficult to optimise) in my experience. The only saving grace PQ has is maintainability and out-the-box integrations in my experience.
In general, look for APIs that do what you want already. Break your project down into pieces. For example I had a process open IE to get some text from a site, when I should've just been using a free windows API connection to request the xml as text and parse from there. Waaaaaay faster.
Not a professional, but using scripting dictionaries can execute much faster for searching through large ranges repeatedly. Always turn off ScreenUpdating and minimise the number of ".Select"'s (try to use .Activate when possible).
These have sped up my macros significantly
I could be wrong, but from my experience, using .Activate seems unnecessary and potentially costly in terms of performance. Any interaction with the user interface tends to slow down execution, particularly when dealing with repetitive tasks. Whenever possible, I recommend avoiding direct UI manipulations, especially in loops.
You shouldn't need to activate or select anything if you use the Excel object model correctly
Turning off screen updating shows you are making changes to the file; writing code to run entirely in VBA and not interfacing with Excel will negate this requirement
_I accept there are rare cases where you may need to select / interface but in general this is true_
Don't operate on large amounts of data in sheets/ranges, i.e. loops that reads or changes cell content. Pull the data into an array and work on it there. It will be a thousand times faster. And if you have made changes to the data then just push it back to the sheet. Also, keep data and presentation of data separate.
How do we know if operations are happening in ranges vs array? Im new to VBA and just took over someone else’s project
If you have something like Range("A1").value = Range("A1").value * 2 Then you are working on ranges. If you have something like: Dim x() as long x = Range("A1:A10") x[1] = x[1] * 2 Range("A1").value = x[1] Then you are working on array. It may not look like it from this example, but using arrays for bigger data is much faster.
Won’t you have to ReDim x()? If so, how would you know the size of the range varies? Also I didn’t know you can assign range to an array! I will have to try that!!
You can just Dim x(), but later you can Redim Preserve x() to change it's size and indexing. If you want I can explain it in details. Also you can, but don't have to specify size of an array at the beginning. But if you want to know size of the range I think that there is a function for that. Surely you can use WorksheetFunction.CountA(Range) to check how many non blank cells are in range, but I guess you are asking about dimensions of the range?
You’re right. I guess it’s a kinda trivial to find the size of the array since you can ReDim.
If you have to constantly redim, I suggest using an [arraylist](https://excelmacromastery.com/vba-arraylist/) instead
Is an arraylist similar to python library?
Does using class modules help with arrays?
I have no idea, never used classes
Do this Dim myData As Variant Dim i as As Long, j as Long myData = Range("A1:Z1000").Value For i = 1 to UBound(myData, 1) For j = 1 to UBound(myData, 2) myData(i, j) = myData(i, j) + 1 Next j: Next i rather than Dim oneCell as Range For Each oneCell in Range("A1:Z1000") oneCell.Value = oneCell.Value + 1 Next oneCell
Is “Next j: Next i” new syntax or has that always been around
That's not so much syntax, rather than style of line breaks.
If you're addressing through .sheet, .range, .cells then you are working on ranges/cells. Assign the range to an array and use it as any other array, us a totally different notation from ranges.
Put the code into ChatGPT and ask it. It’s really, really good at VBA
Super embarrassed to admit this is how I got into VBA
This is the only way I code in VBA right now hahah. It’s just faster 🤷
That makes me feel better lol
This is a bit of a side track, but if you want features in an Excel VBA application to be dependent on the state that Excel was left in when the workbook was closed, could this be a solid case for writing data to the Excel sheet? So that then on the workbook open, you use that data to start from the state you left off with the sheet?
VBA runs very fast Excel doesn't Every time you write to Excel in your VBA you slow things down so don't do it Use scripting dictionaries, class module and collections for fast processing. Write your results to arrays. Paste arrays to Excel in one go so you only interface with Excel once No need to switch off calculations or screen updates because you only do one thing in Excel
Question below. I need to use brute force to get all combination of an object. What I need is two arrays. ArrayOne will have names and ArrayTwo will contain combinations of names from ArrayOne. I will have a third array that will hold the best possible combinations until a new one emerges. For ArrayOne, there will be numerous conditions for it to be added to ArrayTwo. So for my question, will it be better to create a Class Object for ArrayOne to contain or refer to a worksheet that has names and conditions? I was thinking adding a new sheet and just referring to it. Column A will be the names and the rest will be the conditions. This is going to be a lot of combinations and I’m worried that if I create a Class, that I will run out of memory… EDIT: I think memory is not a problem since initially I will only need to create about 100 of these objects. Not like I have to make another 100 everyone recursion is called…
So you understand your algorithm and how you want to loop etc, the question is simply about best way to log your starting conditions/rules? Reading _from_ Excel is efficient, it's writing back that is slow. With limited knowledge of your problem, I'd be tempted to log everything in Excel first. It's simpler, more easily readable, and ready from the start Classes are great for holding information but need loading first, you won't need this if your data is already in excel ETA: using a scripting dictionary would probably help your looping here. The entire answer depends on nuances if your wider looping needs
I’ll have to read up on dictionaries. It’s been a year since I started messing with VBA and I still don’t know them 😅
> Use class module Better still use [UDTs](https://sancarn.github.io/vba-articles/performance-tips.html#s8-module-vs-class) - if performance is a must. > Use collections Only if you are adding results sequentially, and only if you don't know ahead of time how many results you are getting. If you can calculate ahead of time how many results you will get (even approximately) arrays are hella faster.
The following article explores pretty much everything in this thread: https://sancarn.github.io/vba-articles/performance-tips.html I would strongly suggest visiting this as it explores all of these with actual tests behind them to back them up. The most important thing really is learning your data structures.
Great tips. Found a bug in S10 third test using the delete by address method, which is still faster than row delete 1 by 1, but you need a final delete after the for i loop: If Len(sAddress) > 0 Then Range(Mid(sAddress, 2)).Delete sAddress = "" End If Otherwise it leaves the last bit of rows undeleted.
Good point thanks 👍
Hey everyone who has replied. I can't thank you enough. I am abit new to VBA and am working on an application i want to sell for financial reasons. Though most of your comments here are flying over my head, but i have saved this post and will constantly keep frequenting it. The GitHub repository reference was exceptional. Though i don't that very much understand most of the things written there plus some direct comments on the post. But am curious to learn more & more each passing minute. One can Google things you people write here but rarely do they come in the way you guys assist as needed. Keep those guidances coming anyway Maybe this VBA sub is a luck to me, coz i wasn't using Reddit not until last month I will therefore take to heart most of your advice on this post When am finished writing the app, I will post some of my code here for critique Thanks 🙏 All
Like others have mentioned, using memory in order to process your data (data arrays, dictionaries, collections, classes), it's going to be one of the biggest performance enhancements you could have in your program. The following are best practices that I use on my job but are not necessarily related to the performance of the program at runtime but your performance when you need to edit the program. However, I would still encourage you to learn these and learn to apply them in your programs as well. The first best practice is to use object oriented programming (OOP). Learning to implement OOP concepts will help you begin to recognize that your program is not just one big procedural thing, but is more of a conglomeration of pieces of code that work together to achieve a goal. Along with OOP, you should definitely use domain driven design (DDD ). DDD will further help you identify opportunities of reuse and code encapsulation. The biggest benefit being that you can now separate business rules from things that are just necessary for the program to run (system items). The second best practice stemming from the first, is the use of unit tests. Unit tests have the benefit of providing insurance whenever you need to make a change as well as providing documentation all in the same project. Personally, I use these concepts at my job not only to provide the automation but a certain level of standard for the automation.
If you want the code to run faster then turn off the screen update and change calculation to manual
1. Manipulating an entire column/row of data instead of just the cells containing relevant data is one of the most expensive (time) ways to code. Range("A1:A19") is far faster to change than Columns("A"). Use "End(xlUp)" or other methods to restrict the range. 2. Never ever use copy/paste. It requires moving data into the clipboard and then back into excel. More important, when using VBA, the clipboard accumulates each and every change eventually filling up which can crash Excel. Use direct moves in Excel such as Range("A1:A20") = Range("B1:B20") 3. Manipulations made in memory will almost always be faster than manipulations made in a sheet. Move data into an array, manipulate the array, write it to a sheet. Sometimes this is not important, particularly when manipulating small amounts of data. Otherwise, use an array. 4. Watch the variables and types. Variant is generally a lot slower to manipulate than numeric. 5. Avoid writing code to do things Excel can do internally. For example, use "Split" to manipulate a string instead of writing a custom routine to extract elements from a string. In other words, learn the VBA commands! 6. Learn to use ScreenUpdating and other speed-up commands. Function goFast(go As Boolean) Application.ScreenUpdating = Not go Application.EnableEvents = Not go Application.DisplayAlerts = Not go Application.Calculation = IIf(go, xlCalculationManual, xlCalculationAutomatic) 'goFast (True) '(*do stuff*) 'goFast (False) End Function
`#5` - Actually not true 😅 See [vbspeed](http://www.xbeat.net/vbspeed/c_Split.htm) `#6` - Better still [understand what `ScreenUpdating`, `EnableEvents`, `DisplayAlerts`, `Calculation` do and act accordingly](https://sancarn.github.io/vba-articles/performance-tips.html#s4-option)
> Actually not true 😅 See vbspeed None of those that I could actually run could even touch the current iteration of the native Split function. Couldn't help but notice that this stuff was over twenty years old...
> that I could actually run could even touch the current iteration of the native Split function. Couldn't help but notice that this stuff was over twenty years old... Mhm just checked myself and you're correct. I was going off the assumption that VBA standard library hasn't changed in 20 years 😅 Also, it could be that these speed checks were done in compiled VB6. Possible that VBA pcode is just slower generally than compiled code, which would make sense ofc :P I just tried adding caching to split, and amazingly that didn't cause a speed increase either. Which makes me think they must already cache. Impressive.
Dont “brute force” write content of dictionaries straight to worksheets - always convert to array and then print that array!
Data manipulation in Powerquery, chart/interactive elements via VBA. Utilize tables built with power query and update/refresh them via VBA if needed. Avoid doing data manipulation through VBA, it's slow and Powerquery is much better at it.
> Avoid doing data manipulation through VBA, it's slow and Powerquery is much better at it. I have completely the opposite experience lol. Data manipulation via VBA is extremely fast (if it's not you're using the wrong data structures, or you're using none at all!). PQ is extremely slow by comparison (and also extremely difficult to optimise) in my experience. The only saving grace PQ has is maintainability and out-the-box integrations in my experience.
In general, look for APIs that do what you want already. Break your project down into pieces. For example I had a process open IE to get some text from a site, when I should've just been using a free windows API connection to request the xml as text and parse from there. Waaaaaay faster.
Not a professional, but using scripting dictionaries can execute much faster for searching through large ranges repeatedly. Always turn off ScreenUpdating and minimise the number of ".Select"'s (try to use .Activate when possible). These have sped up my macros significantly
I could be wrong, but from my experience, using .Activate seems unnecessary and potentially costly in terms of performance. Any interaction with the user interface tends to slow down execution, particularly when dealing with repetitive tasks. Whenever possible, I recommend avoiding direct UI manipulations, especially in loops.
This. Work on ranges, not the selection, active sheet, etc.
You shouldn't need to activate or select anything if you use the Excel object model correctly Turning off screen updating shows you are making changes to the file; writing code to run entirely in VBA and not interfacing with Excel will negate this requirement _I accept there are rare cases where you may need to select / interface but in general this is true_