[Solved] Custom Function (UDF)

[Badriah]

Hello,

I've created a custom VBA function that returns the square root of a number plus 10. Here's my function:

Function RootPlusTen(x As Double) As Double
    RootPlusTen = Sqr(x) + 10
End Function

I’d like to understand more about how UDFs work. Here are my questions:

1. Why do we use Function instead of Sub when creating a custom formula?
2. How can I modify this function so it returns an error message if the input is text or empty?
3. Is it possible for this function to call another macro or function inside it?

Appreciate your help!

 

[shamimarita]

Hello Badriah,

Great job on creating your first User Defined Function (UDF)! Let me address your questions one by one:

1. Why use Function instead of Sub for custom formulas?
In VBA, Function procedures return a value, which is necessary for creating custom Excel formulas. On the other hand, Sub procedures do not return a value, so they are used for tasks like automating actions (e.g., formatting cells, copying data) rather than performing calculations within worksheet cells.
Since you're returning Sqr(x) + 10, you correctly used a Function.

2. How to return an error if the input is text or empty?
You can use the IsNumeric() and IsEmpty() functions to validate input. Here's a modified version:

Function RootPlusTen(x As Variant) As Variant
    If IsEmpty(x) Or Not IsNumeric(x) Then
        RootPlusTen = CVErr(xlErrValue)
    ElseIf x < 0 Then
        RootPlusTen = CVErr(xlErrNum)
    Else
        RootPlusTen = Sqr(x) + 10
    End If
End Function
CVErr(xlErrValue) returns the #VALUE! error for invalid input.

CVErr(xlErrNum) returns #NUM! if the number is negative (since the square root of negative numbers isn't real in VBA).

3. Can a UDF call another macro or function?
Yes, a UDF can call another function, including custom or built-in ones. However, UDFs should not call Sub procedures that change the workbook, like formatting or writing to cells, because Excel may block them in worksheet cells.

For example:

Function RootPlusTen(x As Variant) As Variant
    RootPlusTen = AddTen(Sqr(x))
End Function

Function AddTen(y As Double) As Double
    AddTen = y + 10
End Function

You might find this article helpful:
How to Create and Use User-Defined Functions in Excel VBA
It explains the basics of UDFs with practical examples.