VBA Count Code Circular reference

Question

Good morning,

I am trying to automate some licensing queries for a particular application.  I am trying to use VBA code to simply count the unique values in a column.  I whipped up the code below and ran it.  The function returns zero, but not before I get this message:

There are one or more circular refernces where a formula refers to its own cell either directly or indirectly.  This might cause them to calculate incorrectly.  Try removing or changing these references, or moving the formulas to different cells.

The sheet with the data is the output of a query.  I run the code (using the command =ColumnACount() ) in cell H2.  On the bottom message bar, next to the Ready box, I get a message Circular Reference: H2.  I have tried moving it and get the same result.  I run the code in the debugger and F8 through all the code.  when I get down to the "End Function" line, the ColumnACount has the right answer, but when I end the function, it plops a big old zero in the target cell.

I have been trying to debug this for 2 or 3 hours to no avail.  I am hoping it is some obscure setup I need to do and not a really stupid code issue.  I want to eventually generalize this to work on any column on any sheet, but am reluctant to put more hours into it until I get this working.

Novice Using Excel 2016 on W10.  Code below: help!!!

Jim

___

Function ColumnACount() As Integer

' counts unique text values in column A.

'   assumes sheet is sorted on column A.

      Dim i As Integer

      Dim NumRows As Integer

      Application.ScreenUpdating = False

      ' Set numrows = number of rows of data.

      NumRows = Range("A1", Range("A1").End(xlDown)).Rows.count

      ' Select cell a1.

      Range("A1").Select

      ' preset previous name value

      PrevName = ""

      ColumnACount = 0

      ' Establish "For" loop to loop "numrows" number of times.

      For i = 1 To NumRows

         ' compare

         If ActiveCell.Value <> PrevName Then

            ' save it and count it

            PrevName = ActiveCell.Value

            ColumnACount = ColumnACount + 1

            End If

         ' Selects cell down 1 row from active cell.

         ActiveCell.Offset(1, 0).Select

      Next

      Application.ScreenUpdating = True

End Function

Answer 1

Hi Jim,

The function that you are using cannot be used as an Excel worksheet function as the code requires the selection of cells which is not legitimate in the case of a UDF (User Defined Function).

Whilst you could rewrite the function to remove such  cell selections, I would suggest that a much better alternative would be to use the following function written by Charles Williams:

'=========>>

Public Function COUNTU(theRange As Range) As Variant

'--------------------------------------------------------------

' Author     : Charles Williams, Decision Models Limited

' Link         : **http://msdn.microsoft.com/en-us/library/ff700515(v=office.14).aspx**

' Purpose   : Count unique values

'-------------------------------------------------------------

    Dim colUniques As New Collection

    Dim vArr As Variant

    Dim vCell As Variant

    Dim vLcell As Variant

    Dim oRng As Range

    Dim bCount As Boolean

    Set oRng = Intersect(theRange, theRange.Parent.UsedRange)

    vArr = oRng

    On Error Resume Next

    For Each vCell In vArr

                If vCell <> vLcell Then

            If Len(CStr(vCell)) > 0 Then

                colUniques.Add vCell, CStr(vCell)

            End If

        End If

        vLcell = vCell

    Next vCell

    COUNTU = colUniques.Count

End Function

'<<=========

===

Regards,

Norman

Answer 2

Hi Jim,

Norman, thank you.  I clipped it and ran it: successful first time.

I am years away from being able to write something this elegant.

I will read up on what I can and cannot do in a UDF...  

Thank you for your kind feedback and I am glad that you have resolved the problem. 

===

Regards,

Norman

Answer 3

Norman, thank you.  I clipped it and ran it: successful first time.

I am years away from being able to write something this elegant.

I will read up on what I can and cannot do in a UDF... 

Thanks for your help.

Jim