Writing Your Own Functions In VBA This page describes how to write your own worksheet functions in VBA.
While Excel provides a plethora of built-in functions, especially so if you include functions in the Analysis Took Pack (in Excel 2007, the functions that used to be in the ATP are now native Excel functions) you may find it useful to create your own custom function for things that Excel cannot (easily) do with the built-in functions.
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 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 : Our function is now ready to be used. Volatile tells us that the application is volatile (which is the case for the IF function).
I found this excellent UDF in David Hager´s Excel Experts E-letter (EEE) on J Walkenbach’s website.
If you have built an Add-In to contain your Excel custom functions you might have discovered that, although the functions work fine in an Excel worksheet, you can't use them in your VBA procedures in other workbooks. When you load an Excel Add-In its functionality immediately becomes available to all your workbooks. In my procedure I want to use the Remove Spaces function that I created a while ago and saved in my Martin's Functions Add-In which is currently installed in my copy of Excel. The Visual Basic Editor is behaving as if the function doesn't exist, but I know it does and I can see it if I look at the code inside my Add-In.
Add-Ins are intended primarily for adding extra functionality to your workbooks.
If you attempt to change anything, the function will terminate immediately and return a Doing this will lead only to confusion, so just because it is possible doesn't mean you should do it. Do not give the same name to both a module and a function (regardless of whether that module contains that function). You can call a UDF that is contained in another (open) workbook by using the workbook name in the formula. If a function is defined in an Add-In (either an XLA or an Automation Add-In; see this page for information about writing Automation Add-Ins in VB6), you don't need to include the name of the Add-In file.
The function name alone is sufficient for calling a function in an Add-In. This will almost certainly return an incorrect result.