Tutorial by Examples

Disabling calculation of the worksheet can decrease running time of the macro significantly. Moreover, disabling events, screen updating and page breaks would be beneficial. Following Sub can be used in any macro for this purpose. Sub OptimizeVBA(isOn As Boolean) Application.Calculation = IIf(...

Different procedures can give out the same result, but they would use different processing time. In order to check out which one is faster, a code like this can be used: time1 = Timer For Each iCell In MyRange iCell = "text" Next iCell time2 = Timer For i = 1 To 30 MyRan...

Using with blocks can accelerate the process of running a macro. Instead writing a range, chart name, worksheet, etc. you can use with-blocks like below; With ActiveChart .Parent.Width = 400 .Parent.Height = 145 .Parent.Top = 77.5 + 165 * step - replacer * 15 .Parent.Left = 5 E...

Deleting rows is slow, specially when looping through cells and deleting rows, one by one A different approach is using an AutoFilter to hide the rows to be deleted Copy the visible range and Paste it into a new WorkSheet Remove the initial sheet entirely With this method, th...

The procedures bellow will temporarily disable all Excel features at WorkBook and WorkSheet level FastWB() is a toggle that accepts On or Off flags FastWS() accepts an Optional WorkSheet object, or none If the ws parameter is missing it will turn all features on and off for all WorkSh...

Using Line Numbers ... and documenting them in case of error ("The importance of seeing Erl") Detecting which line raises an error is a substantial part of any debugging and narrows the search for the cause. To document identified error lines with a short description completes a successf...