VBA Course: Creating a Custom Function

For this example, we will create a custom IF function that can be used on a worksheet like any other Excel function.

To display the value from column B if the cell in column C contains YES, we can use the formula =IF(C2="YES",B2,0):


if function create custom

The goal here is to create a function that can do this =IF(C2 has a green background, B2, 0) and we will write it like this: =IF_GREEN(C2, B2):

if color function create custom

Develop the custom function

Start by creating your function:

Function IF_GREEN(cellColor As Range, cellValue As Range)
    
End Function

The arguments:

In this case, if the test is FALSE, the value will be 0 every time, so a third argument has not been added.

To check if the color is correct, you can use a cell that has the correct color as a comparison point:

Function IF_GREEN(cellColor As Range, cellValue As Range)
	
    color = Sheets("Sheet1").Range("K1").Interior.Color

End Function

But to avoid depending on a cell, we will directly use the number of the desired color:

Function IF_GREEN(cellColor As Range, cellValue As Range)
	
    color = 5296274 'Green

End Function

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

Sub test()

    MsgBox ActiveCell.Interior.Color

End Sub
color number create custom function

Now we just need to test the color with an If statement:

Function IF_GREEN(cellColor As Range, cellValue As Range)
    
    Application.Volatile
    
    If cellColor.Interior.Color = 5296274 Then 'If TRUE
        IF_GREEN = cellValue
    Else 'If FALSE
        IF_GREEN = 0
    End If
    
End Function

The function is now ready to be used.

Application.Volatile indicates that the function is volatile (just like the IF function), which means it needs to be recalculated whenever a value changes. For example, if you modify one of the amounts (or any other cell), the function will be recalculated and display the correct amount.

However, simply changing the background color does not trigger this update. To recalculate the values without waiting, you can, for example, press Delete while selecting an empty cell or add a Refresh button to recalculate everything with one click:

Sub refresh()

    Application.Calculate

End Sub
if green function create custom

You can download the file used in this example: if-green-function.xlsm

Conclusion

The VBA course ends here, hoping that it will help you in your future developments.

If you enjoyed this course, you have the option to download the PDF of the entire course. This is an optional paid option that helps support the site and the development of new content.

To go further, you can find other examples of VBA code in VBA Tips and examples of using VBA functions in VBA Functions.