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
– David Zemens, Commented Mar 16, 2017 at 17:58
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
– David Zemens, Commented Mar 16, 2017 at 17:59
Range.Find can return Nothing. You have to test the return values. — Comintern
– Comintern, Commented Mar 16, 2017 at 18:00
@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
– Mathieu Guindon, Commented Mar 16, 2017 at 18:19

Brandon Barney · Accepted Answer · 2017-03-16 18:24:42Z

1

I would post this as a comment if I could, but I can't. So I know this won't directly solve it, but it will help in debugging. See below:

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

' It is always best to check the inverse of an object before setting
' setting an object variable to the target object. In this case
' I check to make sure each range can be found, and if not, I
' debug.print which variable cannot be set.

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")

If m_store is Nothing then Debug.Print "m_store is nothing"
If m_type is Nothing then Debug.Print "m_type is nothing"
If Credit_Amt_Col is Nothing then Debug.Print "Credit_Amt_Col is nothing."

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

        ' Use the inverse method above on these three items as well.
        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

I posted a comment inline, but basically I added an inverse check for your first three objects (you would want to do this for your second set of objects as well). This is best practice, but in this case it will also (hopefully) help you pinpoint where the object cant be found.

edited Mar 16, 2017 at 18:24

answered Mar 16, 2017 at 18:05

Brandon Barney

2,3921 gold badge11 silver badges18 bronze badges

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

5 Comments

David Zemens Over a year ago

would be easier to Set the object variable and then simply test If Set m_store Is Nothing etc. Also less overhead, and less "hard-coded" string literals.

Brandon Barney Over a year ago

If you tried to use not after set you could encounter the 'Object Variable not Set' error if find doesnt return an object. This is the intent of the inversion block in the first place. Additionally, I would ideally eliminate the hard-coded string literals, and likely use a listobject if possible instead of using Find, but this isn't Codereview. There is much that could be done to improve the code, but the problem has to be identified first.

David Zemens Over a year ago

If the variables are typed correctly, If m_store Is Nothing won't raise any error.

Brandon Barney Over a year ago

Nevermind, I tested it and see what you mean now. For some reason I thought that if Find found nothing then you would encounter an error when setting an object to nothing. I dont know why I thought this was the case because the logic makes sense. I think I was thinking of it in the sense of trying to act on the variable. In any case, i'll edit the code to reflect this. It is indeed better to try setting all three, and then test all three to see if they contain something.

David Zemens Over a year ago

No worries! Yeah, it will def raise an Object Required if you try to invoke any object.method() but you can always safely test an Object variable for Nothingness. Cheers!

BruceWayne · Accepted Answer · 2017-03-16 18:34:40Z

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).

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related