Forum Discussion

terrymatthews's avatar
terrymatthews
Copper Contributor
Oct 18, 2023

Macro enabled workwook lagging

I'm sure this is a popular subject. 

 

I have a macro enabled workbook and it has a lag after performing a macro. i have had this before with other workbooks and had to start again but i am hoping there is something simple causing this so i don't have to start again with this one. 

 

The macros are very simple in that they unhide a sheet i want to work on and hide the one i was on. It seems that when i open the workbook everything is ok but when i perform a macro it then has a lag to it when click from cell to cell. 

Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 18, 2023

    Lagging in a macro-enabled workbook can be caused by various factors. To resolve the lag issue, consider the following steps:

    1. Optimize Your Code: Review your VBA code to ensure it's well-optimized. Inefficient code can slow down your workbook. Here are a few tips:
      • Minimize the use of Select and Activate statements.
      • Avoid using unnecessary loops or repetitive calculations.
      • Disable automatic calculation during macro execution if applicable and re-enable it afterward.
      • Make sure you clean up any objects (worksheets, ranges) that you no longer need.
    2. Application.ScreenUpdating: Add the following line at the beginning of your macro to temporarily turn off screen updating:

    vba code:

    Application.ScreenUpdating = False

    And at the end of your macro, turn it back on:

    Vba code:

    Application.ScreenUpdating = True

    1. Event Handlers: If your workbook contains event handlers (e.g., Worksheet Change events), they might be firing with each cell selection, causing lag. Ensure that event handlers are necessary and well-optimized.
    2. Conditional Formatting: Excessive or complex conditional formatting rules can slow down your workbook. Review and simplify them if possible.
    3. Workbook Size: Large workbooks with many formulas, charts, or data can lead to sluggish performance. Consider archiving older data or optimizing your worksheets.
    4. Add-Ins: Third-party Excel add-ins can sometimes interfere with performance. Try disabling add-ins to see if it affects the lag.
    5. Excel Version: Older versions of Excel may have limitations that cause lag. If you're using an older version, consider upgrading to a newer one.
    6. Hardware and Resources: Make sure your computer's hardware can handle the workbook's complexity. Insufficient RAM, an overloaded CPU, or running other resource-intensive programs can slow down Excel.
    7. Chart and Image Optimization: If your workbook contains many charts or images, optimizing them (e.g., reducing the size or quality) can improve performance.
    8. File Format: Try saving the workbook in binary format (XLSB) instead of XLSM. Binary formats can improve performance.
    9. Network and Storage: If the workbook is stored on a network drive, access speed might be an issue. Copy the workbook to your local drive and see if the lag persists.
    10. Testing on a Different Computer: Try opening the workbook on a different computer to see if the issue is specific to your setup.

    Implement these steps to identify and resolve the lag issue. By following best practices and optimizing your workbook, you can significantly improve its performance.

    The text was created with the help of AI.

    Welcome to your Excel discussion space! Link to give more information if needet.

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources