CODEWITHSUNDEEP
vbaexcel
Garrett Broadnax
Garrett Broadnax

Reputation: 3

Wrong value is returned when calling a function from a cell

First post yall.

Long story short, in Excel, when I call the following function (it's in its own module) from a cell, it returns the wrong value. The function returns the correct value when calling it from a sub, as well as when I step through the code (to the end), but the moment I call it from Excel, it returns a different value. Background at the bottom.

Things I've Tried

It really is just this specific function that's giving me this issue, simpler functions do what they're told. I have to assume it has something to do with the order of events Excel is doing things, or the limits of what parts of Excel a function can change. 

Function ActiveDisciplineFilters()

    Application.Volatile 'makes the function update automatically
    Dim disccolumn As Range
    Dim uniquedisc() As String
    Dim uniquediscstring As String

    'create a string of unique values from the Discipline column
    i = 0
    If Range("LayerList[Discipline]").SpecialCells(xlCellTypeVisible).Address = Range("LayerList[Discipline]").Address Then
        ActiveDisciplineFilters = "None"
        Exit Function
    End If

    For Each cell In Range("LayerList[Discipline]").SpecialCells(xlCellTypeVisible)
        If InStr(1, uniquediscstring, cell.Value) = 0 Then
            If i <> 0 Then
                uniquediscstring = uniquediscstring & ", " & cell.Value
            Else
                uniquediscstring = cell.Value
                i = 1
            End If
        End If
    Next

    ActiveDisciplineFilters = uniquediscstring

End Function

Background

In Excel, I have a table. I'm taking all the data in one specific column of that table and creating a string of the unique values in that range (separated by comma). That string must be placed in another cell, for reasons I don't need to get into. If a filter is applied to the column, the unique values update automatically.

What would make Excel give me the right answer when I call it from a sub, then the wrong one when I call it from a cell?

Upvotes: 0

Views: 207

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

Unfortunately, none of the SpecialCells methods work in a UDF. If you need this to be run from the worksheet as a formula, then your code should look like this instead:

Function ActiveDisciplineFilters()

    Application.Volatile 'makes the function update automatically
    Dim disccolumn As Range
    Dim uniquedisc() As String
    Dim uniquediscstring As String
    Dim i As Long
    Dim cell As Range
    Dim bHidden As Boolean

    'create a string of unique values from the Discipline column
    i = 0

    For Each cell In Range("LayerList[Discipline]").Cells
        If cell.EntireRow.Hidden = False Then
            If InStr(1, uniquediscstring, cell.Value) = 0 Then
                If i <> 0 Then
                    uniquediscstring = uniquediscstring & ", " & cell.Value
                Else
                    uniquediscstring = cell.Value
                    i = 1
                End If
            End If
        Else
            bHidden = True
        End If
    Next

    If Not bHidden Then uniquediscstring = "None"
    ActiveDisciplineFilters = uniquediscstring

End Function

Upvotes: 1

Related Questions