Add-In Run-time error 91 when no workbook is open

Jonathan Harrison

New Member
Joined
Jul 15, 2011
Messages
43
My add-in is producing a "run-time error '91': Object variable or With block variable not set" when I try to run my code without a workbook open. The code is pretty simple and just gets the range of a sheet inside the add-in. If a blank workbook is open, it runs just fine. What gives?

Code:
'create a worksheet object to hold the ALBCC data
Dim dssWKS As Worksheet
Set dssWKS = ThisWorkbook.Worksheets("ALBCC")
'get total number of rows used
Dim rangeRows As String
rangeRows = RealUsedRange(dssWKS).Rows.Count
MsgBox rangeRows

In a seperate module in the add-in:
Code:
Public Function RealUsedRange(sheetToCheck As Worksheet) As Range
 
    Dim FirstRow        As Long
    Dim LastRow         As Long
    Dim FirstColumn     As Integer
    Dim LastColumn      As Integer
    On Error Resume Next
    FirstRow = sheetToCheck.Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    FirstColumn = sheetToCheck.Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
    LastRow = sheetToCheck.Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumn = sheetToCheck.Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))
    On Error GoTo 0
 
End Function
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
- Instead of using ThisWorkbook, wouldn't you use ActiveWorkbook?
- Try:

Code:
If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
 
Upvote 0
I'm using ThisWorkbook instead of ActiveWorkbook because I'm referring to a worksheet embedded in the add-on, not one that a user may be working with, which would be the ActiveWorkbook.
 
Upvote 0
And did you also try my 2nd suggestion?
 
Upvote 0
TypeName(ThisWorkbook) returns "Workbook" as it should, regardless of whether a file is open.

To be clear, I want this script to be executable without any workbooks open.
 
Upvote 0
Which line of code do you get the error on?
 
Upvote 0
Change the line to

Code:
    Set RealUsedRange = Range([COLOR=red]sheetToCheck[/COLOR].Cells(FirstRow, FirstColumn), _
                              [COLOR=red]sheetToCheck[/COLOR].Cells(LastRow, LastColumn))
 
Upvote 0
Have you checked that the function RealUsedRange is actually returning something?
 
Upvote 0

Forum statistics

Threads
1,222,029
Messages
6,163,487
Members
451,838
Latest member
DonSlayer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top