How to Remove Duplicate Values in a Range Within a Bigger Range in Excel Using VBA

A VBA function is a user-defined function that can be used in Excel formulas to perform custom calculations or operations. A VBA function can take one or more arguments as inputs and return a single output value. For example, the following VBA function takes a string argument and returns the length of the string:

Public Function StringLength (SomeString As String) As Integer
  StringLength = Len (SomeString)
End Function

To use this function in an Excel formula, you can enter =StringLength (A1) in any cell, where A1 is a cell that contains a string value.

However, a VBA function can only access the values of the cells that are passed as arguments. It cannot modify or read the values of other cells in the worksheet. For example, the following VBA function tries to multiply the value of cell B1 by the value of cell C1, but it will not work as expected:

Public Function Example (SomeCell As String) As Integer
  Example = Worksheet.Range ("B1").Value * Worksheet.Range ("C1").Value
End Function

If you enter =Example (A1) in any cell, the function will not update automatically when the values of B1 or C1 change. It will only update when the value of A1 changes, because that is the only argument that the function depends on.

Procedures

To make a VBA function update based on cells other than those in the arguments, you need to use a workaround that involves using public variables and worksheet events. The basic idea is to store the values of the cells that you want to monitor in public variables, and then use the Worksheet_Calculate event to update the function whenever those variables change. For example, the following code modifies the previous VBA function to make it update based on the values of B1 and C1:

Public B1Value As Variant ' Declare a public variable to store the value of B1
Public C1Value As Variant ' Declare a public variable to store the value of C1

Public Function Example (SomeCell As String) As Integer
  Example = B1Value * C1Value ' Use the public variables instead of the cell references
End Function

Private Sub Worksheet_Calculate ()
  If B1Value <> Range ("B1").Value Or C1Value <> Range ("C1").Value Then ' Check if the values of B1 or C1 have changed
    B1Value = Range ("B1").Value ' Update the public variable for B1
    C1Value = Range ("C1").Value ' Update the public variable for C1
    Application.CalculateFull ' Recalculate the whole workbook to update the function
  End If
End Sub

The Worksheet_Calculate event is triggered whenever any cell in the worksheet is recalculated. The code inside this event checks if the values of B1 or C1 have changed, and if so, updates the public variables and recalculates the whole workbook. This way, the function will update whenever B1 or C1 change, regardless of the argument that is passed to the function.

Explanation

To understand how this workaround works, let’s go through the steps in detail.

Step 1: Declare public variables

The first step is to declare public variables to store the values of the cells that you want to monitor. Public variables are variables that can be accessed from any module or procedure in the VBA project. To declare a public variable, you need to use the Public keyword followed by the variable name and the data type. For example, the following code declares two public variables, B1Value and C1Value, as variants:

Public B1Value As Variant ' Declare a public variable to store the value of B1
Public C1Value As Variant ' Declare a public variable to store the value of C1

A variant is a data type that can hold any kind of value, such as numbers, strings, dates, etc. You can use variants when you are not sure what kind of data the cells will contain, or when you want to avoid type mismatch errors.

You can declare public variables in any standard module, but it is recommended to declare them at the top of the module, before any procedures or functions.

Step 2: Modify the VBA function

The next step is to modify the VBA function to use the public variables instead of the cell references. For example, the following code modifies the previous VBA function to make it use B1Value and C1Value:

Public Function Example (SomeCell As String) As Integer
  Example = B1Value * C1Value ' Use the public variables instead of the cell references
End Function

This way, the function will return the product of the values stored in the public variables, which will be updated by the worksheet event.

Step 3: Write the worksheet event

The final step is to write the worksheet event that will update the public variables and the function whenever the values of the cells change. A worksheet event is a special procedure that is triggered by a specific action or condition in the worksheet, such as opening, closing, calculating, changing, etc. You can write worksheet events in the code module of the worksheet object, which you can access by double-clicking on the worksheet name in the Project Explorer window.

The worksheet event that we need is the Worksheet_Calculate event, which is triggered whenever any cell in the worksheet is recalculated. To write this event, you need to select Worksheet from the left drop-down menu at the top of the code window, and then select Calculate from the right drop-down menu. This will create a blank procedure with the following syntax:

Private Sub Worksheet_Calculate ()

End Sub

Inside this procedure, you need to write the code that will check if the values of B1 or C1 have changed, and if so, update the public variables and recalculate the whole workbook. For example, the following code does this:

Private Sub Worksheet_Calculate ()
  If B1Value <> Range ("B1").Value Or C1Value <> Range ("C1").Value Then ' Check if the values of B1 or C1 have changed
    B1Value = Range ("B1").Value ' Update the public variable for B1
    C1Value = Range ("C1").Value ' Update the public variable for C1
    Application.CalculateFull ' Recalculate the whole workbook to update the function
  End If
End Sub

