I am currently using a macro to export my excel sheet to a pdf. The current excel sheet has the date in format MM/DD/YY, however, when I export the sheet the date appears to be crossed through.

Excel

PDF:

Below is the code I am currently using to export the information:

Sub Export_Overview()
On Error GoTo 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim OrificeOverview As Worksheet
Set OrificeOverview = ThisWorkbook.Worksheets("Orifice Overview")
    
    OrificeOverview.Shapes.Range(Array("Rectangle 1")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    
    With ActiveSheet.PageSetup
        .PrintArea = "Top_Corner:Bottom_Corner"
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Calculation = xlCalculationAutomatic

    OrificeOverview.Shapes.Range(Array("Rectangle 1")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.75
        .Transparency = 0
        .Solid
    End With

OrificeOverview.Range("DischargePressure_Avg").Select

Application.ScreenUpdating = True
End Sub

Is there another way to export without creating this strike through? I have attempted changing the formatting in excel but even with other date formats it still strikes through the text. The error does not appear when I manually save-as pdf or print to pdf.

I am currently using a macro to export my excel sheet to a pdf. The current excel sheet has the date in format MM/DD/YY, however, when I export the sheet the date appears to be crossed through.

Excel

PDF:

Below is the code I am currently using to export the information:

Sub Export_Overview()
On Error GoTo 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim OrificeOverview As Worksheet
Set OrificeOverview = ThisWorkbook.Worksheets("Orifice Overview")
    
    OrificeOverview.Shapes.Range(Array("Rectangle 1")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    
    With ActiveSheet.PageSetup
        .PrintArea = "Top_Corner:Bottom_Corner"
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Calculation = xlCalculationAutomatic

    OrificeOverview.Shapes.Range(Array("Rectangle 1")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.75
        .Transparency = 0
        .Solid
    End With

OrificeOverview.Range("DischargePressure_Avg").Select

Application.ScreenUpdating = True
End Sub

Is there another way to export without creating this strike through? I have attempted changing the formatting in excel but even with other date formats it still strikes through the text. The error does not appear when I manually save-as pdf or print to pdf.

• excel
• vba
• pdf

• 1 Does it do it with different fonts ? – CDP1802 Commented Feb 14 at 16:04
• Yes. Time New Roman and Calibri produce the same error. Aptos fails altogether as that font is not available in Adobe PDF. – Jake C Commented Feb 14 at 16:18

2 Answers 2

This is the "Format stale values" feature you're seeing.

https://support.microsoft/en-au/office/stale-value-formatting-4b5c63c3-5dc7-4e9b-8e24-88bed1987dbd

In the Formulas tab under "Calculation options"

"Format Stale Values" can be set if you have calculation set to either "Partial" or "Manual"

You can turn it off in VBA:

Application.FormatStaleValues = False

...or perform a full calculation before your Print operation.

Still unsure what was causing the previous error but exporting to PDF rather than printing seems to have resolved the issue.

'Export as PDF
Set selectedRange = Range("Top_Corner:Bottom_Corner")
fileDatePart = Format(Now(), "yyyy-mm-dd") ' Format the date
fileTimePart = Format(Now(), "hh-mm-ss") ' Format the time (24-hour format)
filePath = Application.GetSaveAsFilename(InitialFileName:="OrificeCalculations_" & fileDatePart & "_" & fileTimePart & ".pdf", FileFilter:="PDF Files (*.pdf), *.pdf")
If filePath = "False" Then GoTo Error:
selectedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True