VBA-Lesson 14.2. Creating a custom function

In this example, we will create a custom IF function that can be used in calculations on a worksheet, similar to other Excel functions.

To display the value of (B2) if cell (C2) has a value of ("YES"), we could use the IF function in the formula =IF(C2= "YES",B2,0):

Our goal in this case is to create a function that can do this =IF(C2 has a green background,B2,0) and that we would write like : =IF_GREEN(C2,B2):

Creating your own function

Let's start creating a function:

Function IF_GREEN(paid As Range, amount)
     
End Function

Arguments:

• paid As Range : the cell we will check;
• amount : the value we will insert if TRUE.

In this case, if the check returns FALSE, the value will always be 0, so there is no reason to specify an argument for it.

To check if the color is correct, you can use the cell that contains the color you need:

Function IF_GREEN(paid As Range, amount)

      green_color = Sheets("Sheet1").Range("K1").Interior.color

End Function

But to avoid having to use another cell for this purpose, we'll use the color number we need here:

Function IF_GREEN(paid As Range, amount)

      green_color = 5296274 'Green

End Function

To determine the background color number of a cell, select the cell and run this macro:

Sub test_color()
      MsgBox ActiveCell.Interior.color
End Sub

Now, all we need to do is check the color of the cell using IF:

Function IF_GREEN(paid As Range, amount)
     
      Application. Volatile
     
      green_color = 5296274 'Green
    
      If paid.Interior.color = green_color Then 'IF TRUE
         IF_GREEN = amount
      Else 'IF FALSE
         IF_GREEN = 0
      End If
     
End Function

Now our function is ready to use.

Application.Volatile tells us that the application is volatile (as is the case with the IF function). This means that it must be recalculated every time the value changes. For example, if you change any of the amounts (or indeed any other cell), this function will be recalculated and the correct amount will be displayed.

However, changing the fill of a cell is not an event that triggers a recalculation. To force Excel to recalculate the values, you can select an empty cell and click Delete, or add a Refresh button that will refresh everything when you click it:

Sub refresh_macro()
      Application.Calculate
End Sub

In addition

Below is another way of writing the same function:

Function IF_GREEN(paid As Range, amount)
      Application. Volatile
      IF_GREEN = 0 'IF FALSE
      If paid.Interior.color = 5296274 Then IF_GREEN = amount 'IF TRUE
End Function

You can download the Excel file containing this example by clicking here.

Articles on the topic: