<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Optimize Slow VBA Code. Speeding Up Slow Excel VBA Code

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Back to: Excel VBA. Got any Excel/VBA Questions? Excel Help

Speed up code and stop screen flickering:

Sub NoScreenRePainting()Application.ScreenUpdating=False	'Your code here.	Application.ScreenUpdating=TrueEnd Sub

Preventing calculation while executing code:

Sub NoCalculations()Application.Calculation = xlCalculationManual	'Your code here.	Application.Calculation = xlCalculationAutomaticEnd Sub

Speeding up code if you have Worksheet or Workbook Events. Also stops endless loops in Events

Sub StopAllEvents()Application.EnableEvents = False	'Your code here.	Application.EnableEvents = TrueEnd Sub

Use the With Statement when working with Objects.

Sub WithARange()With Range("A1")		.Value = 100		.Font.Bold = True		.Interior.ColorIndex = 6		.Copy Destination:=Range("B1")	End WithEnd Sub

Use VbNullString instead of = "" When needing to default a String variable back to it's default of "" use:

Sub EmptyText()Dim strWords As String	strWords = "Cats"	MsgBox strWords	strWords = vbNullString	MsgBox strWordsEnd Sub

Inserting a Relative formula into a range of cells: Faster than AutoFill or Copy.

Sub NoAutoFillOrCopy()Range("A1:A200").FormulaR1C1 = "=SUM(RC[1]:RC[5])"End Sub

Tip: To get a formula, type it in any cell then select the cell, go Tools>Macro>Record new macro and record a macro pushing F2 then Enter.

Avoid the use of Copy and Paste whenever Possible:

Sub NoCopyAndPaste()	'Instead of:Sheet1.Range("A1:A200").Copy	Sheet2.Range("B1").pasteSpecial	Application.CutCopyMode=False'Clear Clipboard'Use:'By-passes the Clipboard	Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")'Or, if only values are needed:Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value'Or, if only formulae are needed:Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula	'See also FormulaArray and FormulaR1C1 etc'Instead of:Sheet1.Range("A1:A200").Copy	Sheet1.Range("A1:A200").PasteSpecial xlPasteValues	Application.CutCopyMode=False'Clear Clipboard'Use:Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").ValueEnd Sub

Always declare your variables correctly!

To quickly view a variables definition:
Select the variable that you want the definition for.
Go to View>Definition (Shift+F2)

To return to your previous position:
Go to View>Last Postition (Ctrl+Shift+F2).

Release memory from Object variables:

Sub ReleaseObjectMemory()'Could be any variable of the Object type	Dim wSheet as Worksheet	'Set Object variable	Set wSheet = Sheet1	'Your code here.	'Release memory	Set wSheet = NothingEnd Sub

Don't get caught in the Loop.
Follow this link to see why Loops should (and usually can) be avoided.

Avoid If, Else whenever possible

More often than not people would use an If, Else Statement to test whether a condition is TRUE or FALSE. There is however a slightly faster (and less typing) method. The first example shows the common method, while the second shows a faster method. Of course in such a small example the difference is not noticeable.

Sub TrueOrFalseSlower()Dim bYesNo As Boolean	Dim i As Integer	If i = 5 Then		bYesNo = True	Else		bYesNo = False	End IfMsgBox bYesNoEnd Sub

Here's a better way!

Sub TrueOrFalseFaster()Dim bYesNo As Boolean	Dim i As Integer	bYesNo = (i = 5)	MsgBox bYesNoEnd Sub

Another common need is to toggle a variable between True and False depending on a condition. For example:

Sub ToggleTrueOrFalseSlower()Dim bYesNo As Boolean	If bYesNo = False Then		bYesNo = True	Else		bYesNo = False	End If	MsgBox bYesNoEnd Sub

Here's a much better way

Sub ToggleTrueOrFalseFaster()Dim bYesNo As Boolean	bYesNo = Not bYesNo	MsgBox bYesNoEnd Sub

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft