Application.ScreenUpdating = False
I find it scary that the corresponding
= True is nowhere in your code, for reasons already mentioned. Whenever I turn off screen updating, I find it's good UX to also specify a status bar message, and change the mouse cursor to a hourglass. Something along these lines:
Public Sub ToggleWaitMode(Optional ByVal waitMode As Boolean = False)
Application.ScreenUpdating = waitMode
Application.Calculation = IIf(waitMode, xlCalculationManual, xlCalculationAutomatic)
Application.StatusBar = IIf(waitMode, "Please wait...", vbNullString)
Application.Cursor = IIf(waitMode, xlWait, xlDefault)
Which makes your procedure stub look like this:
Public Sub DoSomething()
On Error GoTo ErrHandler
'do that thing
If Not Application.ScreenUpdating Then ToggleWaitMode
' handle errors here
If I turn Calculation on Manual then my values are no good because...
If I understand properly, you need to update
$N$13 some 2,000 times with a value that's in
"$B$" & i, and then I guess
$W$12 need to be recalculated accordingly.
You haven't shown us what these cells contain and what cells their formula is referring to, but if they're the only cells that need to be recalculated when
$N$13 changes, then you can force calculation like this:
But that might not speed up anything. You're pretty much stuck, since you need to recalculate these three cells before you can do anything, and you need to do that 2,000 times.
I think you're somewhat misusing VBA here, it looks like you could use 3 hidden columns (say,
$AA$4:$AC$2004) and use Excel formulas to automatically calculate the would-be "U", "V" and "W" values for each row; the VBA macro could then just copy values from
Repository!$C$4:$E$2004.. if a macro is even needed for that.
I would suggest naming the ranges/cells in row 12 - anytime you have a specific cell with a specific meaning, it's always better for the VBA code to refer to the meaning rather than the cells' addresses.
I have no clue what these cells mean, but picture this:
Dim interestRate As Double
interestRate = wsInput.Range("InterestRate").Value
This extra abstraction level somewhat decouples the VBA code from the worksheet structure, which allows you to modify [at least parts of] the worksheet without having to modify the VBA code - for example you could insert another row and now
InterestRate is read in row 13 instead of 12, and the VBA code couldn't care less.
You can define names in the [Formulas] Ribbon tab, under the [Defined Names] section. Or you can just select the cell and type its name in the address/names dropdown, just left of the formula bar.
This also has the advantage of making your Excel formulas more readable: instead of
=$X$12*$N42 a formula can now look like
One last thing, I know it's common to call a worksheet variable like
wsInput, but I find it sounds backwards and looks Hungarian. I'd call it
wsRepository would be
repositorySheet. Also it wouldn't hurt to rename