The code uses the If statement to compare the values of the public variables and the cells, using the <> operator, which means not equal to. If either of them is different, it means that the cells have changed, and the code executes the following statements:

It assigns the new values of the cells to the public variables, using the = operator, which means assignment.
It calls the Application.CalculateFull method, which recalculates the whole workbook, including the VBA function.

This way, the function will update whenever B1 or C1 change, regardless of the argument that is passed to the function.

Example

To illustrate how this workaround works, let’s consider an example scenario. Suppose you have the following data in your worksheet:

Table

A	B	C	D
SomeCell	B1	C1	Result
Hello	2	3	=Example (A2)
World	4	5	=Example (A3)

The cells D2 and D3 contain the VBA function Example, which takes the values of A2 and A3 as arguments, but returns the product of the values of B1 and C1. The cells B1 and C1 contain the values 2 and 3, respectively. The public variables B1Value and C1Value are initialized with the same values.

Now, if you change the value of B1 to 6, the following things will happen:

The Worksheet_Calculate event will be triggered, because a cell in the worksheet has changed.
The code inside the event will check if the values of B1 or C1 have changed, and find that B1Value (2) is not equal to B1 (6).
The code will update the value of B1Value to 6, and leave the value of C1Value as 3.
The code will recalculate the whole workbook, which will update the values of D2 and D3 to 18, which is the product of 6 and 3.

Similarly, if you change the value of C1 to 7, the following things will happen:

The Worksheet_Calculate event will be triggered, because a cell in the worksheet has changed.
The code inside the event will check if the values of B1 or C1 have changed, and find that C1Value (3) is not equal to C1 (7).
The code will update the value of C1Value to 7, and leave the value of B1Value as 6.
The code will recalculate the whole workbook, which will update the values of D2 and D3 to 42, which is the product of 6 and 7.

However, if you change the value of A2 to “Hi”, the following things will happen:

The Worksheet_Calculate event will be triggered, because a cell in the worksheet has changed.
The code inside the event will check if the values of B1 or C1 have changed, and find that B1Value (6) and C1Value (7) are equal to B1 (6) and C1 (7).
The code will not update the values of the public variables, and will not recalculate the whole workbook, because there is no need to do so.
The values of D2 and D3 will remain as 42, which is the product of 6 and 7.

This way, the function will not update based on the value of A2, because it is not one of the cells that the function depends on.

Other approaches

The workaround that we have discussed is one of the possible ways to make a VBA function update based on cells other than those in the arguments. However, it is not the only way, and it may have some limitations or drawbacks. For example:

It requires declaring public variables and writing worksheet events, which may add complexity and maintenance overhead to the VBA project.
It recalculates the whole workbook whenever the values of the monitored cells change, which may affect the performance and efficiency of the workbook, especially if it contains many formulas or functions.
It may not work as expected if the workbook contains multiple worksheets or multiple instances of the same function, because the public variables and the worksheet events are shared across the workbook.

Therefore, depending on the specific situation and requirements, you may want to consider other approaches to achieve the same goal. Some of the alternative approaches are:

Using the Application.Volatile method, which makes the function volatile, meaning that it will recalculate whenever any cell in the workbook changes. However, this may also affect the performance and efficiency of the workbook, and it may not be suitable for functions that require a lot of computation or resources.
Using the Worksheet_Change event, which is triggered whenever a cell in the worksheet is changed by the user or by an external link. This may be more efficient than the Worksheet_Calculate event, because it only updates the function when the monitored cells are changed by the user or by an external link, not by a formula or a function. However, this may not work if the monitored cells are changed by a formula or a function, or if the function is used in a different worksheet.
Using the INDIRECT function, which returns the value of a cell or a range that is specified by a text string. This way, you can pass the cell references as text strings to the function, and the function will use the INDIRECT function to access the values of those cells. For example, the following code modifies the previous VBA function to use the INDIRECT function:

Public Function Example (SomeCell As String, B1Cell As String, C1Cell As String) As Integer
  Example = Worksheet.Range (INDIRECT (B1Cell)).Value * Worksheet.Range (INDIRECT (C1Cell)).Value
End Function

To use this function in an Excel formula, you can enter =Example (A2, "B1", "C1") in any cell, where A2 is a cell that contains a string value, and “B1” and “C1” are text strings that specify the cell references. The function will update automatically when the values of B1 or C1 change, because the INDIRECT function is volatile by default.

However, this approach may also have some limitations or drawbacks, such as:

It requires passing the cell references as text strings to the function, which may be inconvenient or error-prone, especially if the cell references are dynamic or complex.
It may not work if the cell references are invalid or refer to cells in a different workbook or worksheet, because the INDIRECT function will return an error value in those cases.
It may affect the performance and efficiency of the workbook, because the INDIRECT function is volatile by default, and it may cause unnecessary recalculations of the function.

These are some of the possible alternative approaches to make a VBA function update based on cells other than those in the arguments. You may need to experiment with different approaches and find the one that best suits your needs and preferences.