I have a Run Time Error 91 for an Excel Add In-CodePudding

I spent hours trying to debug this. This is a a macro to generate PDF from my selected cells. This code works on my personal workbook but when I export it as an add-in, add it in the developer tab and review the code again in the add-in workbook I keep getting an run time error 91: object variable or with block variable not set. Any help will be appreciated!

Sub Save_Selection_As_PDF_sheet()

Dim my_file As String


With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(0)
            .RightMargin = Application.InchesToPoints(0)
            .TopMargin = Application.InchesToPoints(0)
            .BottomMargin = Application.InchesToPoints(0)
            .HeaderMargin = Application.InchesToPoints(0)
            .FooterMargin = Application.InchesToPoints(0)
            '.Orientation = xlLandscape
            .Orientation = xlPortrait
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .PrintArea = Selection.Address
    Debug.Print I
    End With

FileName = ActiveWorkbook.Name
    If InStr(FileName, ".") > 0 Then
       FileName = Left(FileName, InStr(FileName, ".") - 1)
    End If
    
    my_file = "H:\data\Desktop\" & FileName & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    FileName:=my_file, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    
End Sub

CodePudding user response：

One possible solution is to load the add-in in the right way. Your error occurs, if you open the add-in directly. You should load the add-in according to this instructions: Microsoft Excel: Add or remove add-ins

CodePudding user response：

Export Selection to PDF (Run from an Add-In)

This particular error, Run-time error '91': Object variable or With block variable not set, occurred when there was no unhidden workbook open and when the ActiveSheet was a chart sheet.
Different errors occurred when the folder didn't exist (Run-time error '1004': Document not saved. The document may be open, or an error may have been encountered when saving.) or the selection was not a range (Run-time error '438': Object doesn't support this property or method).

Option Explicit

Sub ExportSelectionToPDF()
    Const ProcName As String = "ExportSelectionToPDF"
    On Error GoTo ClearError

    Const dFolderPath As String = "H:\data\Desktop\"
    Const dFileExtension As String = ".pdf"
        
    If Len(Dir(dFolderPath, vbDirectory)) = 0 Then
        MsgBox "The path '" & dFolderPath & "' doesn't exist.", _
            vbCritical, ProcName
        Exit Sub
    End If
    
    Dim sh As Object: Set sh = ActiveSheet
    
    If sh Is Nothing Then ' to test, close all workbooks
        MsgBox "No active sheet ('Nothing').", vbCritical, ProcName
        Exit Sub
    End If
    
    If sh.Type <> xlWorksheet Then ' to test, activate a chart sheet
        MsgBox "No worksheet ('" & sh.Name & "') active.", vbCritical, ProcName
        Exit Sub
    End If
    
    If TypeName(Selection) <> "Range" Then ' to test, select a shape
        MsgBox "No range ('" & TypeName(Selection) & "') selected.", _
            vbCritical, ProcName
        Exit Sub
    End If
    
    Dim paAddress As String: paAddress = Selection.Address
    
    Dim BaseName As String: BaseName = sh.Parent.Name
    If InStr(BaseName, ".") > 0 Then
        BaseName = Left(BaseName, InStrRev(BaseName, ".") - 1)
    End If
    
    Dim dFilePath As String: dFilePath = dFolderPath & BaseName & dFileExtension
    
    With sh.PageSetup
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        '.Orientation = xlLandscape
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintArea = paAddress
    End With

    sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=dFilePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
    
ProcExit:
    Exit Sub
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Sub