VBA for Excel, activecell doesn't work while writing function

Question

I wrote a function but for some reason it doesn't work. I debugged and I still don't get what happened. I wrote some other functions to understand what doesn't work and I came to conclusion that activecell is the problematic part. For some reason when I write functions activecell. is not detected. I wrote my code below. The function is with two parameters, r (a range that is only a column, in other words something like Ai:Aj) and name (which has to be a string) and what this function is supposed to do is count the longest appearance of name continuously, in other words if I have the entered cells that have the values a,a,b,a,a,a , if name = a then the function will return 3.

Function cont(r As range, name As String) As Integer
Dim count As Integer, l As Integer
r.Cells(1).Activate
l = 0
count = 0
Do While IsEmpty(ActiveCell) = False
    If ActiveCell.Value <> name Then
        ActiveCell.Offset(1, 0).Activate
    Else
        Do While ActiveCell.Value = name
            count = count + 1
            ActiveCell.Offset(1, 0).Activate
        Loop
        If count >= l Then l = count
    End If
    count = 0
Loop
cont = l
End Function

I looked up to see if anybody else has a similar problem to what I have, but I couldn't find something useful. Maybe someone here can tell me what's wrong?Thanks!

Scott's supplied an answer, but as an explanation as to why your code wasn't working a UDF cannot change the `ActiveCell`, so your line `r.Cells(1).Activate` doesn't do anything, and the `ActiveCell` remains as the cell that triggered the call to `cont`, which would be the cell in which the `=cont(...)` occurs if you are entering/updating it (and would therefore give a circular reference error), or the cell within your `r` range that was changed. (And if the cell contained a value of `name`, you would probably get an infinite loop or, at least, a loop until `count` became 32767.) — YowE3K, Dec 30 '16 at 18:18

score 1 · Accepted Answer · edited May 23 '17 at 11:45

Do not use activate or select whenever possible, just iterate through the input range and test it:

Function cont(r As Range, name As String) As Integer

Dim i&, j&, cnt&
For i = 1 To r.Rows.count
    If r(i, 1).Value = name Then
        For j = i To r.Rows.count
            If r(j, 1).Value <> name Then
                Exit For
            Else
                cnt = cnt + 1
            End If
        Next j
        If cnt > cont Then cont = cnt
        cnt = 0
        i = j
    End If
Next i

To do it with only one loop then:

Function cont(r As Range, name As String) As Integer

Dim i&, cnt&, tst As Boolean
For i = 1 To r.Rows.count
    If r(i, 1).Value = name And tst = True Then
        cnt = cnt + 1
    ElseIf r(i, 1).Value = name And tst = False Then
        tst = True
        cnt = 1
    Else
        tst = False
        If cont < cnt Then cont = cnt
    End If
Next i
If cont < cnt Then cont = cnt   

End Function

See here for more information on avoiding select and activate: How to avoid using Select in Excel VBA macros

Scott, your code counts all appearances of name in range, the OP wants to keep only continuous appearances — Shai Rado, Dec 30 '16 at 17:39

VBA for Excel, activecell doesn't work while writing function

1 Answers1