Issue with Object Reference in VBA

Question

I am trying to write some code that cycles through a range of values and return either a 1 or 0. The code will only run if I select the sheet the calculation takes place in, even though I am telling the procedure that the range it is dealing with is in that sheet. I want this sheet to be very hidden as other people will use this document and I don't want them messing with the formatting. I have named the sheet in the VB as 'Binary' as well. I am still very new to VBA and am trying to learn new things about this language.

I have tried several things to fix the issue, but it breaks when I don't explicitly say to select the sheet. I've commented below in the code where it is the code breaks and I can't find a solution. Printing my debug statements give me the correct values, and the entire project runs correctly as long as I explicitly tell the procedure to select the sheet. I would prefer if there is a solution that does not require me to tell the code to hide/unhide sheets, and have the sheet remain veryhidden.

Sub Binary_Check()

    Dim binaryWS As Worksheet
    Dim summaryLastRow As Long
    Dim summaryLastColumn As Long
    Dim BinaryRng As Range
    
    'binaryWS.Visible = xlSheetVisible
   
    Set binaryWS = Binary

    'Taking away this next line will break where I set BinaryRng
    Binary.Select
    
    summaryLastRow = binaryWS.Range("A" & Rows.Count).End(xlUp).Row
    summaryLastColumn = binaryWS.Cells(1, Columns.Count).End(xlToLeft).Column
    'Debug.Print summaryLastColumn
    'Debug.Print summaryLastRow
    
    'This is what breaks and gives me an error saying Method Range of object _worksheet failed
    Set BinaryRng = binaryWS.Range("B2", Cells(summaryLastRow, summaryLastColumn))
    
    'Debug.Print BinaryRng.Address
        
    For Each cell In BinaryRng
        If InStr(cell, "(") > 0 Then
            cell.Value = 1
        Else
            cell.Value = 0
        End If
    Next cell
    
    'binaryWS.Visible = xlSheetHidden
       
End Sub

howard13243883 · Accepted Answer · 2020-07-21 03:36:09Z

1

If you use the .Cells, .Rows, .Columns method, you have to add the Worksheet reference. Otherwise it will assign an Active Worksheet to that method.

Set BinaryRng = binaryWS.Range("B2", binaryWS.Cells(summaryLastRow, summaryLastColumn))

Usually I would use the With...End With statement:

With binaryWS 
    summaryLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    summaryLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    'Debug.Print summaryLastColumn
    'Debug.Print summaryLastRow

    'This is what breaks and gives me an error saying Method Range of object _worksheet failed
    Set BinaryRng = .Range("B2", .Cells(summaryLastRow, summaryLastColumn))
End With

edited Jul 21, 2020 at 3:36

answered Jul 21, 2020 at 3:24

howard13243883

964 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue with Object Reference in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related