I Have A Run Time Error 91 For An Excel Add In - Stack Overflow

1. 1. Home
   2. Questions
   3. Tags
   4. Users
   5. Companies
   6. Labs
   7. Jobs
   8. Discussions
   9. Collectives

   10. Communities for your favorite technologies. Explore all Collectives

2. Teams

   Ask questions, find answers and collaborate at work with Stack Overflow for Teams.

   Try Teams for free Explore Teams
3. Teams

4. Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Get early access and see previews of new features.

Learn more about Labs I have a Run Time Error 91 for an Excel Add In Ask Question Asked 2 years, 11 months ago Modified 2 years, 11 months ago Viewed 2k times 1

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

• excel
• vba
• runtime-error
• add-in

Share Improve this question Follow edited Dec 29, 2021 at 13:32 BigBen 49.9k77 gold badges2727 silver badges4444 bronze badges asked Dec 29, 2021 at 13:05 ezbzqezbzq 1122 bronze badges 2

• What line throws the error? – BigBen Commented Dec 29, 2021 at 13:33
• @BigBen Right after "With ActiveSheet.PageSetup" – ezbzq Commented Dec 29, 2021 at 20:50

Add a comment |

2 Answers 2

Sorted by: Reset to default Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first) 0

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 Share Improve this answer Follow edited Dec 30, 2021 at 1:38 answered Dec 30, 2021 at 1:27 VBasic2008VBasic2008 54.7k55 gold badges2020 silver badges3636 bronze badges 2

• thanks for the response, when I run this I get the msgbox no active sheet but I do have an active sheet open and selection made – ezbzq Commented Dec 30, 2021 at 15:12
• The code begs to differ. Maybe you have opened the add-in in one instance of Excel, and the worksheet in another. Otherwise, you could recreate either the add-in or the workbook: something seems corrupt. I tested the code which I put in an add-in, that I have always opened, and ran the code on the active (open) workbook. Which Office version are you using? Here is an interesting link about instances related to versions. – VBasic2008 Commented Dec 30, 2021 at 16:01

Add a comment | 0

UPDATE 03.01.2022: Did you open a normal Excel-file or your Add-in again after you registered your add-in? You still need a normal workbook to use your code because an add-in doesn't have any worksheets.

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

Share Improve this answer Follow edited Jan 3, 2022 at 10:43 answered Dec 29, 2021 at 23:08 Sergeij_MolotowSergeij_Molotow 8377 bronze badges 1

• Thanks for the response, i did add it as per the link but I'm still getting the same error – ezbzq Commented Dec 30, 2021 at 15:13

Add a comment |

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.
• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Draft saved Draft discarded

Sign up or log in

Sign up using Google Sign up using Email and Password Submit

Post as a guest

Name Email

Required, but never shown

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged

• excel
• vba
• runtime-error
• add-in

or ask your own question.

• The Overflow Blog
• Why do developers love clean code but hate writing documentation?
• This developer tool is 40 years old: can it be improved?
• Featured on Meta
• The December 2024 Community Asks Sprint has been moved to March 2025 (and...
• Stack Overflow Jobs is expanding to more countries

Related

2 What is run time error 91, and why is it showing up in my Excel VBA script? 0 Run-time error 91 0 How to fix Runtime Error "91" in Excel 0 run time error 91 vba 0 Runtime error 91 vba 1 Microsoft Visual Basic Run-Time error 91 0 VBA Run-Time Error 91 - 2 0 Excel VBA Run-Time Error 91 1 Excel VBA run-time error 91 can't solve 0 Run-time error '91

Hot Network Questions

• Why don't the Bene Gesserit retaliate against Vladimir Harkonnen for trying to kill Jessica and Paul?
• scp with sshpass does not work (with custom identity file and custom port)
• Why is it YHWH and not 'HYH?
• Are special screws required inside an oven?
• Will Homescreen website shortcuts be gone if I switch carriers?
• How to repair a broken ground wire?
• Why is there no AES-512 for CTR & variants to have good large nonces?
• Odds of hitting a star with a laser shone in a random direction
• Two gang cover - half receptacle half flat?
• Errors while starting vite + react
• Would it be possible to use a Cygnus resupply spacecraft as a temporary space station?
• Will a PC complain if a USB 2 flash drive is powered externally?
• Splicing 3 wires into 4 wires 220 to an RV pad
• Is ‘drop by’ formal language?
• A SAT question about SAT property
• A mistake in revised version
• Makefile for a tiny C++ project
• How feasible would it be to "kill" the Sun by using blood?
• Jingle bells, Christmas sells!
• Mastering the inner game of bullying/harrassment
• I probably disallowed using the camera at some time in the past and now can't find a way to allow it again. How can I reenable it?
• How to account for disproportionate group sizes?
• Does "To the Moon" generate interest while using the Green Deck?
• Movie ends with wall mounted alien hand moving. Poison lump on hand

more hot questions Question feed Subscribe to RSS Question feed

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

lang-vb