Examples of User-Defined Functions

On This Page

Example 1: Calculate the Factorial of a Number

Excel VBA: Create a function named Factorial that calculates the factorial of a non-negative integer. Sample file path: Samples\ch08\Excel VBA\UDF_Factorial.xlsm.

code.vba 
Function Factorial(n As Integer) As Long
    Dim i As Integer
    Dim result As Long
    result = 1
    If n < 0 Then
        Factorial = "Invalid input(n must be non-negative)"
        Exit Function
    End If
    For i = 1 To n
        result = result * i
    Next i
    Factorial = result
End Function

Call in worksheet: =Factorial(5) → returns 120.

Python: Create a function named factorial that calculates the factorial of a non-negative integer.

code.python 
def factorial(n):
    """Return the factorial of a non-negative integer n."""
    if n < 0:
        return "Invalid input(n must be non-negative)"
    result = 1
    for i in range(1, n + 1):
        result *= i
    return result
# Call the function
print(factorial(5))  # Output: 120

Example 2: Check if a String is a Palindrome

Excel VBA: Create a function named IsPalindrome that checks if a string is a palindrome (reads the same forwards and backwards). Sample file path: Samples\ch08\Excel VBA\UDF_Palindrome.xlsm.

code.vba 
Function IsPalindrome(strInput As String) As Boolean
    Dim lenStr As Integer
    Dim i As Integer
    lenStr = Len(strInput)
    For i = 1 To lenStr \ 2
        If Mid(strInput, i, 1) <> Mid(strInput, lenStr - i + 1, 1) Then
            IsPalindrome = False
            Exit Function
        End If
    Next i
    IsPalindrome = True
End Function

Call in worksheet: =IsPalindrome("racecar") → returns TRUE; =IsPalindrome("hello") → returns FALSE.

Python: Create a function named is_palindrome that checks if a string is a palindrome.

code.python 
def is_palindrome(s):
    """Return True if the string s is a palindrome, False otherwise."""
    return s == s[::-1]
# Call the function
print(is_palindrome("racecar"))  # Output: True
print(is_palindrome("hello&quot;))    # Output: False

Example 3: Convert Celsius to Fahrenheit

Excel VBA: Create a function named CelsiusToFahrenheit that converts Celsius to Fahrenheit. Sample file path: Samples\ch08\Excel VBA\UDF_Temperature.xlsm.

code.vba 
Function CelsiusToFahrenheit(celsius As Double) As Double
    CelsiusToFahrenheit = celsius * 9 / 5 + 32
End Function

Call in worksheet: =CelsiusToFahrenheit(0) → returns 32; =CelsiusToFahrenheit(100) → returns 212.

Python: Create a function named celsius_to_fahrenheit that converts Celsius to Fahrenheit.

code.python 
def celsius_to_fahrenheit(celsius):
    """Convert Celsius to Fahrenheit."""
    return celsius * 9 / 5 + 32
# Call the function
print(celsius_to_fahrenheit(0))    # Output: 32.0
print(celsius_to_fahrenheit(100))  # Output: 212.0