Ten Simple Tricks to Speed up Your Excel VBA Code

I’m currently on a project to improve the performance of a particular workbook.

Part of this task involves speeding up a few of the macros in the workbook.

While I’m thinking about this stuff, I’d like to share a few simple tips for speeding up your Excel VBA code.

Some of you will know all of these and more. So feel free to comment and share any other performance tips you may have.

.

Halt Sheet Calculations

Each time a cell that affects any formula in your spreadsheet is changed or manipulated, Excel recalculates the entire worksheet. In worksheets that have a large amount of formulas, this behavior can drastically slow down your macros. If your workbook is formula intensive, you may not want Excel to trigger a recalculation every time a cell value is altered by your macro. You can use the Application.Calculation property to tell Excel to switch to manual calculation mode. When a workbook is in manual calculation mode, the workbook will not recalculate until you explicitly trigger a calculation. The idea is to place Excel into manual calculation mode, run your code, and then switch back to automatic calculation mode. Setting the calculation mode back to xlCalculationAutomatic will automatically trigger a recalculation of the worksheet.

Application.Calculation = xlCalculationManual

‘Place your macro code here

 

Application.Calculation = xlCalculationAutomatic

.

Disable Sheet Screen Updating

You may notice that when your macros run, your screen does a fair amount of flickering. This flickering is Excel trying to redraw the screen in order to show the current state the worksheet is in. Unfortunately, each time Excel redraws the screen, it takes up memory resources. In most cases, you don’t need Excel using up resources to redraw the screen each time your macro performs some action. In addition to setting the calculation mode to manual, you can use the Application.ScreenUpdating property to disable any screen updates until your macro has completed. This saves time and resources, allowing your macro to run a little faster. Once you macro code is done running, you can turn screen updating back on.

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

‘Place your macro code here

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

.

Turn Off Status Bar Updates

The status bar normally displays the progress of certain actions in Excel. For example, if you copy/paste a range, Excel will show the progress of that operation on the status bar. Often times, the action is performed so fast, you don’t see the status bar progress. However, if your macro is working with lots of data, the status bar will take up some resources. It’s important to note that turning off screen updating is separate from turning off the status bar display. That is to say, the status bar will continue to be updated even if you disable screen updating. You can use the Application.DisplayStatusBar property to temporarily disable any status bar updates, further improving the performance of your macro.

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

‘Place your macro code here

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

.

Tell Excel to Ignore Events

Let’s say you have a Worksheet_Change event implemented for Sheet1 of your workbook. Any time a cell or range is altered on Sheet1, the Worksheet_Change event will fire. So if you have a standard macro that manipulates several cells on Sheet1, each time a cell on that sheet is changed, your macro has to pause while the Worksheet_Change event runs. You can imagine how this behavior would slow down your macro. You can You can add another level of performance boosting by using EnableEvents property to tell Excel to ignore events while your macro runs. Simply set the EnableEvents property False before running your macro. Once you macro code is done running, you can set the EnableEvents property back to True.

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

‘Place your macro code here

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.EnableEvents = True

.

Although disabling events can indeed speed up your macros, you may actually need some events to trigger while your macro runs. Be sure to think about our specific scenario and determine what will happen if your worksheet or workbook events are turned off while your macro runs.

.

Hide Page Breaks

Another opportunity for a performance boost can be found in Page Breaks. Each time your macro modifies the number of rows, modifies the number of columns, or alters the Page Setup of a worksheet, Excel will be forced to take up time recalculating where the page breaks are shown on the sheet. You can avoid this by simply hiding the page breaks before starting your macro. Set the DisplayPageBreaks sheet property to False in order to hide page breaks. If you want to continue to show page breaks after your macro runs, you can set the DisplayPageBreaks sheet property back to True.

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

Activesheet.DisplayPageBreaks = False

‘Place your macro code here

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.EnableEvents = True

Activesheet.DisplayPageBreaks = True

.

Suspend Pivot Table Updates

If your macro manipulates pivot tables that contain large data sources, you may experience poor performance when doing things like dynamically adding or moving pivot fields. This is because each change you make to the structure of the pivot table requires Excel to recalculate all values in the pivot table for each pivot field your macro touches. You can improve the performance of your macro by suspending the recalculation of the pivot table until all your pivot field changes have been made. Simply set the PivotTable.ManualUpdate property to True to defer recalculation, run your macro code, and then set the PivotTable.ManualUpdate property back to False to trigger the recalculation.

ActiveSheet.PivotTables(“PivotTable1”).ManualUpdate=True

‘Place your macro code here

ActiveSheet.PivotTables(“PivotTable1”).ManualUpdate=False

.

Steer Clear of Copy and Paste

It’s important to remember that while the Macro Recorder saves time by writing VBA code for you, it doesn’t always write the most efficient code. A prime example of this is how the Macro Recorder captures any copy and paste action you perform while recording. If you were to copy cell A1 and paste it into cell B1 while recording a macro, the Macro Recorder would capture this:

Range(“A1”).Select

Selection.Copy

Range(“B1”).Select

ActiveSheet.Paste

While this code will indeed copy from cell A1 and paste into B1, it forces Excel to utilize the clipboard which adds a kind of middle man where there does not need to be one. You can give your macros a slight boost by cutting out the middle man and performing a direct copy from one cell to a destination cell. This alternate code uses the Destination argument to bypass the clipboard and copy the contents of cell A1 directly to cell B1.

Range(“A1?).Copy Destination:=Range(“B1?)

If you only need to copy values (not formatting or formulas), you can improve performance even more by avoiding the Copy method all together. Simply set the value of the destination cell to the same value found in the source cell. This method is about approximately 25 times faster than using the Copy method.

Range(“B1?).Value = Range(“A1?).Value

If you need to copy only the formulas from one cell to another, (not values or formatting), you can set the formula of the destination cell to the same formula contains in the source cell.

Range(“B1?).Formula = Range(“A1?).Formula

.

Use the With Statement

When recording macros, it’s not uncommon to manipulate the same object more than once. For example, your code may change the formatting of cell A1 so that it is underlined, italicized, and formatted bold. If you were to record a macro applying these formatting options to cell A1, you would get something like this.

Range(“A1”).Select

Selection.Font.Bold = True

Selection.Font.Italic = True

