Excel Macro - VBA: Selection or range is not accepted as a passed parameter to another function. Runtime error 424 encountered

Question

I have a simple Sub() to pass the current selected cell/s as range to another function but I'm getting an Error 424: Object required when I run the sub.

I already checked SO and saw other Error 424 posts but all are of a different case than mine.

Code:

Public Sub ShowRemoveLicTypModal()
    Dim MsgBoxInput As Integer

    'Show confirmation for removing license types
    MsgBoxInput = MsgBox("Would you like to remove the selected license types?", _
    vbYesNo)

    Select Case MsgBoxInput
        Case vbYes
            RemoveLicenseTypeFromList (Selection) '==Error 424 encountered on this line
            ThisWorkbook.Save
        Case vbNo
    End Select
End Sub

The line RemoveLicenseTypeFromList (Selection) where I'm getting the error. It calls the function below. I've already removed all the code from this function for debugging but I'm still getting the error.

Private Function RemoveLicenseTypeFromList(InputRange As Range)
'Nothing here
End Function

I've already tried the following:

Assign selection to a Range variable

Public Sub ShowRemoveLicTypModal()
    Dim MsgBoxInput As Integer
    Dim Temp As Range

    Set Temp = Selection

    'Show confirmation for removing license types
    MsgBoxInput = MsgBox("Would you like to remove the selected license types?", _
    vbYesNo)

    Select Case MsgBoxInput
        Case vbYes
            RemoveLicenseTypeFromList (Temp)
            ThisWorkbook.Save
        Case vbNo
    End Select
End Sub

Watch the Selection / Range being passed to RemoveLicenseTypeFromList and I can see that the type is Object/Range OR (Range/Range) for Temp so the types should be matching.

Questions:
What could possibly be causing this error and how do I fix it?

My selection:
Cell A13 shown highlighted on the image. I also tried changing selection all with similar results.

you only need the brackets when returning to an object. RemoveLicenseTypeFromList temp — Nathan_Sav
If an error persists after removing all code from the called sub or function the error must be in the call itself. I think with that logic you would have found the error yourself because when you type RemoveLicenseTypeFromList(Selection) VBA will insert a space before the bracket, indicating that it isn't required. — Variatus

YowE3K YowE3K · Accepted Answer · 2017-05-19T09:28:15

Remove the brackets!!

RemoveLicenseTypeFromList (Selection)

should be

RemoveLicenseTypeFromList Selection

because you are not assigning the return value from RemoveLicenseTypeFromList to a variable.

And it is a bit pointless to have a Function that doesn't return a value. (It returns a Variant/Empty by default, but it's a bit pointless always returning a Variant/Empty) Your Function should be a Sub if it isn't going to return anything.

Excel Macro - VBA: Selection or range is not accepted as a passed parameter to another function. Runtime error 424 encountered

2 Answers