By using t2do.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

T2Do.com

 


Create a List Add to top of List Add all to top Add to bottom Edit List Comment Add a Checklist Add list Counter Import/Add many Export List Formatting Modify a List Delete a List
Ascending Descending No Sort
Add ---------- Show list only Show everything Show tags Hide tags
Nav Overview Top Up Show BreadCrumbs Shortcuts Subheader Random Search Last 100 items you added Last 100 modified Public Lists Popular Lists
Create/View to-do List Upcoming Deadlines Add a deadline Time balance List Daily Top 50
How to View Shortcuts Add a Shortcut System shortcuts Saving time with Tags Date/time Time Tracking Shortener AutoHotKeys
Time is your most valuable resource k Link Track Task from List Name Track via shortcut To-do list and time tracking Timer-Creator.com Time Tracking Spreadsheet based Time Tracking Set up @Time Tracking list Reports on how you spent your time Today Popup Current task Window
Random select Shuffle list Random Book Title How to use the Random Tag Random Domain Name Random Book Title Random List Item
To-Do List Add to to-do Set Goals Balance your time Track your time Create User defined header Create Top 10 List Ideas Habits Questions Creativity Accomplished Problems Learn Health Misc Lists
Work to-do Work Terms Work Accomplishments Work 1 Pagers Weekly Status Reports Work Checklists Work Rules Work test
Weight Loss Terms Accomplishments 1 Pagers Health Weekly Status Reports Checklists Rules
-->

How to speed up or optimize slow Excel VBA sheet?

There are a number of ways to speed up a large spreadsheet. Some of them are:
  • Turn off screen updating
  • Turn off recalculation
  • Disable Events
  • Optimize the code
  • Reduce the amount of data
  • Convert Formulas to values
  • Use native Excel over VBA
  • Use a tool to reduce the spreadsheet size
  • Move Excel VBA code to SQL
  • Use a trace routine

    Turn off screen updating
    To turn off screen updating use:

    Application.ScreenUpdating = False

    '....Excel VBA code

    Application.ScreenUpdating = True


    Note that you may want to save the current value and then restore it which is a better practice.

    Dim bScreenUpdating as Boolean
    bScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False

    '....Excel VBA code

    Application.ScreenUpdating = bScreenUpdating


    Turn off recalculation
    You can speed up Excel VBA Code by turning Recalculation off and on

    Application.Recalculation = False


    Application.Recalculation = True

    Disable Events
    Application.EnableEvents = False

    Optimize the code
    Using Ctrl+Break find out where the code is spending most of the time and optimize it.

    You can use:

    'note this won't work if you cross midnight boundary
    dim vStart
    vStart = timer
    ...

    msgbox "Routine took " & timer - vStart

    Reduce the amount of data
    Are you putting too much information in the spreadsheet?  If spreadsheet is too large who will be able to use the information?

    Convert Formulas to values
    Copy your fomulas to the clipboard and then Paste Values to turn them to values. This will often help when slow Vlookups are taking place.

    Use native Excel over VBA
    Use Excel function of VBA developed functions they're much faster.

    Use a tool to reduce the spreadsheet size
    Use software to identify what's using up too much space. I have a tool called Determine Sheet Size that can help with this that I'll publish info about in the near future or contact us at

    Move Excel VBA code to SQL
    If your processing data in a sheet that was retrieved from a database, see if some of the work can be done in the database.

    •