Selection.Font.Underline = xlUnderlineStyleSingle

Unfortunately, this code is not as efficient as it could be because it forces Excel to select and then change each property separately. You can save time and improve performance by using the With statement to perform several actions on a given object in one shot. The With statement utilized in this example tells Excel to apply all the formatting changes at one time. Getting into the habit of chunking actions into With statements will not only keep your macros running faster, but it will also help to more easily read your macro code.

With Range(“A1”).Font

.Bold = True

.Italic = True

.Underline = xlUnderlineStyleSingle

End With

.

Don’t Explicitly Select Objects

The Macro Recorder is quite fond of using the Select method to explicitly select objects before taking actions on them. If you were to record a macro while entering the value 1000 in cell A1 for multiple sheets, you would end up with code that looks similar to this.

Sheets(“Sheet1”).Select

Range(“A1”).Select

ActiveCell.FormulaR1C1 = “1000”

Sheets(“Sheet2”).Select

Range(“A1”).Select

ActiveCell.FormulaR1C1 = “1000”

Sheets(“Sheet3”).Select

Range(“A1”).Select

ActiveCell.FormulaR1C1 = “1000”

While this code will run fine, it’s not at all efficient. It forces Excel to take the time to explicitly select each object that is being manipulated. There is generally no need to select objects before working with them. In fact, you can dramatically improve macro performance by not using the Select method. Make it a habit to remove the Select method from any generated code. In this case, the optimized code would look like this. Note that the nothing is being selected. The code simply uses the object hierarchy to apply the needed actions.

Sheets(“Sheet1”).Range(“A1”).FormulaR1C1 = “1000”

Sheets(“Sheet2”).Range(“A1”).FormulaR1C1 = “1000”

Sheets(“Sheet3”).Range(“A1”).FormulaR1C1 = “1000”

.

Avoid Excessive Trips to the Worksheet

Another way to speed up your macros is to limit the amount of times you reference worksheet data in your code. It is always less efficient to grab data from the worksheet than from memory. That is to say, your macros will run much faster if they do not have to repeatedly interact with the worksheet. For instance, this simple code forces VBA to continuously return to Sheets(“Sheet1”).Range(“A1”) to get the number needed for the comparison being performed in the If statement.

For ReportMonth = 1 To 12

If Range(“A1”).Value = ReportMonth Then

MsgBox 1000000 / ReportMonth

End If

Next ReportMonth

 

A much more efficient way is to save the value in Sheets(“Sheet1”).Range(“A1”) into a variable. This way, the code references the variable instead of the worksheet.

Dim MyMonth as Integer

MyMonth = Range(“A1”).Value

For ReportMonth = 1 To 12

If MyMonth = ReportMonth Then

MsgBox 1000000 / ReportMonth

End If

Next ReportMonth

.

Hope that helps. Again, feel free to add your own tips to the comments.

