Performance in both of these areas is very similar between VBA and VB6 COM Add-ins.
Similar to selecting ranges and objects to perform an action in the sheet, an explicit reference to the sheet also slows down processing. For example, the following code references the same cell (value) six times: Function Return Fee Slow() Select Case Range("I4") Case 1 Return Fee = Range("I4") * 10 Case 2 Return Fee = Range("I4") * 20 Case 3 Return Fee = Range("I4") * 30 Case 4 Return Fee = Range("I4") * 40 Case 5 Return Fee = Range("I4") * 50 End Select Msg Box Return Fee, vb OKOnly End Function At the very least, Return Fee Slow() makes two explicit references to I4.It's not changing the value, it's using the value in a simple expression.However, if you're working with a complex custom application, these easy-to-implement changes should improve efficiency.Whenever you have a task to automate, you can usually go down the VBA route. Maybe later you add some features here-and-there, making your VBA code more powerful. Color = vb White End If i = i 1 Next cell ' offset i to alternate column coloring i = i 1 Next repeat Msg Box "Total time was: " & (Timer - start Time) End Sub Before moving on, let’s get a better understanding of why this is taking so long. Color = vb White End If i = i 1 Next cell ' offset i to alternate column coloring i = i 1 Next repeat Msg Box "Total time was: " & (Timer - start Time) Application.Calculation speed probably isn't a large performance factor is most normal workbooks though, and it can have unexpected results, so use it sparingly—as needed: Application. A few won't be noticeable, but if the macro is complex enough, you might consider disabling events while the macro is running: Application. The commented lines show the Sheet and Table object references.
The sheet reference is necessary only if you want to run the macro outside of the sheet (Divisions, in this case).
In this case, it's more efficient to define a variable with the value in I4 and use the variable, as follows: Function Return Fee Fast() Dim int Fee As Integer int Fee = Range("I4").
Value Select Case int Fee Case 1 Return Fee = int Fee * 10 Case 2 Return Fee = int Fee * 20 Case 3 Return Fee = int Fee * 30 Case 4 Return Fee = int Fee * 40 Case 5 Return Fee = int Fee * 50 End Select Msg Box Return Fee, vb OKOnly End Function Please forgive the obnoxiously contrived examples, but the concept is the point, not the code's purpose.
To disable updates to the Status Bar, use the Display Status Bar property as follows: If your macro is analyzing a lot of data, consider setting the Calculation property to Manual while the macro is running. Calculation = xl Calculation Automatic Macros can trigger unnecessary event procedures. In short, you simply combine the two statements and delete the Select method and the Selection object.
That way, the workbook won't recalculate unless you force it to by pressing F9. Display Status Bar = False 'macro code Application. For instance, entering a value into a cell triggers the Worksheet_Change event. The optimized code is more efficient and less prone to runtime errors.
There seems generally to be some confusion about what VBA is and what its uses and limitations are.