New answers tagged vba
1
You are doing twice the work by having a function to get the starting row and a second function get the last row. Passing the starting row into the GetLastRow() function would be more efficient.
I prefer to have a single function return the range object. Using the WorkshetFunction.Match() is far more efficient then iterating over the cells.
Results
...
1
75K Non-Contiguous Areas, No Problem
My FastUnion class was able to crack the 75K non-contiguous areas goal by expanding on Ahmed AU answer using Union() with multiple parameters. Although, this class excels at smaller numbers of areas, my UnionCollection class far out performs it by working with smaller groups of cells at a time.
Results
Immediate ...
2
My answer is very similar to Ahmed AU with a few exceptions.
- I didn't bother adding the Conditional formatting because everything on the new worksheet represents changes.
- The other main difference is that I match the Ranges using the Range addresses. These will automatically adjust for differences in column and rows count and starting cell.
Refactored ...
1
My answer makes the assumption that you mean to compare the values of the data and not the formulas, though much of the other comments here remain valid for your code.
Identify your function parameters as ByRef or ByVal. As you may guess, passing a variable "by reference" generally allows you to reference the variable as held by the caller and make ...
2
May try the modified code using Arrays to Compare. Tested with 250000 rows X 26 columns of random data and every 5th cells have value difference (Total 130000 differences). It takes around 18 secs to compare and another 22 secs to completes report generation with total 40 seconds only.
Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet)
Dim ws1row ...
2
There are some issues with your code:
1) Try to avoid unqualified references, this means always specify the worksheet, when referencing a cell.
2) Try to avoid formatting a lot of single cells, rather format them all at once at the end. Formatting slows down Excel a lot!
3) When handeling a great deal of Ranges, Integer can be insufficient, use Long ...
0
Here is my advice to speed it up.
Don’t repeat the same assignment of a value to a variable more than once.
For example:
rs.Fields("APP_NUMBER").Value
Can be assigned to a temporary variable and then used throughout until
rs.MoveNext
2
It is far easier to write, debug and modify smaller chunks of code that perform 1 or 2 operations. For this reason, the code should be separated into multiple subs and functions. I also recommend taking advantage of Field Aliases to give your Fields more meaningful names.
Refactored Code
Option Explicit
Const REDACTED = "<Connection String>"
...
1
Here is yet another approach, a fair bit simpler, albeit less robust than other solutions. The upside with this approach, is you only have to remember one Sub name to call, then just add a boolean parameter to enable/disable optimizations.
Public Sub OptimizeExcel(Optional EnableOptimizations as Boolean = True)
With Application
.ScreenUpdating = ...
1
Probably the single, most-used module in my personal library is Lib_PerformanceSupport, which helps to manage Application level performance controls. I designed (evolved) the methods in a way that they can be sprinkled liberally through the code and reused easily, even when nested. Though I could have designed this as a persistent object, it's implemented as ...
0
The question asked by the OP boils down to "How to make the macros work quicker". To that end the only optimization that you commonly see that isn't included in the OP's macros is Worksheet.DisplayPageBreaks.
I have answered and tackled this problem several times and feel that timing a procedure goes hand in hand to the code optimizations and have tried ...
0
I found the following in the VBAVault which addresses what FreeMan spoke about but I think it's a simpler way of doing the same thing.
Public CalcState As Long
Public EventState As Boolean
Public PageBreakState As Boolean
Sub OptimizeCode_Begin()
Application.ScreenUpdating = False
EventState = Application.EnableEvents
Application.EnableEvents = False
...
3
You should save the initial state of the Application.* variables before you mess with them, then reset them when you're done. Not all of your users will have the same settings and it's rude to assume they do.
A simple way to do it would be to implement a class like this:
'@Folder("Classes")
Option Explicit
Private Type ApplicationSettings
...
1
I am not saying how this is going to help, but I did the Same test on My system (Office 2010) couldn't find 2007.
So for the Test of the Range SpecialCells(xlCellTypeVisible), it took less than a second in my system to complete the Range as in your Answer using the same code on a Blank sheet.
Result in Debug.
Success at 1000 Range Area Count 250
Success ...
1
A few items as I look through the code:
Use Option Explicit. Always. Every time. Every module. No exception.
When I added Option Explicit (well, OK, I have it set to always add it), your code won't compile because you are using undeclared variables. Without it, VBA "helpfully" declares any new variable name you type as a Variant. Variants have use, but ...
2
Standard public announcement: always include Option Explicit at the top of modules.
How to improve performance
Two tips here:
Put your range into an array and work with the array, not Excel
objects. The switching between Excel and VBA models is computationally expensive.
Avoid wherever possible ReDimming and Preserve-ing arrays. They are computationally ...
3
Here are some ideas on improvements you can make in the overall code, presented in an example for one of the math functions. The ideas can be applied to all the other functions as well.
My lead-in to the rest of my comments and examples primarily deal with highly repeated logic in all of your functions. A big clue is when you end up copying a section of ...
4
I do have a few suggestions:
First of all, you don't need to reactivate your main
workbook every loop, since you hold it's reference in a variable
anyway.
Second, instead of creating new workbook and copying the sheet over, just make a copy to new workbook (i.e. wks.Copy creates copy in new workbook, you can then assign it to a variable since, as you said ...
0
I've corrected my original code accordingly to @AJD tips. Now it's fully working and lightning fast.
Sub MatrixTransform()
Dim originalMatrix() As Variant
Dim finalMatrix() As Variant
originalMatrix() = Selection.Value
MatrixRows = 1 + (Selection.Rows.Count - 1) * (Selection.Columns.Count - 1)
ReDim finalMatrix(MatrixRows, 3)
finalMatrix(1, 1) = CStr(...
3
Many areas to comment here.
Well done on including Option Explicit and good indenting!
I have identified some key themes:
Avoiding repetition
Avoid magic numbers
Meaningful variable names
Avoid switching between Excel and VBA unless really necessary
Properly qualify calls to Excel objects
Avoiding repetition
While there is not a lot of obvious ...
3
Your description is not very clear, but I see the usual suspects.
Indenting
Always indent your code properly - this makes it easier to read, easier to pick out the logic loops and easier to maintain. In itself, it does not guarantee code correctness.
Option Explicit
Always put Option Explicit at the top of your modules.
Variable names
Use descriptive ...
0
I want to share the result of simple tests carried out today to test limit of creating range of SpecialCells(xlCellTypeVisible). Only to keep the original post length readable, I am posting it as answer.
I filled the range A2:A150001 with 1 n number of times (each area length) with one 0 cell and filtered for 1 with code.
Sub FillRange()
Dim Arr(1 To ...
4
Don't be afraid to trash me or offend me.
Ok, I'll bite ;-)
But this isn't about you, it's all about the code. It's always about the code. Reviewers are not here to judge, they're here to help you grow, and improve your programming!
I obviously am new to this, and there has to be a better way to do it
Oh, yes, absolutely. But first, I need to know what ...
Top 50 recent answers are included
