View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Jim Thomlinson Jim Thomlinson is offline
external usenet poster
 
Posts: 5,939
Default Passing a range to a UDF in VBA that references a different wo

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

Jim Thomlinson


"Jim Thomlinson" wrote:

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

Jim Thomlinson


"jrisch" wrote:

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.