Tags: dim, excel, follows, function, icol, icount, integer, irow, irow2, microsoft, msdn, passing, range, references, rng, software, udf, uniquerows, vba, worksheet

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

On Microsoft » Microsoft Excel

6,513 words with 5 Comments; publish: Fri, 23 May 2008 01:35:00 GMT; (30693.75, « »)

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.

All Comments

Leave a comment...

  • 5 Comments
    • 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.

      >

      #1; Fri, 23 May 2008 01:36:00 GMT
    • 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).

      --

      Regards,

      Tom Ogilvy

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

      >

      #2; Fri, 23 May 2008 01:37:00 GMT
    • 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.

      > >

      > >

      #3; Fri, 23 May 2008 01:38:00 GMT
    • Thanks, both.

      Tom, that explains a lot as I obviously have misunderstood how the

      Range(Cells(,),Cells(,)) works for years :-(

      Regards, John

      #4; Fri, 23 May 2008 01:39:00 GMT
    • Many thanks, both. I now know I've misunderstood how

      Range(Cells(,),Cells(,)) works, for years :-(

      Regards,

      John.

      #5; Fri, 23 May 2008 01:40:00 GMT