Getting error: ByRef argument type mismatch - When running Function

[jmazorra]

Hello:

I wrote the following function

Public Function sorting(Sheets As Worksheet, Column As Range, Range As Range, SortOrder As XlSortOrder)

'This function is called when a sheet(s) needs to be sorted. Two procedures
'call on this function to sort.


'@Parameter Sheets to declare sheet(s) name
'@Parameter Column to set the column to sort
'@Parameter Range to set the range to sort
'@Paramenter SortOrder to determine ascending or descending


    Application.ScreenUpdating = False
    
    With Sheets
    
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Column, SortOn:=xlSortOnValues, Order:=SortOrder, DataOption:=xlSortNormal


        With .Sort


            .SetRange Range
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply


        End With


    End With
    
    Application.ScreenUpdating = True


End Function

Now I am trying to run the following sub:

Private Sub cmbCareerPath_Change()





Application.ScreenUpdating = True


    Set WS = Worksheets("FindPath")
    Set Column = WS.Range("D:D")
    Set Rng = WS.Range("A:Z")
    
        Call sorting(WS, Column, Rng, xlAscending)


                               
            Application.ScreenUpdating = False




End Sub

However, when I run my Sub I get the compile error:

ByRef argument type mismatch

on

Call sorting(WS, Column, Rng, xlAscending)

. I am guessing is on WS, but I can't figure out why there would be a type mismatch.

Any ideas?

 

[Rick Rothstein]

However, when I run my Sub I get the compile error:

ByRef argument type mismatch

on

Call sorting(WS, Column, Rng, xlAscending)

. I am guessing is on WS, but I can't figure out why there would be a type mismatch.

Any ideas?

Just a guess... you named your first argument "Sheets"... "Sheets" is then name of a built in property of the Application object and defaults to Application when that object is not specified. Your "With Sheets" may be referencing the Application objects "Sheets" property and not the worksheet object you are passing into the function. My suggestion would be to try changing that argument name to something like Sht or WS instead.

 

[xenou]

Hi,
Since you haven't declared variables in your calling routine, they are all variants. They probably need to be explicitly declared:

dim WS as Worksheet
dim Col as Range
dim Rng as Range
....


Note that I personally would not declare any variable names that have inbuilt object names. It's confusing and possibly buggy.
So I'd prefer define your function:
sorting(sh As Worksheet, col As Range, rng As Range, sort_order As XlSortOrder)
Or something similar i.e., with the variable names not overlapping Excel object names.