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 ...
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
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 ...
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:
Private Type ApplicationSettings
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 ...
Many areas to comment here.
Well done on including Option Explicit and good indenting!
I have identified some key themes:
Avoid magic numbers
Meaningful variable names
Avoid switching between Excel and VBA unless really necessary
Properly qualify calls to Excel objects
While there is not a lot of obvious ...
Your description is not very clear, but I see the usual suspects.
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.
Always put Option Explicit at the top of your modules.
Use descriptive ...
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 ...
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 ...
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 ...
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.
Const REDACTED = "<Connection String>"
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.
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 ...
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 ...
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)
.ScreenUpdating = ...
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 ...
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.
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