VBA: Function gives “Run time error '424': Object required” Error when called

Question

I have two main functions, the first is search_bank. It searches through the Credits, Type, and store columns, cell by cell and figures out if we have a match or not.If there is a match, it returns True and as a side effect changes the color of the matched cell.

The second sub I'm using to test the first function. The problem I'm having is that I'm getting a Run time error '424': Object required with no indication of where the issue is.

Here is the first function:

Function search_bank(Store As String, amount As Double, Amex As Boolean) As Boolean
    Dim m_store As Range
    Dim m_type As Range
    Dim Credit_Amt_Col As Range

    Set m_store = bank_sheet.Range("1:1").Find("M_STORE")
    Set m_type = bank_sheet.Range("1:1").Find("M_TYPE")
    Set Credit_Amt_Col = bank_sheet.Range("1:1").Find("Credit Amt")

    search_bank = False
    Dim i As Long
    For i = 1 To 9000
        If Not search_bank Then
            Dim store_cell As Range
            Dim type_cell As Range
            Dim credit_cell As Range

            Set store_cell = Worksheets(2).Cells(i, m_store.Column)
            Set type_cell = Worksheets(2).Cells(i, m_type.Column)
            Set credit_cell = Worksheets(2).Cells(i, Credit_Amt_Col.Column)

            If InStr(UCase(store_cell.Value), UCase(Store)) > 0 And credit_cell.Value = amount Then
                If store_cell.Interior.ColorIndex <> 46 Then
                    If Amex And InStr(UCase(type_cell.Value), UCase("amex deposit")) Then
                        store_cell.Interior.ColorIndex = 46
                        search_bank = True

                    End If
                    If Not Amex And InStr(UCase(type_cell.Value), UCase("Credit Card Deposit")) Then
                        store_cell.Interior.ColorIndex = 46
                        search_bank = True

                    End If
                End If
            End If
        End If
    Next i

End Function

and here is the tester:

Sub Tester()
    Dim x As Boolean
    x = search_bank("ctc", 38.4, True)
    Debug.Print (x)
End Sub

I have tried using 'set' on the tester:

Sub Tester()
    Dim x As Boolean
    Set x = search_bank("ctc", 38.4, True)
    Debug.Print (x)
End Sub

And even declaring the variable before passing them in the tester (I'm not very used to VBA but for a moment I believed it was just so ancient, it needed things to be declared before they're passed)

Sub Tester()
    Dim x As Boolean
    Dim store As String
    Dim Amount As Double
    Dim amex As Boolean
    store = "ctc"
    Amount = 38.4
    amex = True
    x = search_bank(store, Amount, amex)
    Debug.Print (x)
End Sub

When you get the runtime error, select the Debug option and use F8 to step through the code until you see the line which errors. — David Zemens
Also, ensure that the result of your three Find methods return valid objects. If the values are not found in row 1, then they'll return a Nothing, which would raise this error later in your code. — David Zemens
Range.Find can return Nothing. You have to test the return values. — Comintern
@RyanFrancis FWIW you do have a global worksheet object for free. Look at the Project Explorer toolwindow; worksheet nodes appear as Sheet1 (Sheet1) - that's CodeName (WorksheetName), where CodeName is a global-scope Worksheet object variable you get for free. You can control its name in the properties toolwindow (F4), by changing the (Name) property. — Mathieu Guindon

BruceWayne BruceWayne · Accepted Answer · 2017-03-16T18:34:40

There's lots of good commentary under your OP, and with @BrandonBarney's answer too, but here's my two cents:

Cent one: The biggest thing I see is you never declare blank_sheet yet try to use it while setting a range object. This is where your error is coming from. It's looking to do Range("1:1").Find("M_STORE"), but doesn't know what bank_sheet is.

Cent two: A quick way to have this pointed out to you is to always use Option Explicit at the top of your code. That ensures that any variable you use is explicitly declared. I.e.:

Option Explicit

Function search_bank(Store As String, amount As Double, Amex As Boolean) As Boolean
    Dim m_store As Range
    Dim m_type As Range
    Dim Credit_Amt_Col As Range

    ''''' New code here: ''''''
    Dim bank_sheet as Worksheet
    Set bank_sheet = Worksheets("Bank Sheet") ' change to whatever the name is.
    '''''''''''''''''''''''''''
    Set m_store = bank_sheet.Range("1:1").Find("M_STORE")
    Set m_type = bank_sheet.Range("1:1").Find("M_TYPE")
    Set Credit_Amt_Col = bank_sheet.Range("1:1").Find("Credit Amt")
    ' etc. etc.

Option Explicit will also help if you ever accidentally have a typo. So if you ever did bank_sheeet.Range("A:A") it'll error out and ask you to declare bank_sheeet. Or, of course, you'll realize it's a typo and then just fix it.

Bonus cent: You can save a few lines by combining your Dims:
Dim m_store as Range, m_type as Range, Credit_Amt_Col as Range can all be on one line.

(Note: Doing Dim m_store, m_type, Credit_Amt_Col as Range will not set all three to Range type. It'll make m_store and m_type a Variant since it's not declared. Only Credit_Amt_Col would be a Range in that case. So you still have to explicitly state the type for each variable).

VBA: Function gives “Run time error '424': Object required” Error when called

2 Answers