Worksheet Reference Not Working

masouder

Board Regular
Joined
Jul 5, 2013
Messages
79
Office Version
  1. 2013
Platform
  1. Windows
I have several subroutines that accept a worksheet as a parameter. When I pass the parameter using the sheet name (i.e., sht1, shtSetup) the code is inconsistent. Specifically, the first time I run the code it works fine. If I run it again immediately after I get a runtime 91 - Objective variable not set error. When I stop the code and try it again it works fine. Additionally, after running the code several times Excel often crashes. However, if I pass the worksheet parameter using Worksheets("1-Sources") and Worksheets("Setup") there are no issues.

Below is the code inn question. The first subroutine in on the sheet and the second is in a module.

Any ideas why this is not working consistently?

VBA Code:
Sub cmdHideUnusedSources()
    HideSheetRowsSpecificCellLessThan sht1, shtSetup.Range("Setup1aStart"), _
            GetColumnNumber(shtSetup.Range("Setup1CheckColumn")), shtSetup.Range("Setup1CheckAmount")
End Sub

Sub HideSheetRowsSpecificCellLessThan(sSheet As Worksheet, StartRange As Range, CheckColumn As Integer, CheckAmount As Double) 'NEW
    Dim C As Integer
    Dim I As Integer
    
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
    C = 1
    
    Do Until StartRange.Offset(C, 0) = ""
        For I = StartRange.Offset(C, cEndRow) To StartRange.Offset(C, cStartRow) Step -1
            If sSheet.Cells(I, CheckColumn) < CheckAmount Then
                sSheet.Cells(I, 1).EntireRow.Hidden = True
            End If
        Next I
        
        C = C + 1
    Loop
                
CleanUp:
    Set sSheet = Nothing
    Set StartRange = Nothing
    
    Application.Calculation = xlAutomatic
    
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,346
Office Version
  1. 365
Platform
  1. Windows
- How are you setting the sheet names sht1 and shtSetup? (Presumably you're declaring and setting these variables, they are not code names?)
- Which line do you get the error message on?

If the problem is with the sheet names, the error message implies that you haven't set a value for the sheet.

Your code seems a little convoluted, e.g. it's not clear why you generate row numbers using:
For I = StartRange.Offset(C, cEndRow) To StartRange.Offset(C, cStartRow) Step -1
and where cStartRow and cEndRow come from?

As an alternative, i.e. rather than looping through each row, you could simply Filter all values >= CheckAmount
 

masouder

Board Regular
Joined
Jul 5, 2013
Messages
79
Office Version
  1. 2013
Platform
  1. Windows
Stephen, thank you for the reply, and apologies for not acknowledging until now - I didn't receive notification that I had received a reply.

I set the sheet names by changing the Name property in the VBA code window from Sheet1, Sheet2, et cetera, to more descriptive names, then refer to each sheet via the assigned name. This approach has always served me well. That said, if there any gotchas to this approach, I'm all ears.

I receive the error message on the line that calls the HideSheetRowsSpecificCellLessThan routine.

cStartRow and cEndRow are simply global constants.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,346
Office Version
  1. 365
Platform
  1. Windows
OK, you're using the worksheets' codenames - no problem with this.

But ahh, I just realised! On re-reading your original post and code, I see you're cleaning up your variables at the end of Sub HideSheetRowsSpecificCellLessThan. Nothing wrong with a clean up once you're finished with an object, but the problem here is that objects are passed to a Sub by reference . So when you Set sSheet = Nothing, you're really saying Set sht1 = Nothing. That will cause problems.

If you delete your CleanUp lines and try again, I suspect your code will work fine?

If not, next suggestion would be to check that the three range names Setup1aStart, Setup1CheckColumn and Setup1CheckAmount all exist in shtSetup?
 

masouder

Board Regular
Joined
Jul 5, 2013
Messages
79
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Removing the CleanUp lines did the trick! Curiously though: I got the code to work shortly after the initial post by changing sht1 to Worksheets("1-Source") and shtSetup to Worksheets("Setup"). Interesting that the former does not work but the latter does.

Thanks for the help.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,346
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Sub DontTryThisAtHomeKids()

    Destroy Worksheets("1-Source")  'VBA says "Huh?"
    MsgBox Worksheets("1-Source").Name
    Destroy sht1    'VBA says "OK, sure"
    MsgBox sht1.Name
    
End Sub
Sub Destroy(ws As Worksheet)

    Set ws = Nothing

End Sub
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,346
Office Version
  1. 365
Platform
  1. Windows
It sort of makes sense. VBA is happy to tear up a sht1 label.
 

Forum statistics

Threads
1,147,517
Messages
5,741,629
Members
423,674
Latest member
Charles2dodo

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
Top