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)
:
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)
:
Develop the custom function
Start by creating your function:
Function IF_GREEN(cellColor As Range, cellValue As Range)
End Function
The arguments:
- cellColor As Range: the cell to test the color
- cellValue As Range: the cell containing the value
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
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
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.