Training
Module
Build Office Add-ins for Excel - Training
This module walks through development of Office Add-ins for Microsoft Excel.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Consider the following scenario:
In this scenario, Excel writes to the cells very slowly when you run the VBA code.
Example
In an Excel 2016 worksheet, create 105 ActiveX controls as option buttons.
Set the Visible property of the option buttons to False.
Run the following VBA code:
For row = 1 To 20000
For col = 1 To 5
Cells(row, col).Formula = "=Func()"
Next
Next
You notice that this code runs much slower than it does in earlier versions of Excel.
This issue occurs because of a design change in Excel. Because of this change, the following behavior occurs when VBA code writes a formula to a cell:
To work around this issue, use one of the following methods:
Note
If there are many Shape objects in the worksheet, the VBA code may still run slowly after you use the workarounds. In this situation, remove the Shape objects.
Microsoft has confirmed that this is an issue in the products that are listed in the "Applies to" section.
Training
Module
Build Office Add-ins for Excel - Training
This module walks through development of Office Add-ins for Microsoft Excel.