33 thoughts on “Ten Simple Tricks to Speed up Your Excel VBA Code

  1. Seb

    Thanks for the tips.

    I usually refrain from potentially changing some user settings, as might be the case with the calculation mode and page break setting.

    I would store the setting in a variable and reset it to the stored setting at the end.

    I often encounter huge workbooks deliberately in manual calculation or some in “Automatic except table” mode and changing it to automatic at the end might become really problematic.

  2. AlexJ

    “Avoid Excessive Trips to the Worksheet”. I have found that approach useful for a long time.
    – Not only read-once, but use Array reads to import multiple data values in a single read operation instead of multiple single cell reads.
    – Likewise, Array writes instead of multiple single cell writes.
    – And since a write operation is twice as slow as a read, use variables in the code to hold intermediate data instead of using worksheet ranges.

  3. Bigger Don

    Thanks for this. As a self-developed VBA programmer I appreciate it when things I assume are confirmed by those who know more, e.g. using arrays is more efficient than searching worksheet ranges, as well as the obvious, such as there is no need to .Select prior to assigning a value to a cell..

    Then there are the things I didn’t know, like the effect of Page Breaks and the difference between copy/paste versus .Copy Destination:=.

    I am in full agreement with Seb’s suggestion to capture the current state of settings, making the change for efficiency, then returning them to the previous state at the end.

    I’ll add one thing to this. While testing the code I often find a need to interrupt the code in the middle of a routine. In those cases, then settings are in the efficient mode. This causes a problem when I am using an Event to trigger the routine. Therefore I have a Sub that I can run after the execution is shut down after an error that sets them properly
    Sub ResetEnv()
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    End Sub

  4. Gordon

    I have a routine called SpeedUp that I use to set this

    Public Sub SpeedUp(Optional Switch As Boolean = True, Optional SU As Boolean = True, Optional Calc As Boolean = True, Optional Evnts As Boolean = True)
    With Application
    .ScreenUpdating = Switch Xor SU
    If Switch Xor Calc Then
    .Calculation = xlCalculationAutomatic
    Else
    .Calculation = xlCalculationManual
    End If
    .EnableEvents = Switch Xor Evnts
    End With
    End Sub

    Simply call SpeedUp where you want thing to whizz along and SpeedUp False when you’re done. It can be applied granularly too.

    Be warned about switching these on and off too often (in a loop, for example) as they can take a bit of time, particularly calculation.

  5. AlexJ

    @Sam:
    Why “Application.PrintCommunication = False “. What does this do?? I haven’t heard of it before

  6. Jeff Weir

    Hi Mike. This comment comes via Rick Rothstein, who’s having trouble leaving comments on your blog for some reason.

    Take it away, Rick:

    I think you need to be careful with disabling events within a macro. Let’s say that you have a Change event that counts activity on a row.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Cells(Target.Row, “A”).Value = Cells(Target.Row, “A”).Value + 1
    Application.EnableEvents = True
    End Sub

    If you disable events for the sheet while your macro runs, the cell counter won’t count the activity your macro performs and, hence, it the worksheet’s displayed counts will be incorrect. This is a simple example to demonstrate the problem, but the effect of disabling the events could be much worse. What if your macro was making a calculation on a range where a Change or Calculate event was doing some kind of calculation and change to the worksheet that you macro expected to be done and which it used within its own calculations later on. Nothing would update from the event code, the macro would execute to conclusion and the user would never know the worksheet was updated with incorrect information. I think you need intimate knowledge of what all relevant events are doing on the worksheet or worksheets that your macro will touch and intimate knowledge of whether your macro is dependent on them… and still there could be a future problem if the events or macro get modified six months from now or, say, a Change event that did not exist before get added to the project later on. Personally, I think restricting turning events off to only within other events (where their code could “run wild” as a resulted of nested calls back to itself) is a better policy than doing so in macros.

  7. AlexJ

    How about something related to using VBA to make slicer selections? It’s instant when you do it manually, but if you record and repeat, it is much slower. pivotTable.ManualUpdate = True ??

  8. Patrick

    Great !! and i use this technique since several years.

    The next step is: how manipulate data in a virtual sheet (in mémoriel not in the sheet) and place the result in the sheet 🙂 ?

    Thanks

    Patrick

  9. Dave Marriott

    Under Steer Clear of Copy & Paste what do the question marks mean? I’ve never seen this form of referring to ranges before, ie:

    Range(“A1?).Copy Destination:=Range(“B1?)

  10. sam

    With Range(“A1”).Font

    .Bold = True

    .Italic = True

    .Underline = xlUnderlineStyleSingle

    End With

    Can be reduced to

    With Range(“A1”).Font

    .FontStyle = “Bold Italic”
    .Underline = xlUnderlineStyleSingle

    End With

    Which can further be reduced to 1 line

    Range(“A1”).Style = “BIU”

    Where BIU is a style defined in the Workbook or an Addin (But if stored in an Add-in it has to be merged with the workbook first – which means there will be one more line of code)

  11. RandyG

    I found your tips for speeding VBA extremely helpful! I have a large spreadsheet I use daily that copies and filters data, refreshes pivot tables, charts, vlookups, IF calculations, etc. which takes about 10-12 seconds to execute. As a test I tried your suggestion to halt sheet calculations and I was amazed as the processing time dropped to just under 5 seconds! What an impact that one suggestion had. Thank you! Great article and well written!

  12. Steve Martin

    In my macro I copy several formulas from my Library worksheet to my Input worksheet, but alas the formula shown above is not working, all my button does is absolutely nothing just flickers, and no formula gets pasted.

    My code is:

    Private Sub StartHorseName1_Click()
    Dim wsLib, wsInput As Worksheet

    Set wsLib = Sheet2 ‘ Library Sheet
    Set wsInput = Sheet1 ‘Input Sheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ‘Copying formula for the Letter A
    iresultAA = Application.WorksheetFunction.CountIf(wsInput.Range(“F2:F500”), “A”)
    If iresultAA > 0 Then
    iresultC = 2 ‘ starting row
    For X = 1 To iresultAA
    wsLib.Range(“K1:L1”).Formula=wsInput.Range(“G” & iresultC).Formula
    iresultC = iresultC + 1
    Next X
    End If
    End Sub

    Why would it not work in this case

  13. Carlo

    For the “Steer Clear of Copy and Paste”, is it possible to use variables for the cells/range? My current formula is this but it takes ages to process my file which might have 50,000 rows:

    r = 2

    Do Until IsEmpty(Cells(r, 3))
    c = 29

    Do Until IsEmpty(Cells(r, c))

    ‘This duplicates the row
    Cells(r, 1).EntireRow.copy
    Cells(r, 1).Select
    Selection.Insert Shift:=xlDown

    ‘This copies an identifier to a column
    Cells(r, c).Select
    Application.CutCopyMode = False
    Selection.copy
    Cells(r, 28).Select
    ActiveSheet.Paste

    c = c + 1
    r = r + 1
    Loop

    r = r + 1
    Loop

    Any help you can give will be greatly appreciated!

  14. Dan Judd

    Selecting Objects Kludge: An even more efficient way to populate several sheets at once is by selecting the several sheets, activating one of those sheets, and then populating a range. Live on the dark side of ‘”Select”! That population of the one range will also populate the others. So from the area “Don’t Explicitly Select Objects” an even faster code is (I did not test but I have used similar coding structures and they are easily 10%-20% faster than looping):

    Sheets(Array(“Sheet1”, “Sheet2”, “Sheet3”)).Select
    Sheets(“Sheet1”).Activate
    Range(“A1”).Select
    With Selection
    .FormulaR1C1 = “1000”
    End With

    Then, unless you continue with other groupings in those sheets you must un-select and continue the coding. To un-select, I often use Application.Goto because I just hate to Select anything! I would cloud the sin of using the Select kludge by the darkness of Application.ScreenUpdating = False. That will speed-up the code too.

  15. William

    I want to make this code faster, the only scenario that works is to comment out some of the wait code. Anything else causes the macro to go off the rails when pulling data from another source and bug out. Can someone give it a look and advise? Thanks

    ‘Contact William Skillman x43817

    Public Sub GetActiveClaims()

    ‘ Session.Connect nInstance, RSDM, apishortname, Flag

    Dim sLastClaim As String, sClaimCheck As String, sFInit As String, sCode As String
    Dim iRow As Integer, r As Integer, iLn As Integer, iThisRow As Integer, iPage As Integer, nPages As Integer

    Dim sPayment As String, sPatName As String, sPatID As String, sDOSStart As String, sDOSEnd As String, sProv As String, sTotalCharge As String, sPageCheck As String
    Dim sPmtTarget As String, sStatus As String, sCCN As String, sSCCF As String, sMonth As String, sDay As String, sYear As String, sTOS As String, sDX As String
    Dim sLineTOS As String, sLineDX As String, sSubID As String
    Dim sID_CARD As String, sThisPatID As String, sPatientName As String
    Dim sSearchEndDate As String
    Dim sRMIH_Page_Test As String

    Sheets(“ACTIVE”).Select
    Range(“A11:X65536”).Delete xlShiftUp ‘these 2 lines of code clear the excel spreadsheet out in between uses.
    Rows(“11:65536”).RowHeight = 19.5 ‘so the previous claims delete before you run another sob.
    Range(“B1”).Select

    sAccDate = Trim$(Range(“B1”).Value)
    sSubID = HandleSponID(Range(“B2”).Value)
    sFInit = Trim$(Range(“B3”).Value)
    sPatID = Format(Trim$(Range(“B4”).Value), “00#”)
    sSearchEndDate = Now

    With Session
    PrepSession

    ClearScreen
    If RMPI_Retrieval(sSubID, sPatID) = False Then
    Exit Sub
    End If

    ‘RMIS – CLAIM SEARCH
    SendText “RMIS”
    HitEnter

    SendText sSubID, 4, 46 ‘SUBSCRIBER ID: (can take up to 17 bytes!?!)
    SendText sPatID, 4, 78 ‘PATIENT ID:
    SendText Format(sAccDate, “MMDDYYYY”), 13, 24 ‘SERVICE DATE: (start)
    SendText Format(sSearchEndDate, “MMDDYYYY”), 13, 62 ‘thru SERVICE DATE: (end)

    HitEnter

    ‘==========================================================================================================================
    ‘—————–
    ‘ SEARCH RESULTS
    ‘—————–

    ‘ Case 0
    If GetText(22, 7, 8) = “NO CLAIM” Then
    ‘We’re done; no claims found
    Application.ScreenUpdating = True
    MsgBox “Done!”
    Exit Sub
    End If

    iRow = 11

    ‘ Case 1 record returned
    If .FindText(“RMIH – CLAIM DISPLAY”, 1, 1) Then
    If sCCN = “” Then sCCN = GetText(4, 12, 13)
    ‘===========================================================================================================================
    If CDate(GetText(6, 39, 10)) >= CDate(sAccDate) Then ‘Check DOS TO date is on or after the accident date
    GetClaimData iRow, “ACTIVE”
    End If
    ‘===========================================================================================================================
    Exit Sub
    End If

    ‘ Case > 1 reecords returned


    ‘Do you always get PARTIAL HISTORY REVIEW
    ‘NOT ALL CLAIMS DISPLAYED

    ‘nPages = CInt(GetText(2, 76, 4))
    iPage = 1
    ‘For iPage = 1 To nPages
    Do
    r = 10
    sthisccn = GetText((r), 2, 13)

    While sthisccn “”

    ‘If GetText(r + 1, 33, 1) = “.” Then ‘sPayment = GetTrimText(r + 1, 17, 20))
    sThisPayment = GetTrimText(r + 1, 24, 12)
    If IsNumeric(sThisPayment) Then
    ‘Go to CLAIM DISPLAY for the specific claim selected
    PutCursor r, 6
    HitEnter

    ‘===========================================================================================================================
    If GetText(14, 74, 3) = sPatID Then
    Sheets(“PRINTABLE”).Cells(4, 4).Value = FixCase(Trim(.GetDisplayText(14, 16, 50)))
    GetClaimData iRow, “ACTIVE”
    End If
    ‘===========================================================================================================================

    iRow = iRow + 1
    End If

    r = r + 2 ‘Next row on RMIS results screen
    sthisccn = GetTrimText((r), 22, 13)
    ‘Loop until we’ve *VIEWED* every claim on this screen
    Wend ‘Loop Until sThisCCN “” ‘Claim number showing on row r
    sRMIH_Page_Test = GetText(10, 2, 13)
    HitPF8
    If GetText(10, 2, 13) = sRMIH_Page_Test Then
    ‘We’re done (final screen shows ALL CLAIMS DISPLAYED, PF7 FOR PREVIOUS CLAIMS)
    Exit Do
    End If
    iPage = iPage + 1
    Loop
    ‘Next iPage

    End With

    End Sub

    Function RMPI_Retrieval(sSubID As String, sPatID As String) As Boolean
    Dim r As Long, iPage As Integer, nPages As Integer
    Dim iThisRow As Long
    Dim sID_CARD As String, sThisPatID As String, sPatientName As String
    RMPI_Retrieval = True ‘Let’s live a little dangerously

    With Session

    SendText “RMPI” & sSubID
    HitEnter

    ‘Should be showing active RMPI record now
    If Not .FindText(“- A M M S – PATIENT ID”, 1, 1) Then
    If .FindText(“XREF – SUBSCRIBER NUMBER CROSS REFERENCE”, 1, 1) Then
    If .FindText(sSubID & ” 16″, 1, 1) Then
    iThisRow = .FoundTextRow
    SendText “G”, (iThisRow), 2
    HitPF3
    ‘Should be showing active RMPI record now
    End If

    If Not .FindText(“- A M M S – PATIENT ID”, 1, 1) Then
    MsgBox “Cannot get to RMPI to get the ID CARD and Patient Name. Contact Elise Heise via Melissa Connors”, vbOKOnly, “SOB Powersheet”
    RMPI_Retrieval = False
    Exit Function
    End If
    End If
    End If

    sID_CARD = GetTrimText(4, 50, 3) & GetTrimText(4, 54, 12)
    Sheets(“ACTIVE”).Range(“B6”).Value = sID_CARD

    nPages = CInt(GetText(1, 76, 4))

    For iPage = 1 To nPages

    r = 7

    sThisPatID = GetTrimText((r), 4, 3)

    Do
    If sThisPatID = sPatID Then
    sPatientName = FixCase(GetTrimText((r), 8, 30))
    Sheets(“PRINTABLE”).Range(“D4”).Value = sPatientName
    Exit For ‘We’re done once we find the patient on RMPI
    End If

    r = r + 2
    sThisPatID = GetTrimText(r, 4, 3)

    Loop Until sThisPatID “” ‘Pat ID showing on row r

    HitPF8

    Next iPage
    ClearScreen
    End With
    End Function

    ‘ ‘sClaimCheck = GetText(10, 2, 13)
    ‘ ‘For r = 10 To 20 Step 2
    ‘ ‘If GetText(r, 2, 11) sLastClaim Then
    ‘ ‘sLastClaim = GetText(r, 2, 11)
    ‘ If GetText(r + 1, 33, 1) = “.” Then
    ‘ ‘sPayment = GetTrimText(r + 1, 17, 20))
    ‘ ‘Go to RMIH CLAIM DISPLAY for the specific claim selected

    ‘ PutCursor r, 6
    ‘ HitEnter

    ‘ ‘===========================================================================================================================
    ‘ If GetText(14, 75, 3) = sPatID Then
    ‘ GetClaimData iRow, sSheetName
    ‘ End If
    ‘ ‘===========================================================================================================================

    ‘ iRow = iRow + 1
    ‘ End If

    ‘ r = r + 2 ‘Next row on RMIS results screen
    ‘ sThisCCN = GetTrimText(r, 22, 13)
    ‘ ‘Loop until we’ve *VIEWED* every claim on this screen
    ‘ Loop Until sThisCCN “” ‘Claim number showing on row r

  16. Pingback: Possible to speed up import process? Currently 500K rows of data takes 4 min and 45 sec « news-rss feed form stackoverflow

  17. Pingback: how to speed up macro

  18. Pingback: Speeding up Excel VBA | IHACtips

  19. Amin

    My solution starting and stopping everything while going through multiple subs:

    I hope this is not too confusing. Some expert here might be able to simplify my point.

    – If you stop ScreenUpdating,EnableEvents,DisplayStatusBar,DisplayPageBreaks in the beginning of a macro,

    – but you trigger multiple other macros through it and
    —– the triggered macro can also be triggered independently by themselves and
    ——— they also need to stop events at beginning and start back at end, then stopping and starting everything in each macro will not work properly.
    – Because the 1st triggered macro will restart everything at the end of it.

    I have about 15-20 macros that they trigger through each other and this is what I figured out the best way till now.

    The following will only start everything back at the macro that stopped them.

    Public Everything_IsStopped As Boolean
    Public ProcThat_StoppedEverthing As String

    ‘—————————————————————————–
    Sub Macro1()
    Dim ThisProcName As String
    ThisProcName = “Macro1”
    Stop_Everything ThisProcName
    Macro2
    Restart_Everything ThisProcName
    End Sub

    ‘—————————————————————————–
    Sub Macro2()
    Dim ThisProcName As String
    ThisProcName = “Macro2”
    Stop_Everything ThisProcName
    Macro3
    Restart_Everything ThisProcName
    End Sub

    ‘—————————————————————————–
    Sub Macro3()
    Dim ThisProcName As String
    ThisProcName = “Macro3”
    Stop_Everything ThisProcName
    CODE
    Restart_Everything ThisProcName
    End Sub

    ‘—————————————————————————–
    Sub Stop_Everything(ProcName As String)
    If Not Everything_IsStopped Then
    If Application.ScreenUpdating = True Then Application.ScreenUpdating = False
    If Application.EnableEvents = True Then Application.EnableEvents = False
    If Application.DisplayStatusBar = True Then Application.DisplayStatusBar = False

    If ProcThat_StoppedEverthing = “” Then ProcThat_StoppedEverthing = ProcName
    Everything_IsStopped = True
    End If
    End Sub

    ‘—————————————————————————–
    Sub Restart_Everything(ProcName As String)
    If ProcName = ProcThat_StoppedEverthing Then
    If Application.ScreenUpdating = False Then Application.ScreenUpdating = True
    If Application.EnableEvents = False Then Application.EnableEvents = True
    If Application.DisplayStatusBar = False Then Application.DisplayStatusBar = True

    ProcThat_StoppedEverthing = “”
    Everything_IsStopped = False
    End If
    End Sub

  20. Daniel

    Hello:

    I am trying to auto populate real time data from Sheet 1 to Sheet 2 without copy and paste. Basically link certain columns (A,B,D,E,,F,G,K,L,M,N) from Sheet 1 to Columns (A,B,C,D,E,F,G,H,I,J,K,L) in sheet 2 without manually transferring data over manually. I can do this with excel formula but would like to know how to do this via MACROS.

    I input my code and get previous data to transfer correctly, however when I enter new data into sheet1 it does not transfer to sheet 2. I am new to Macros and Kind of self teaching.

    Will someone be willing to help? Code I created listed below. Thank you in advance.

    Sub copyrange()

    Dim lastrow As Long, erow As Long
    ActiveSheet.Calculate
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    Sheet2.Select
    Sheet2.Cells.ClearContents
    Range(“a1”).Value = “Received Date”
    Range(“B1”).Value = “CID”
    Range(“c1”).Value = “First”
    Range(“d1”).Value = “Last”
    Range(“e1”).Value = “RD”
    Range(“f1”).Value = “Case Type”
    Range(“g1”).Value = “Tested”
    Range(“h1”).Value = “Time”
    Range(“i1”).Value = “Limited”
    Range(“j1”).Value = “FICA”
    Range(“k1”).Value = “Release”
    Range(“L1”).Value = “QP”

    Sheets(“sheet1”).Range(“a2:a10000”).Copy Sheets(“sheet2”).Range(“a2”)
    Sheets(“sheet1”).Range(“b2:b10000”).Copy Sheets(“sheet2”).Range(“b2”)
    Sheets(“sheet1”).Range(“d2:d10000”).Copy Sheets(“sheet2”).Range(“c2”)
    Sheets(“sheet1”).Range(“e2:e10000”).Copy Sheets(“sheet2”).Range(“d2”)
    Sheets(“sheet1”).Range(“f2:f10000”).Copy Sheets(“sheet2”).Range(“e2”)
    Sheets(“sheet1”).Range(“g2:g10000”).Copy Sheets(“sheet2”).Range(“f2”)
    Sheets(“sheet1”).Range(“k2:k10000”).Copy Sheets(“sheet2”).Range(“g2”)
    Sheets(“sheet1”).Range(“l2:l10000”).Copy Sheets(“sheet2”).Range(“h2”)
    Sheets(“sheet1”).Range(“m2:m10000”).Copy Sheets(“sheet2”).Range(“i2”)
    Sheets(“sheet1”).Range(“n2:n10000”).Copy Sheets(“sheet2”).Range(“j2”)
    Sheets(“sheet1”).Range(“o2:o10000”).Copy Sheets(“sheet2”).Range(“k2”)
    Sheets(“sheet1”).Range(“p2:p10000”).Copy Sheets(“sheet2”).Range(“l2”)

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True

    End Sub

  21. Muhammad Shafay Amjad

    Well this takes like 14 seconds to execute, and still same its been like 13 now.
    Private Sub PICKASSOCIATE_Click()
    Worksheets(“Settings”).Activate ‘ Activates the Setting Sheet
    Row_Settings = ActiveSheet.UsedRange.Rows.Count ‘–Row used in the worksheet 3
    Column_Settings = ActiveSheet.UsedRange.Columns.Count ‘ — Column used in the worksheet3
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    ReDim As_Pos_List(Row_Settings) As Variant ‘<– Array holding the associates name
    ReDim Po_Pos_List(Row_Settings) As Variant '<– This is the Array Holding the positions.
    For i = 4 To Row_Settings
    If Cells(i, 1) = "" And Cells(i, 2) = "" Then
    As_Pos_List(i) = 0 'When None Found put 0 instead
    Po_Pos_List(i) = 0 ' Whemn None Found Put a 0 instead
    ElseIf Cells(i, 1) = "" And Cells(i, 2) “” Then
    As_Pos_List(i) = 0 ‘When None Found put 0 instead
    Po_Pos_List(i) = Cells(i, 2)
    ElseIf Cells(i, 2) = “” And Cells(i, 1) “” Then
    As_Pos_List(i) = Cells(i, 1) ‘ Getting all the Associate in the list As_Pos_List
    Po_Pos_List(i) = 0 ‘ Getting all the Position in the lise Po_Pos_List
    Else
    As_Pos_List(i) = Cells(i, 1) ‘ Getting all the Associate in the list As_Pos_List
    Po_Pos_List(i) = Cells(i, 2) ‘ Getting all the Position in the lise Po_Pos_List
    End If
    Next
    ‘To Check the Debugging output of the list use this 😀
    ‘ For i = 4 To Row_Settings
    ‘ If As_Pos_List(i) 0 Then
    ‘ MsgBox (As_Pos_List(i))
    ‘ MsgBox (Po_Pos_List(i))
    ‘ End If
    ‘ Next
    ‘Ending the Debugginh sections of the output List.
    ReDim PTV_position(Row_Settings) As Variant ‘ Holding the Position which is passed throug the Main Tbale Created un the Associates Table
    ReDim PTV_target_name(Row_Settings) As Variant ‘ Holding the Target name for the POSITION TARGET VALUE TABLE
    ReDim PTV_Target_Value(Row_Settings) As Variant ‘ Holding the Position Targetal Value of the Table
    For i = 4 To Row_Settings
    If Worksheets(“settings”).Cells(i, 6) = “” Then
    PTV_position(i) = “NA”
    Else
    PTV_position(i) = Worksheets(“Settings”).Cells(i, 6) ‘Storing the value of the Position in the Position Targetal Value Table
    End If
    If Worksheets(“settings”).Cells(i, 7) = “” Then
    PTV_target_name(i) = “NA”
    Else
    PTV_target_name(i) = Worksheets(“Settings”).Cells(i, 7) ‘Holding lthe TARGETS NAME SUCH THAT IT WILL BE EASY TO HAVE THE VALUE FOR THE PARTICULAR TARGET.
    End If
    If Worksheets(“Settings”).Cells(i, 8) = “” Then
    PTV_Target_Value(i) = “NA”
    Else
    PTV_Target_Value(i) = Worksheets(“Settings”).Cells(i, 8) ‘Storing the value for the posotion targetal value table
    End If
    Next
    ‘MsgBox (PTV_position(6)) ‘
    ‘MsgBox (PTV_target_name(6))
    ‘MsgBox (PTV_Target_Value(6))
    ‘ Checking the Valid Asssociates and the position in the ASSOCIATE ENTRY SHEET
    Worksheets(“TargetWEIGHTEAGE”).Activate
    Dim TW_Rows As Integer
    Dim TW_Columns As Integer
    TW_Rows = ActiveSheet.UsedRange.Rows.Count ‘ Storing the Rows size for the worksheet AES
    TW_Columns = ActiveSheet.UsedRange.Columns.Count ‘ Storing the column size for the worksheet For the Columns.
    ReDim TW_Positions(TW_Rows) As Variant ‘ Array to hold the Position Names from the Worksheet of the Taret Weightaege
    For i = 2 To TW_Rows
    If CStr(Worksheets(“TargetWEIGHTEAGE”).Cells(i, 2)) = “” Then ‘ picking the values for the positions from the Target Weight
    ‘TW_Positions(i) = “N/A”
    ‘MsgBox (“this is null”)
    Else
    TW_Positions(i) = Worksheets(“TargetWEIGHTEAGE”).Cells(i, 2)
    End If
    Next
    ‘MsgBox (CStr(TW_Positions(2)))
    ‘ MsgBox (CStr(PTV_Position(4)))
    ‘ Applying all the null Value so that the values are not counted .
    For i = 4 To Row_Settings
    For j = 2 To TW_Rows
    If CStr(TW_Positions(j)) = CStr(PTV_position(i)) Then
    ‘MsgBox (“j is : ” & j)
    ‘MsgBox (“i is :” & i)
    Dim Picked_Target_name As Variant
    Dim Picked_Target_Value As Variant
    Picked_Target_name = PTV_target_name(i) ‘ Picking the name of the target from the position we matched there
    Picked_Target_Value = PTV_Target_Value(i) ‘ Picking the Target Value for the respectable position
    ‘MsgBox (Picked_Target_name)
    ‘ MsgBox (Picked_Target_valuve)
    ‘Now below Apply the loop to find the name and then insert the value at the specified position
    For Col_in_TwSheet = 3 To TW_Columns
    If CStr(Picked_Target_name) = CStr(Worksheets(“TargetWEIGHTEAGE”).Cells(1, Col_in_TwSheet)) Then
    ‘ MsgBox (Worksheets(“TargetWeighteage”).Cells(1, Col_in_TwSheet))
    Worksheets(“TargetWEIGHTEAGE”).Cells(j, Col_in_TwSheet) = Picked_Target_Value
    End If
    Next
    ‘ MsgBox (“One time done “)
    ‘– Else
    ‘DO Nothing here
    End If
    Next
    Next
    ‘———————————————————————- HERE YOU NEED TO START THE CALCULATION—————————————————————–‘
    Worksheets(“Obtained_Total_W”).Activate
    Dim OTW_Rows As Integer ‘Decelerating the both variable
    Dim OTW_Columns As Integer ‘ Declearting the variable for the column
    OTW_Rows = ActiveSheet.UsedRange.Rows.Count ‘applying the formula for the calculation of the rows
    OTW_Columns = ActiveSheet.UsedRange.Rows.Count ‘ applying the formula for the calcuation of the columns.
    For OTW_ROW = 2 To OTW_Rows
    For otw_col = 3 To OTW_Columns
    If Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, otw_col) = “” Then
    Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, otw_col) = “N/A”
    End If
    Next
    Next
    Dim obt_Counter As Integer
    obt_Counter = 1
    Dim tg_Counter As Integer
    tg_Counter = 2
    Dim Final_Cal_Columns As Integer
    Final_Cal_Columns = 3
    ‘ReDim Secondary_Array_Calculated(cm_Rows, cm_Columns) As Variant ‘ this is the secondary array which need to be calculated
    ‘ReDim Secondary_Array_TV(cm_Rows, cm_Columns) As Variant ‘ this is the target value which needs to be calculated
    ‘ Looping through the each value of the column and then moving the column
    For OTW_ROW = 2 To OTW_Rows
    For otw_col = 3 To OTW_Columns
    obt_Counter = obt_Counter + 2 ‘ odd –> obtained counter
    tg_Counter = tg_Counter + 2 ‘ EVEN — .target counter
    ‘ Below is the Inner row Counter for the Calculation :p
    ‘ MsgBox (Worksheets(“Obtained_Total_W”).Cells(otw_row, obt_Counter))
    ‘ MsgBox (Worksheets(“Obtained_Total_W”).Cells(otw_row, tg_Counter))
    If (CStr(Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, obt_Counter)) = “” And CStr(Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, tg_Counter)) = “”) Or (CStr(Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, obt_Counter)) = “N/A” And CStr(Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, tg_Counter)) = “N/A”) Then
    ‘Do nothing
    Else
    If CStr(Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, tg_Counter)) = “” Or CStr(Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, tg_Counter)) = “N/A” Then ‘ IF there is not target assigned then the obtained value will be counted and
    Worksheets(“Calculated_Marks”).Cells(OTW_ROW, otw_col) = (Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, obt_Counter)) * 0.1
    Else
    Worksheets(“Calculated_Marks”).Cells(OTW_ROW, otw_col) = (Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, obt_Counter)) / (Worksheets(“Obtained_Total_W”).Cells(OTW_ROW, tg_Counter))
    ‘ Secondary_Array_Calculated(otw_row, otw_col) = CDbl(Worksheets(“Obtained_Total_W”).Cells(otw_row, obt_Counter)) / CDbl(Worksheets(“Obtained_Total_W”).Cells(otw_row, tg_Counter))
    ‘ Secondary_Array_TV(otw_row, otw_col) = CDbl(Worksheets(“Obtained_Total_W”).Cells(otw_row, tg_Counter))

    End If
    End If
    Next
    obt_Counter = 1 ‘ incrementing the counter and then intializing them with the same value as the before varaiable
    tg_Counter = 2 ‘ incrementing the counter and then intializing them with the same value as the before variable.
    Next
    ” Here we need to implemet the code so that the given weigteafe can be calculated so t
    ‘———————————— Putting all the values to be N/A ————
    ‘For Tw_row = 2 To TW_Rows
    ‘ For TW_Col = 3 To TW_Columns
    ‘ If Worksheets(“TargetWEIGHTEAGE”).Cells(Tw_row, TW_Col) = “” Then
    ” Worksheets(“TargetWEIGHTEAGE”).Cells(Tw_row, TW_Col) = “N/A”
    ‘ End If
    ‘ Next

    ‘Next
    ‘———————————————————————————————————————————————
    ‘For Tw_row = 2 To TW_Rows
    ‘ For TW_Col = 3 To TW_Columns
    ‘ If Worksheets(“TargetWEIGHTEAGE”).Cells(Tw_row, TW_Col) = “” Then
    ‘ Worksheets(“TargetWEIGHTEAGE”).Cells(Tw_row, TW_Col) = “N/A”
    ‘ End If
    ‘ Next
    ‘Next
    Worksheets(“Calculated_Marks”).Activate ‘ Activated Calculated_Marks Sheet
    cm_Rows = ActiveSheet.UsedRange.Rows.Count ‘ Counting the rows count
    cm_Columns = ActiveSheet.UsedRange.Columns.Count ‘ Counting the column count , keep in mind that the CM is the worksheet name

    For cm_Row = 2 To cm_Rows
    For cm_column = 3 To cm_Columns
    If CStr(Worksheets(“Calculated_Marks”).Cells(cm_Row, cm_column)) = “” Then
    Worksheets(“Calculated_Marks”).Cells(cm_Row, cm_column) = CStr(“N/A”)
    End If
    Next
    Next
    ‘——————————————————————————————————————————————————————
    ‘ Looping through the worksheet calculated marks so the the result can be caluculated on the basis of the position .
    For Row = 2 To cm_Rows
    Dim Inner_position As Variant
    If CStr(Worksheets(“Calculated_Marks”).Cells(Row, 2)) = “” Or CStr(Worksheets(“Calculated_Marks”).Cells(Row, 2)) = “N/A” Then
    Else
    Inner_position = CStr(Worksheets(“Calculated_Marks”).Cells(Row, 2))
    ‘ Below Looping through the position of the Target_weighted and then matching the Position
    For TW_inner_row = 2 To cm_Rows
    If CStr(Worksheets(“TargetWEIGHTEAGE”).Cells(TW_inner_row, 2)) = Inner_position Then
    For Tw_inner_Columns = 3 To cm_Columns
    If CStr(Worksheets(“TargetWEIGHTEAGE”).Cells(TW_inner_row, Tw_inner_Columns)) = “” Or CStr(Worksheets(“TargetWEIGHTEAGE”).Cells(TW_inner_row, Tw_inner_Columns)) = “N/A” Then

    Else
    If CStr(Worksheets(“Calculated_Marks”).Cells(TW_inner_row, Tw_inner_Columns)) “” And CStr(Worksheets(“Calculated_Marks”).Cells(TW_inner_row, Tw_inner_Columns)) “N/A” Then
    ‘ Here you need to place the values in the PLace of the Target And the Calculated Value
    ‘MsgBox (Worksheets(“TargetWEIGHTEAGE”).Cells(TW_inner_row, Tw_inner_columns)) ‘ this holds the Target value which is obtained from the
    ‘ MsgBox (“TARGET IS ” & (Worksheets(“TargetWEIGHTEAGE”).Cells(TW_inner_row, Tw_inner_columns) * 0.1)) <– EXACT TARGET
    'MsgBox ("CALCULATED VALUE IS " & Worksheets("Calculated_Marks").Cells(TW_inner_row, Tw_inner_columns)) '<—EXCAT MAPPING VALUE
    'MsgBox (CDbl(Worksheets("Calculated_Marks").Cells(TW_inner_row, Tw_inner_columns)) * (CDbl(Worksheets("TargetWEIGHTEAGE").Cells(TW_inner_row, Tw_inner_columns)) * 0.1))
    Worksheets("Calculated_Marks").Cells(Row, Tw_inner_Columns) = CDbl(Worksheets("Calculated_Marks").Cells(TW_inner_row, Tw_inner_Columns)) * (CDbl(Worksheets("TargetWEIGHTEAGE").Cells(TW_inner_row, Tw_inner_Columns)) * 0.1)
    'MsgBox (Worksheets("Calculated_Marks").Cells(Row, Tw_inner_columns))
    End If
    End If
    Next
    End If
    Next
    End If
    Next
    ' —————————- Calculation has been finished here ———————
    ' Calculating the total and the obtained here '''''' <—- HOLAAA!!
    Dim Total_Obtained As Double
    Dim Total_FROM As Double
    Dim Counter2 As Integer ' This will be used to calculate the target value !d!
    Counter2 = -1
    For Row = 2 To OTW_Rows
    Total_Obtained = 0 ' Setting the variable value to be zero
    Total_FROM = 0 ' Setting the variable value to be zero
    Counter2 = -1
    For Col = 3 To OTW_Columns
    If Col Mod 2 = 1 Then
    Counter2 = Counter2 + 1
    If (Worksheets("Obtained_Total_W").Cells(Row, Col) “” And Worksheets(“Obtained_Total_W”).Cells(Row, Col) “N/A”) Then
    If (Worksheets(“TargetWEIGHTEAGE”).Cells(Row, Col – Counter2) “N/A”) And (Worksheets(“TargetWEIGHTEAGE”).Cells(Row, Col – Counter2) 10) Then
    Total_Obtained = Total_Obtained + (CDbl(Worksheets(“Obtained_Total_W”).Cells(Row, Col)) * (0.1 * CDbl(Worksheets(“TargetWeighteage”).Cells(Row, Col – Counter2)))) ‘ when there is a target weighteage which is applied
    Else
    Total_Obtained = Total_Obtained + CDbl(Worksheets(“Obtained_Total_W”).Cells(Row, Col)) ‘ when there is no target weigteage applied
    End If
    End If
    Else ‘ below is the calculation for the total marks which have been obtained
    If (Worksheets(“Obtained_Total_W”).Cells(Row, Col) “” And Worksheets(“Obtained_Total_W”).Cells(Row, Col) “N/A”) Then
    If Worksheets(“TargetWeighteage”).Cells(Row, Col – Counter2 – 1) “N/A” And Worksheets(“TargetWeighteage”).Cells(Row, Col – Counter2 – 1) 10 Then
    Total_FROM = Total_FROM + (CDbl(Worksheets(“Obtained_Total_W”).Cells(Row, Col))) * (0.1 * CDbl(Worksheets(“TargetWeighteage”).Cells(Row, Col – Counter2 – 1)))
    Else
    Total_FROM = Total_FROM + (CDbl(Worksheets(“Obtained_Total_W”).Cells(Row, Col)))
    End If
    End If
    ‘ Here you need to do the Targetal values sum . !
    End If
    Next
    Worksheets(“Calculated_Marks”).Cells(Row, 106) = Total_Obtained
    Worksheets(“Calculated_Marks”).Cells(Row, 107) = Total_FROM
    Next
    ‘ Here we are checking the percentage of the Dependent worksheet
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    End Sub

  22. Alfonso Scirocco

    Very interesting. What about using add-ins routine versus having the whole code on the same file? Is there a way to force Excel to run on more than one core in multicore processor?

  23. Alisdair

    Many thanks for your help!!!!

    I found that only putting this code in front of your whole macro code wouldn’t work in my case.

    It seemed to enable all of that stuff at some point again. but couldn’t figure out why and where.

    I simply spammed the whole code with a sub “disableUpdates” that contains the code you showed. I put it everywhere where there was a loop.

    That speeded up my code from hours to seconds!

  24. Daniel Burns

    Good afternoon, I realize this is an old thread but I tried to use the above suggestions, and now it appears my screen update will not stay on.

    Everytime I insert a new column for example, it doesnt show the change. I have to run a application.screenupdate=true to show the changes.

    Driving me nuts!

  25. Daniel Burns

    Good afternoon, I realize this is an old thread but I tried to use the above suggestions, and now it appears my screen update will not stay on.

    Everytime I insert a new column for example, it doesnt show the change. I have to run a application.screenupdate=true to show the changes.

    Driving me nuts!

  26. Brent G.

    Screen updating doesnt just get turned off permanently, so id do some debugging and figure out where your’re the missing the “off” — as a fail safe, can always throw screenupdating off in worksheet deactivation or activation, workbook close, etc.

    I didnt really look closely at your code as im on my phone and just browsing, but, it sounds like from what youve described regarding updates from multiple external sources, that powerpivot may be a good option for you. Would be able to cut out a lot of that macro code as well as having the data refreshed a lot quicker and on demand since you would be linked to the data sources… power pivot (cubes) can be set to only update data that has changed, instead of rerunning everything. Worth a look.

  27. Nick

    A simple solution to the problem of switching these tips on and off is to create a class module and to put the speedup code in class_initialise and the return to normal code in the class teminate.

    Instantiating the class in a macro that starts your code and setting the class to nothing at the end of the macro will automatically install and rmove the speed ups

    eg: classSpeedup
    sub Class_initialize()
    Application.Calculation=xlcalculationManual
    end sub
    sub Class_Terminate()
    Application.calculation=xlcalculationAutomatic
    end sub

    module Main
    Dim clsS as classSpeedUp

    set clsS=new classSpeedUp

    ‘********Call your macros here*****

    ‘******when your macros complete

    if not clsS is Nothing then set clsS=Nothing

    This enables the encapsulisatiuon of your code better than a standard module and subs

Leave a Reply

Your email address will not be published. Required fields are marked *