Count Uniqe With Criteria UDF (Addin Question)

[JoshuaD]

Good Morning/Afternoon!

I created a user Defined Function that works amazing whenit is in the active workbooks module. However, when I tried to turn it into an add-init gives me a #Value Error. I know why it is doing this, I just can’t figureout how to solve it. I need a way to use the UDF variables to look at the activeworkbook for the ranges. Currently it is trying to use the range of the add-inand not the workbook that I am calling it from. It also doesn’t work with themultiple criteria if I am using ranges on different sheets in the same workbook.

This function will count all unique values in a range with an optional 4 criteria.


Here is the code:

Public Function COUNTUNIQUE( _
rng As Range, _
Optional LookupRange_1 As Range, Optional Criteria_1 As Variant, _
Optional LookupRange_2 As Range, Optional Criteria_2 As Variant, _
Optional LookupRange_3 As Range, Optional Criteria_3 As Variant, _
Optional LookupRange_4 As Range, Optional Criteria_4 As Variant _
) As Integer
        
        
        
    'Declare Supporting Variables
    Dim dict As Dictionary
    Dim cell As Range
        
    'Ensure Dictionary is empty
    Set dict = New Dictionary
    'Loop through each cell to check criteria, and add to dictionary
        For Each cell In rng.Cells
        
            'Check if Criteria One is met in Criteria One Range, If not goes to NextVal label
            If IsMissing(Criteria_1) = False Then
                If ActiveWorkbook.ActiveSheet.Cells(cell.Row, LookupRange_1.Column) = Criteria_1 Then
                Else
                GoTo NextVal:
                End If
            End If
            
            'Check if Criteria Two is met in Criteria Two Range, If not goes to NextVal label
            If IsMissing(Criteria_2) = False Then
                If Cells(cell.Row, LookupRange_2.Column).Value = Criteria_2 Then
                Else
                GoTo NextVal:
                End If
            End If
            
            'Check if Criteria Three is met in Criteria Three Range, If not goes to NextVal label
            If IsMissing(Criteria_3) = False Then
                If Cells(cell.Row, LookupRange_3.Column).Value = Criteria_3 Then
                Else
                GoTo NextVal:
                End If
            End If
            
            'Check if Criteria Four is met in Criteria Four Range, If not goes to NextVal label
            If IsMissing(Criteria_4) = False Then
                If Cells(cell.Row, LookupRange_4.Column).Value = Criteria_4 Then
                Else
                GoTo NextVal:
                End If
            End If
        
                'Check if Cell.Value is already in Dictionary, If not add it.
                 If Not dict.Exists(cell.Value) Then
                    dict.Add cell.Value, 0
                End If
                
NextVal:
        
        Next
        
        'Counts objects in dictionay and sets the value to the Functions Return Value
        COUNTUNIQUE = dict.Count
        
    End Function

Any Ideas?

 

[JoshuaD]

Edit,

"ActiveWorkbook.ActiveSheet." In Criteria_1 Was just a test.

Funny thing is if i close the workbook and reload it, it calculates correctly. But, if i edit the formula it gives me #Value .

 

[JoshuaD]

So i have identified the issue. Every time i edit the formula, excel looses the link to the Add-in file. Anyone know of a way to correct this?