'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Strikethrough Text in Excel PDFs Exported via VBA
Question details
The user needs to resolve an issue where Excel workbooks exported to PDF via VBA display unexpected strikethrough formatting on text, despite the original cells not being formatted that way.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Exporting Excel workbooks to PDF format using VBA commands such as ExportAsFixedFormat or PrintOut.
- Observed behavior
- Unexpected strikethrough text appears in the generated PDF files. This anomaly began occurring after a recent Excel update.
Ensure you have access to your workbook's calculation options, as this issue is closely tied to the manual calculation mode in recent Excel versions.
Disable 'Format Stale Values' or Force Workbook Recalculation
Turn off the Format Stale Values option or recalculate the workbook prior to exporting. This resolves the phantom strikethrough formatting caused by the manual calculation mode.
When Excel's calculation mode is set to Manual, a new feature called 'Format Stale Values' may apply strikethrough formatting to indicate cells that have not been recalculated. This visual cue can unintentionally carry over to PDFs when exporting via VBA.
Navigate to the 'Formulas' tab on the ribbon and click on 'Calculate Now', or simply press F9 on your keyboard to force a full recalculation before running your VBA export script.
If you prefer to keep calculation set to Manual without the strikethrough effect, click on 'File' in the top-left corner and select 'Options'.
In the Excel Options dialog box, select the 'Advanced' tab on the left-hand side.
Scroll down to the 'When calculating this workbook' section. Uncheck the box labeled 'Format Stale Values' and click 'OK'.
Execute your VBA macro (ExportAsFixedFormat or PrintOut) again. The exported PDF should now generate without the unexpected strikethrough text.
Experience Flawless VBA and PDF Exporting with WPS Office
Tired of unexpected formatting issues in Microsoft Excel after updates? WPS Office provides a lightweight, highly compatible alternative with robust VBA support and built-in, flawless PDF export tools. Enjoy a familiar interface and seamless migration without the hassle of stale value bugs.
- 1. Download and Install: Download WPS Office for free from the official website and follow the simple installation instructions.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx files without worrying about compatibility loss.
- 3. Run Macros and Export: Execute your existing VBA macros and use the reliable built-in PDF export feature for perfectly formatted documents.
Frequently Asked Questions
Why does my Excel PDF export have strikethrough text?
This occurs due to Excel's 'Format Stale Values' feature. When your workbook is set to Manual calculation, Excel places a strikethrough on cells that need recalculating. This visual indicator accidentally carries over to PDFs when exporting via VBA.
How do I fix formatting issues in VBA ExportAsFixedFormat?
If you are seeing unexpected formatting like strikethroughs, ensure your workbook is fully recalculated by pressing F9 before running the VBA script, or disable the 'Format Stale Values' option in Excel's Advanced options.
Does manual calculation affect VBA macros?
Yes, running macros while calculation is set to manual can leave cells with outdated values. In recent Excel versions, this triggers visual features like 'Format Stale Values', which alters the cell appearance and can negatively impact files exported via macros.
Can I share my workbook with Microsoft to investigate a bug without revealing private data?
Yes, if requested by Microsoft support to investigate an issue like this, you should create a simplified version of your spreadsheet by removing all confidential information, then upload it through a secure private message channel provided by the agent.
