VBA Course: Create a Custom Function
In this example, we will create a custom IF function that can be used on a calculation worksheet like any other function.
To display a value (B2) if a cell (C2) has the value ("YES"), we could use the IF function with this formula
Our goal in this case is to create a function that can do this
=IF(C2 has a green background,B2,0) which we would write as follows:
Develop the custom function
Start by creating the function:
Function IF_GREEN(paid As Range, amount) End Function
paid As Range: the cell to test
amount: the value to insert if TRUE
In this case, if the test is FALSE, the value will be 0 every time, so there's no reason to have an argument for that.
To test to see whether the color is correct, you can use a cell that contains the color that you want as a point of reference:
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 number of the color that we're interested in here:
Function IF_GREEN(paid As Range, amount) green_color = 5296274 'Green End Function
To figure out a cell's background color number, select the cell and run this macro:
Sub test_color() MsgBox ActiveCell.Interior.color End Sub
Now all we have to do is test the color number 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
Our function is now ready to be used.
Application.Volatile tells us that the application is volatile (which is the case for the IF function). This means it should be recalculated each time a value is changed. For example, if you modify any of the amounts (or any other cell, in fact), the function will be recalculated and the correct amount will be displayed.
However, changing the background color does not trigger this recalculation. To force recalculation of the values, you can select an empty cell and press "Delete" or add an "Refresh" button that will cause everything to be updated when it is clicked:
Sub refresh_macro() Application.Calculate End Sub
Here is another way to write 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 that contains this example here: if_green_function.zip