
Back to: Excel VBA . Got any Excel/VBA Questions? Excel Help
Sub NoScreenRePainting() Application.ScreenUpdating=False 'Your code here. Application.ScreenUpdating=True End Sub
Sub NoCalculations() Application.Calculation = xlCalculationManual 'Your code here. Application.Calculation = xlCalculationAutomatic End Sub
Sub StopAllEvents() Application.EnableEvents = False 'Your code here. Application.EnableEvents = True End Sub
Sub WithARange() With Range("A1") .Value = 100 .Font.Bold = True .Interior.ColorIndex = 6 .Copy Destination:=Range("B1") End With End Sub
Sub EmptyText() Dim strWords As String strWords = "Cats" MsgBox strWords strWords = vbNullString MsgBox strWords End Sub
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.
Sub NoCopyAndPaste() 'Instead of: Sheet1.Range("A1:A200").Copy Sheet2.Range("B1").pasteSpecial Application.CutCopyMode=False'Clear Clipboard 'Use: 'Bypasses 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").Value End 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).
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 = Nothing End Sub
Don't get caught in the Loop.
Follow this link to see why Loops should (and
usually can) be avoided.
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 If MsgBox bYesNo End Sub
Sub TrueOrFalseFaster() Dim bYesNo As Boolean Dim i As Integer bYesNo = (i = 5) MsgBox bYesNo End Sub
Sub ToggleTrueOrFalseSlower() Dim bYesNo As Boolean If bYesNo = False Then bYesNo = True Else bYesNo = False End If MsgBox bYesNo End Sub
Sub ToggleTrueOrFalseFaster() Dim bYesNo As Boolean bYesNo = Not bYesNo MsgBox bYesNo End 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 Addins Collection on all purchases totaling over $64.00. ALL purchases 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
Excel Trader Package Technical 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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates