Using a Private Function inside a Sub creates Subscripts out of Range?

Question

So I have a macro that compares two workbooks and eliminates any similar entries on the Workbook running the macro but I have at the end a Private Function that runs to sort the second sheet of the Workbook's contents. When I run the function by itself it works fine, executing and sorting the heet with no problem, however when I run it in the Macro itself it returns this:

Run-time error '9': Subscript out of Range

Sub CheckReadyBoard()
    Dim I, J, total, fRow As Integer
    Dim found As Range


    'SP = SharePoint List, SPS = SharePoint Sheet
    'Filter by Dept then Machine
    '
    Dim SP As Workbook
    Dim SPS As Worksheet
    Set SP = Workbooks.Open(get_user_specified_filepath())
    Set SPS = SP.Sheets(1)
    Set HSheet = ThisWorkbook.Sheets("Building Parts")
    ' Remove Anything from Readyboard and NOT Ready for T
    HBLR = HSheet.Range("A" & Rows.Count).End(xlUp).Row
    RBLR = SPS.Range("A" & Rows.Count).End(xlUp).Row

    For I = 2 To HBLR
        answer1 = HSheet.Range("I" & I).Value
        Set found = SPS.Columns("G:G").Find(what:=answer1) 'finds a match
        If found Is Nothing Then
            'Leave Line w/ Nothing so it will pass this line
        Else
            HSheet.Range("I" & I).EntireRow.Delete
        End If
    Next I
    'Application.ScreenUpdating = False
    'Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    'Application.ScreenUpdating = True
    Sort2
    SP.Close

End Sub



Private Function Sort2()
    Set HSheet = ThisWorkbook.Sheets("Building Parts")
    HBLR = HSheet.Range("A" & Rows.Count).End(xlUp).Row
    Worksheets("Building Parts").Activate

    With Range(Columns("A"), Columns("I"))
        .Sort key1:=Columns("A"), Order1:=xlDescending, Header:=xlNo
        .Sort key1:=Columns("B"), Order1:=xlDescending, Header:=xlNo
    End With
End Function

When I hit debug it highlights the Worksheets("Building Parts").Activate I'm not super well-versed in VBA so i'm not sure if my Syntax is bad or if my I need to make a slight edit someplace..

Information that might be important:

1)My Workbook contains two sheets. Sheet 1 has buttons to initiate Macros and Sheet 2 contains the data.

2)Adding Worksheets("Building Parts").Activate made the Sort work on its own, previously it didn't work by itself or in the Macro..

First of all, you use `Function` instead a `Sub` when you need to retrieve something, you are no retieving anything from your `Function` So I would change that. Second and most important, fully qualify all your references: `workbook.sheet.range` — Damian, Feb 13 '20 at 15:04
Side note: add `Option Explicit` to the top of the module and declare all variables... it'll take your coding to the next level. — BigBen, Feb 13 '20 at 15:09
A workbook must be active before you can activate a sheet. Read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Feb 13 '20 at 15:11
@Damian Could you Clarify what that means? I thought ```Function``` is used when retrieving something from code but my code is meant to just delete rows and sort the sheet. @SJR I thought I was already declaring my Workbook as ```Dim SP As Workbook``` @BigBen I added it but one of my variables says that it is un-defined? ``` Set HSheet = Worksheets("Building Parts")``` — Rob, Feb 13 '20 at 15:22
Just exactly what I meant @RobertFarmer your function isn't retrieving any result, so change `Function` To `Sub` — Damian, Feb 13 '20 at 15:26
Declaring variables is a different thing. If the workbook containing "Building Parts" is not active you cannot activate that sheet without previously activating that workbook. Read that link I posted and you will see that you need do neither. Re the other error you haven't declared `HSheet`. — SJR, Feb 13 '20 at 15:33
@Damian Ah, I thought you meant change Sub to Function which I was confused about. @SJR If I' understood the post in question I should have my HSheet as ```Set HSheet = ActiveWorkbook.Worksheets('Building Parts")``` instead of what I have previously. — Rob, Feb 13 '20 at 16:32

Damian · Accepted Answer · 2020-02-13T15:47:22.027

Along with my suggestions and both SJR and BigBen you could try this:

Option Explicit
Sub CheckReadyBoard()

    'Dim J, total, fRow As Integer you are not using any variable here

    'SP = SharePoint List, SPS = SharePoint Sheet
    'Filter by Dept then Machine
    '
    Dim SP As Workbook
    Set SP = Workbooks.Open(get_user_specified_filepath())
    Dim SPS As Worksheet
    Set SPS = SP.Sheets(1)
    Dim HSheet As Worksheet
    Set HSheet = ThisWorkbook.Sheets("Building Parts")
    ' Remove Anything from Readyboard and NOT Ready for T
    Dim HBLR As Long
    HBLR = HSheet.Range("A" & Rows.Count).End(xlUp).Row
    Dim RBLR As Long
    RBLR = SPS.Range("A" & Rows.Count).End(xlUp).Row

    'Declare your variable next to their assign line
    Dim I As Long
    Dim answer1 As String
    Dim found As Range
    For I = 2 To HBLR
        answer1 = HSheet.Range("I" & I).Value
        Set found = SPS.Columns("G:G").Find(what:=answer1) 'finds a match

        'The logic here is telling excel to delete the row if a match is found
        If found Is Nothing Then
            'Leave Line w/ Nothing so it will pass this line
        Else
            HSheet.Range("I" & I).EntireRow.Delete
        End If
        'You can change the logic to only keep a single line:
        If Not found Is Nothing Then HSheet.Range("I" & I).EntireRow.Delete
    Next I
    'Application.ScreenUpdating = False
    'Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    'Application.ScreenUpdating = True
    Sort2
    SP.Close

End Sub
Private Sub Sort2()
    Set HSheet = ThisWorkbook.Sheets("Building Parts")
    HBLR = HSheet.Range("A" & Rows.Count).End(xlUp).Row
    'Worksheets("Building Parts").Activate there is no need to activate
    'Besides your error comes here, when you open a workbook, the SP workbook,
    'it goes to the focus, so the active workbook here is SP, not thisworkbook.

    'Fully qualify your ranges so you don't need to activate nor workbooks or sheets.
    With HSheet.Range("A:I")
        .Sort key1:=.Columns("A"), Order1:=xlDescending, Header:=xlNo
        .Sort key1:=.Columns("B"), Order1:=xlDescending, Header:=xlNo
    End With

End Sub

`With HSheet.Range(HSheet.Columns("A"), HSheet.Columns("I"))`... or just `With HSheet.Range("A:I")`. — BigBen, Feb 13 '20 at 15:46
true that... And I was telling to myself, this looks awfully wrong but didn't do anything at all. — Damian, Feb 13 '20 at 15:47

Using a Private Function inside a Sub creates Subscripts out of Range?

1 Answers1