When automating reporting in Excel using Visual Basic for Applications (VBA), exporting charts to PDF files is a very common task. However, developers frequently encounter Run-time error '13': Type Mismatch during execution. This error can be especially frustrating when the code appears to work in one workbook but fails in another identical-looking setup.

In this guide, we will break down why this specific error occurs in VBA chart exports and provide a robust, modern solution to fix it.

Understanding the Root Causes of Type Mismatch in VBA

In the provided code snippet, two primary suspects usually cause a Type Mismatch error during runtime:

1. Mismatch Between `Chart` and `Worksheet` Objects

The variable declaration and assignment state:

Dim WSA As Chart
ActiveWorkbook.Charts(1).Activate
Set WSA = ActiveSheet

In Excel VBA, Charts(1) specifically targets dedicated Chart Sheets (sheets that contain only a chart). If your workbook contains a chart object embedded inside a standard Worksheet rather than a standalone Chart Sheet, Excel treats ActiveSheet as a Worksheet object. Attempting to assign a Worksheet object to a variable explicitly typed as Chart immediately throws a Type Mismatch (Error 13).

2. Incorrect Handling of `GetSaveAsFilename` Return Value

Another subtle issue lies in how the user cancellation check is structured:

' myFile is declared as Variant
If myFile <> "False" Then

The Application.GetSaveAsFilename method returns a string path if the user selects a file, but returns the Boolean value False if the user cancels the dialog box. Comparing a Variant holding a Boolean directly to a string literal ("False") forces VBA to perform implicit type coercion. Depending on localized language settings or implicit conversion contexts, this comparison can trigger a Type Mismatch.

The Refactored, Production-Ready Solution

To eliminate these errors, you should explicitly type-check chart references, use explicit error messaging, and safely evaluate the return value of GetSaveAsFilename without string-to-boolean confusion.

Here is the updated, clean VBA script:

Sub ExportChartToPDF()
    Dim chartObj As Chart
    Dim wbA As Workbook
    Dim strTime As String
    Dim strName As String
    Dim strPath As String
    Dim strFile As String
    Dim strPathFile As String
    Dim myFile As Variant
    
    On Error GoTo errHandler
    
    Set wbA = ThisWorkbook
    
    ' Ensure at least one dedicated Chart sheet exists
    If wbA.Charts.Count > 0 Then
        Set chartObj = wbA.Charts(1)
    Else
        MsgBox "No standalone Chart sheet found in this workbook.", vbExclamation, "Export Canceled"
        Exit Sub
    End If
    
    ' Calculate week number for the file name
    strTime = Format(DateAdd("ww", -1, Now), "ww")
    
    ' Get active workbook path or fallback to default path
    strPath = wbA.Path
    If strPath = "" Then
        strPath = Application.DefaultFilePath
    End If
    strPath = strPath & "\"
    
    ' Format object name safely
    strName = Replace(chartObj.Name, " ", "")
    strName = Replace(strName, ".", "_")
    
    ' Construct file name
    strFile = "Weekly overview " & strTime & ".pdf"
    strPathFile = strPath & strFile
    
    ' Prompt user for save path
    myFile = Application.GetSaveAsFilename( _
        InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and File Name to Save")
    
    ' Properly check if user canceled (Boolean False comparison)
    If myFile <> False Then
        chartObj.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=False, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
            
        MsgBox "PDF file created successfully:" & vbCrLf & myFile, vbInformation, "Success"
    End If

exitHandler:
    Exit Sub

errHandler:
    MsgBox "Could not create PDF file." & vbCrLf & "Error: " & Err.Description, vbCritical, "Error"
    Resume exitHandler
End Sub

Key Takeaways & Best Practices

  • Avoid generic `ActiveSheet` assignments: Always assign object references directly (e.g., Set chartObj = wbA.Charts(1)) instead of activating sheets and referencing ActiveSheet.
  • Use `Err.Description`: Replace deprecated error functions like Error$ with Err.Description inside your error handlers for detailed debugging details.
  • Boolean comparisons: Compare GetSaveAsFilename against literal boolean False (not string "False") to prevent conversion mismatches across different system locales.