How to correctly call a vba function

Question

I'm new to vba, but I'm trying to create a function that dynamically finds the range of an entire sheet to be used in other macros for data cleaning.

Here is the function:

Public Function FindRange(Rw As Long, CL As Long)
Dim Rw As Long
Dim CL As Long

CL = ActiveSheet.Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column

Rw = ActiveSheet.Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
End Function

Here is where I'm trying to call it:

Sub Range_Find_Method()


Call FindRange(Rw, CL)
ActiveSheet.Range("A1", Cells(Rw, CL)).Select



End Sub

I keep getting a ByRef argument type mismatch.

Better to explain what you are trying to do, since it's not clear from your wrong code. Your function doesn't do anything, the variable with which are passing as parameters are set via `find` and then die when the function ends. If your function could return two items (it can't) then they would be of type `Range` but your `Cells(Rw, CL)` requires integers as parameters not Ranges. — JNevill, Jul 09 '18 at 17:31
See if you can modify this to suit (or better yet, just use it): https://www.rondebruin.nl/win/s9/win005.htm — sous2817, Jul 09 '18 at 17:33
`Public Sub FindRange(ByRef Rw As Long, ByRef CL As Long)` but this is not the best approach to your problem. — Tim Williams, Jul 09 '18 at 17:36

3-14159265358979323846264 · Answer 1 · 2018-07-09T17:56:15.307

You shouldn't call a function, you should assign it's return value to a variable in the calling sub/function. You should also give a function a return type eg

Public Function GetUsedRange() As Range
    Set GetUsedRange = ActiveSheet.UsedRange
End Function

Also, as the code above shows, can you not use

ActiveSheet.UsedRange

To find the used range of cells?! https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-usedrange-property-excel

The function I've written is essentially redundant because you can just use

 Dim MyUsedRange As Range
 Set MyUsedRange = ActiveSheet.UsedRange

In which case, you don't even need to go as far as assigning the result to a variable, unless that used range changes during the course of your code running.

score 2 · Answer 2 · answered Jul 09 '18 at 17:40

Consider:

Public Function FindRange()
    Dim Rw As Long
    Dim CL As Long

    CL = ActiveSheet.Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column

    Rw = ActiveSheet.Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    Dim arr(1 To 2) As Long
    arr(1) = CL
    arr(2) = Rw
    FindRange = arr
End Function

Sub MAIN()
    Dim coor() As Long
    coor = FindRange()
    ActiveSheet.Range("A1", Cells(coor(2), coor(1))).Select
End Sub

Mathieu Guindon · Answer 3 · 2018-07-09T18:35:12.620

Public Function FindRange(Rw As Long, CL As Long)
Dim Rw As Long
Dim CL As Long

That shouldn't even compile, Rw and CL local variables are duplicate declarations here:

Compile error: duplicate declaration in current scope

Remove them both, and your code should work as intended, i.e. assigning the ByRef parameters Rw and CL... which could both use a more descriptive name and an explicit ByRef modifier (it's implicit if unspecified).

Also, as others have hinted, functions are expected to return a value. You do that by specifying a return type in the function's signature...

Public Function FindRange(ByVal inRow As Long, ByVal inColumn As Long) As Range

...and then by assigning the function's identifier in the body of that function:

    Set FindRange = result ' where result is a Range object reference

Using ByRef parameters as return values works, but makes for a rather confusing API when the procedure is a Function. Making it a Sub procedure would remove the ambiguity about what the returned values might be, and again naming can help make the intent clearer:

Public Sub FindRange(ByRef outRow As Long, ByRef outColumn As Long)

The Range.Find method call you're making, is assuming a cell will be found - and will crash with run-time error 91 if invoked against an empty sheet. Never assume Range.Find will return a valid object reference - store its result in a Range object reference, and verify if it's Nothing:

Dim result As Range
Set result = ActiveSheet.Cells.Find(What:="*", _
                                    After:=Range("A1"), _
                                    LookAt:=xlPart, _
                                    LookIn:=xlFormulas, _
                                    SearchOrder:=xlByColumns, _
                                    SearchDirection:=xlPrevious, _
                                    MatchCase:=False)
If Not result Is Nothing Then
    outRow = result.Row
    outColumn = result.Column
End If

As a bonus you avoid the need to make the same call twice. But then again, consider just returning the result range instead:

Set FindRange = result

Lastly, look here for an actually reliable way to retrieve the last row/column on a worksheet. UsedRange can tell lies; that answer also lists various ways to do this, and why not to use them.

That single routine is not returning the true last row/last column if the only values in the worksheet were in B99 and Z2. — , Jul 09 '18 at 18:22

score 0 · Answer 4 · answered Jul 09 '18 at 18:36

Set the range objects found.
Pass the parent worksheet in as an argument. Activesheet should never be used in a universal function and you are going to stop using it soon in any event.
Don't redim your arguments.
Since you only attributing new values byRef to the arguments and not returning a value from the function, this might as well be a sub procedure.
Dim the vars in your main. Use Option Explicit.

Here's a rewrite based on the notes above.

Public sub FindRange(ws as worksheet, byref Rw As Long, byref CL As Long)
    with ws
        CL = .Cells.Find(What:="*", _
                         After:=.cells(1), _
                         LookAt:=xlPart, _
                         LookIn:=xlFormulas, _
                         SearchOrder:=xlByColumns, _
                         SearchDirection:=xlPrevious, _
                         MatchCase:=False).Column

        Rw = .Cells.Find(What:="*", _
                         After:=.cells(1), _
                         LookAt:=xlPart, _
                         LookIn:=xlFormulas, _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious, _
                         MatchCase:=False).Row
    end with
End sub

Sub Range_Find_Method()

    Dim Rw As Long
    Dim CL As Long

    FindRange ActiveSheet, Rw, CL
    ActiveSheet.Range("A1", ActiveSheet.Cells(Rw, CL)).Select

End Sub

How to correctly call a vba function

4 Answers4