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

[Jonathan Harrison]

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?

'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:

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

 

[wigi]

- Instead of using ThisWorkbook, wouldn't you use ActiveWorkbook?
- Try:

If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub

 

[Jonathan Harrison]

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.

 

[wigi]

And did you also try my 2nd suggestion?

 

[Jonathan Harrison]

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.

 

[Norie]

Which line of code do you get the error on?

 

[shg]

Change the line to

    Set RealUsedRange = Range([COLOR=red]sheetToCheck[/COLOR].Cells(FirstRow, FirstColumn), _
                              [COLOR=red]sheetToCheck[/COLOR].Cells(LastRow, LastColumn))

 

[Jonathan Harrison]

The error comes from this line:

rangeRows = RealUsedRange(dssWKS).Rows.Count

shg, nice catch but it didn't solve my problem.

 

[Norie]

Have you checked that the function RealUsedRange is actually returning something?

 

[Jonathan Harrison]

Yes, the function returns the proper results when any workbook is open or the embedded sheet is not hidden.