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

 

[shg]

OK, try this:

Public Function RealUsedRange(wks As Worksheet) As Range
    Dim iRowBeg     As Long
    Dim iRowEnd     As Long
    Dim iColBeg     As Long
    Dim iColEnd     As Long
 
    On Error Resume Next
    With wks
        iRowBeg = wks.Cells.Find(What:="*", _
                                 After:=.Cells(.Rows.Count, .Columns.Count), _
                                 LookIn:=xlValues, _
                                 LookAt:=xlPart, _
                                 SearchOrder:=xlByRows, _
                                 SearchDirection:=xlNext).Row
        If iRowBeg = 0 Then
            Set RealUsedRange = .Range("A1")
        Else
            On Error GoTo 0
            iColBeg = wks.Cells.Find(What:="*", _
                                     After:=.Cells(.Rows.Count, .Columns.Count), _
                                     SearchOrder:=xlByColumns, _
                                     SearchDirection:=xlNext).Column
            iRowEnd = wks.Cells.Find(What:="*", _
                                     After:=Range("A1"), _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlPrevious).Row
            iColEnd = wks.Cells.Find(What:="*", _
                                     After:=Range("A1"), _
                                     SearchOrder:=xlByColumns, _
                                     SearchDirection:=xlPrevious).Column
            Set RealUsedRange = Range(.Cells(iRowBeg, iColBeg), .Cells(iRowEnd, iColEnd))
        End If
    End With
End Function

 

[Norie]

But when the problem happens does it return the proper results?

There's actually some worksheet references that might be missing in the function code.

For example in this piece of code you don't have a worksheet reference for Range("IV65536").

FirstRow = sheetToCheck.Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

 

[ZVI]

Wrong: After:=Range
Correct: After:=.Range

This works:

Sub Test()
  Dim Sh As Worksheet, Rng As Range, rs As Long
  Set Sh = ThisWorkbook.Worksheets("ALBCC")
  Set Rng = RealUsedRange(Sh)
  rs = Rng.Rows.Count
  MsgBox Rng.Address & vbLf & rs
End Sub

Public Function RealUsedRange(sheetToCheck As Worksheet) As Range
  Dim FirstRow As Long
  Dim LastRow As Long
  Dim FirstColumn As Long
  Dim LastColumn As Long
  'On Error Resume Next
  With sheetToCheck.Cells
    FirstRow = .Find(What:="*", After:=.Range("IV65536"), LookIn:=xlValues, LookAt:= _
                      xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    FirstColumn = .Find(What:="*", After:=.Range("IV65536"), LookIn:=xlValues, LookAt:= _
                      xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
    LastRow = .Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
                      xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumn = .Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
                      xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Set RealUsedRange = .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn))
  End With
End Function

 

[Jonathan Harrison]

Norie, that was it! I needed a worksheet ref before Range. Thanks.

 

[Jonathan Harrison]

ZVI, even better. Thanks.