In the previous article, I told you how to use the excel formula to count unique values and distinct values in excel. In this article, I will tell you how to define customized VBA functions to do the same thing using VBA.
1. Insert VBA Module In Excel Worksheet.
- This example will use the data tables in the article How To Use Excel Formula To Count Unique Value In Excel And Count Distinct Value In Excel.
- Below is the cell table data.
A B C 1 User Name Unique User Name Distinct User Name 2 Jerry Li Tom Ma Jerry Li 3 jerry li Tom Sun Tom Ma 4 Tom Ma Jackie Liu Tom Sun 5 Tom Sun Jackie Liu 6 Jackie Liu =Count_Unique_Value(A2:A6) =Count_Distinct_Value(A2:A6, TRUE) - Open the excel worksheet, and click the Developer tab —> Visual Basic item in the Code group.
- Then it will open the Microsoft Visual Basic for Applications window.
- Click the Insert menu item in the window’s top area.
- Then click the Module menu item in the drop-down menu list to insert a module in the VBA window.
- You can read the article How To Enable Developer Tab And Write VBA Source Code In Excel if you can not find the Developer tab in excel.
2. How To Count Unique Value In Excel Using VBA.
- Now input the below VBA source code in the above VBA module file to define the function Count_Unique_Value.
Function Count_Unique_Value(cellRange As Range) As Integer ' Define a variable to save a cell value. Dim cellValue As Variant ' Define a collection variable to save unique cell values. Dim uniqueValues As New Collection ' Define the variable to record whether the cell value exist in the uniqueValues or not. Dim cellValueExist As Boolean cellValueExist = False ' Define this variable to save the existing item index in the uniqueValues collection object. Dim existItemIndex As Integer existItemIndex = 0 ' The below code will make this user defined function recalculate when any cell of the worksheet caculate. ' If do not call the below code Application.Volatile, then this user defined function will only recaculate when the input parameter is changed. Application.Volatile ' Ignore errors and execute to the next line of code. On Error Resume Next ' Loop in the cell range. For Each cellValue In cellRange ' If the currnet cell value is a text value. If ActiveSheet.IsText(cellValue) Then ' Initialize the related variables. cellValueExist = False existItemIndex = 0 ' If the cell value is not empty. If IsEmpty(cellValue) = False Then ' Loop the items in the uniqueValues collection object. For Each Item In uniqueValues ' Compare the cell value (lower case) to the item (lower case) in the uniqueValues collection object. If LCase(Item) = LCase(cellValue) Then ' If the cell value exist in the uniqueValues collection object. cellValueExist = True End If existItemIndex = existItemIndex + 1 ' Exit the for loop when the cell value exist in the uniqueValues collection object. If cellValueExist Then Exit For End If Next ' If the cell value exist in the uniqueValues collection object, then remove it from the uniqueValues collection object. If cellValueExist = True Then uniqueValues.Remove (existItemIndex) ' Else add the cell value to the collection object. Else uniqueValues.Add cellValue, CStr(cellValue) End If End If End If Next ' Assign the collection object's size to the function name to return the unique value's count. Count_Unique_Value = uniqueValues.Count End Function - The above VBA function will count the unique values in the provided cell range, you can see the code comments for detailed explanation.
- To use it, input the formula =Count_Unique_Value(A2:A6) in cell B6 and press enter key, then it will display the number 3 in the cell B6.
3. How To Count Distinct Value In Excel Using VBA.
- Input the below source code in the VBA module file to define the function Count_Distinct_Value.
Function Count_Distinct_Value(cellRange As Range, ignoreBlanks As Boolean) As Integer ' Define a variable to save a cell value. Dim cellValue As Variant ' Define a collection variable to save distince cell values. Dim distinctValues As New Collection ' The below code will make this user defined function recalculate when any cell of the worksheet caculate. ' If do not call the below code Application.Volatile, then this user defined function will only recaculate when the input parameter is changed. Application.Volatile ' Ignore errors and execute to the next line of code. On Error Resume Next ' Loop in the cell range. For Each cellValue In cellRange ' If user ignore blank cell. If ignoreBlanks = True Then ' If the cell value is not empty. If IsEmpty(cellValue) = False Then ' Add the cell value to the collection object. ' key is the cell object, and the value is the cell string. distinctValues.Add cellValue, CStr(cellValue) End If Else ' If not ignore empty cell, then add the cell value to the distinct collection object. distinctValues.Add cellValue, CStr(cellValue) End If Next ' Assign the collection object's size to the function name to return the distinct value's count. Count_Distinct_Value = distinctValues.Count End Function - The above function will count the distinct value in the provided excel cell range.
- The first parameter is the excel cell range.
- The second parameter is a bool value, True means it will ignore the blank cell, False means it will not ignore the blank cell.
- Input the formula =Count_Distinct_Value(A2:A6, TRUE) in the cell C6 and press the enter key, then it will show the number 4 in the cell C6.
