Passing a range to a UDF in VBA that references a different worksheet

[jrisch]

Hi, I have a UDF as follows:

Function UniqueRows(rng As Range) As Integer
Dim iRow As Long
Dim iRow2 As Long
Dim iCol As Integer
Dim iCount As Integer
Dim colStrings As New Collection
Dim strKey As String

For iRow = 1 To rng.Rows.Count
strKey = ""
For iCol = 1 To rng.Columns.Count
strKey = strKey & "~" & rng.Cells(iRow, iCol).Text
Next iCol
On Error Resume Next
colStrings.Add strKey, strKey
On Error GoTo 0
Next iRow
UniqueRows = colStrings.Count
End Function

It produces the expected result when used on a worksheet even if the
range referenced is on a different one. However, when trying to use it
in a subroutine in VBA, I don't know how to pass a range to the UDF if
the range is not on the active worksheet.

I'm using a statement such as :
iUniqueRows =
UniqueRows(Worksheets("Sheet2").Range(Cells(iTLRow,iTLCol),Cells(iBRRow,iBRCol)))

I get an error meesage about an Application error.

Any advice appreciated.

Regards, John.

 

[Jim Thomlinson]

As a guess one or more of your variables used in Cells is invalid. The code
itself is fine... What are the values of iTLRow,iTLCol, iBRRow,iBRCol when
the line is executed. I am guessing that at least 1 of them is 0...

 

[Tom Ogilvy]

With Worksheets("Sheet2")
iUniqueRows = UniqueRows(.Range( _
.Cells(iTLRow,iTLCol),.Cells(iBRRow,iBRCol)))
End with

Note the period (.) preceding Range, Cells and Cells. This qualifies all so
they are on the same sheet. If not, only range is referenced to
Worksheets("Sheet2"). the unqualified cells references refer to the
activesheet (if the code is in a general module) of to the sheet containing
the code (if the code is in a sheet module).

 

[Jim Thomlinson]

I should know enough not to answer questions before my first cup of coffee.
Take a look at Tom's post... Mine is off the mark.

 

[jrisch]

Thanks, both.

Tom, that explains a lot as I obviously have misunderstood how the
Range(Cells(,),Cells(,)) works for years :-(

Regards, John

 

[jrisch]

Many thanks, both. I now know I've misunderstood how
Range(Cells(,),Cells(,)) works, for years :-(

Regards,
John.