Skip to content

Excel VBA User Defined Function, VBA Functions & Worksheet Functions

    User Defined Functions

    A User Defined Function performs calculations and returns a value, much like Excel’s built in worksheet functions – SUM, AVERAGE etc

    User Defined Functions can be used in an Excel worksheet or within a sub procedure.

    Example User Defined Functions

    Functions Without Arguments

    This function called CREATEDBY, when entered into a worksheet cell as =CREATEDBY() returns the following string.

    Worksheet created on [Date & Time] by [UserName]

    Function CREATEDBY() As String
'Returns the current date, time and username
Application.Volatile False
CREATEDBY = "Worksheet created on " & Now & " by " & Application.UserName
End Function

    This function called BOOKLOC , when entered into a worksheet cell as =BOOKLOC() returns the following string.

    Workbook, [Workbook Name] is saved in [Workbook Path]

    Function BOOKLOC() As String
'Returns the Workbook Name and Path
BOOKLOC = "Workbook, " & ActiveWorkbook.Name & " is saved in " & ActiveWorkbook.Path
End Function

    This function called TODAYSDATE, when entered into a worksheet cell as =TODAYSDATE() returns the following string.

    Today’s date is Thursday 09 March 2017

    Function TODAYSDATE() As String
'Returns Today's Dates in format: Today's date is Monday 1 January 2011
TODAYSDATE = "Today's date is " & Format(Date, "dddd dd mmmm yyyy")
End Function

    These functions can also be used within sub procedures, for example…

    Sub WelcomeMessage()
Range("A1") = "Welcome to " & ActiveSheet.Name
Range("A2") = CREATEDBY
Range("A3") = BOOKLOC
Range("A4") = TODAYSDATE
End Sub

    Functions with Arguments

    Account Status Example

    In this Function, we have two Arguments: Budget and Actual.  When functions contain arguments they are listed within the function’s brackets:

    STATUS(Budget As Double, Actual As Double) As String

    The function is designed for a specific worksheet where we need to quickly workout the status on each account – “OVERBUDGET” or “ON BUDGET”.  See the relevant data displayed beneath the code.

    Function STATUS(Budget As Double, Actual As Double) As String
If Actual > Budget Then
    STATUS = "OVERBUDGET"
Else: STATUS = "ON BUDGET"
End If
End Function

    Unfortunately Excel doesn’t display a function screen tip for User Defined Functions, so you have to use Insert Function if you can’t remember the order of the arguments.  The functions are listed in the User Defined category.

    The Functions Arguments dialog will display the arguments for you.

    The function can also be used in a sub procedure as shown below.

    Sub CalculateStatus()
Dim AreaCell As Range, AreaField As Range, StatusCell As Range
Set AreaField = Range("A2", Range("A2").End(xlDown))
For Each AreaCell In AreaField
Set StatusCell = AreaCell.Offset(0, 5)
    StatusCell = STATUS(StatusCell.Offset(0, -2), StatusCell.Offset(0, -1))
Next AreaCell
End Sub

    Overdue Invoice Fee Example

    In this example, the function calculates the overdue fee amount on overdue invoices.

    Function OVERDUEFEE(Due_date As Date, Invoice_amt As Currency) As Currency
If Due_date < Date Then
    OVERDUEFEE = (Date - Due_date) * Invoice_amt * 0.02
    Else: OVERDUEFEE = 0
    End If
End Function

    The function is specifically designed for calculating the fee in the worksheet shown below.

    Instead of using the function within a worksheet, we could use the function within a sub procedure.  For example…

    Sub CalculateFees()
Dim FeeCell As Range, FeeField As Range
Set FeeField = Range("D2:D6")
For Each FeeCell In FeeField
    FeeCell = OVERDUEFEE(FeeCell.Offset(0, -1), FeeCell.Offset(0, -2))
Next FeeCell
End Sub

    Discounted Total Example

    With this function we can apply the correct product discount based on which category has been assigned to the product.  The relevant data is displayed beneath the code.

    Function DISCOUNTEDTOTAL(Category As String, Unit_Price As Currency, Qty As Byte) As Single
    Select Case Category
        Case "A": DISCOUNTEDTOTAL = Unit_Price * Qty * 0.93
        Case "B": DISCOUNTEDTOTAL = Unit_Price * Qty * 0.91
        Case "C": DISCOUNTEDTOTAL = Unit_Price * Qty
        Case "D": DISCOUNTEDTOTAL = Unit_Price * Qty * 0.88
        Case "E": DISCOUNTEDTOTAL = Unit_Price * Qty * 0.75
    End Select
End Function

    We might also use the function in a sub procedure in the following way…

    Sub CalculateDiscountedTotals()
Dim ProductCell As Range, ProductField As Range, DiscountedTotalCell As Range
Dim CatCell As Range, UnitPriceCell As Range, QtyCell As Range
Set ProductField = Range("A2:A11")

For Each ProductCell In ProductField
Set CatCell = ProductCell.Offset(0, 1)
Set UnitPriceCell = ProductCell.Offset(0, 2)
Set QtyCell = ProductCell.Offset(0, 3)
Set DiscountedTotalCell = ProductCell.Offset(0, 4)
    DiscountedTotalCell = DISCOUNTEDTOTAL(CatCell.Value, UnitPriceCell.Value, QtyCell.Value)
Next ProductCell

End Sub

    Function With Optional Argument

    As you probably know, some Excel functions, have optional arguments.  For example LEFT as two arguments: text and num_chars.  The second argument is optional, if you leave it out the function still works and LEFT returns the first character of the text. To specify an optional argument within a User Defined Function placed the keyword Optional infront of the argument name as shown below.

    FunctionName(Optional ArgumentName as DataType) as DataType

    Functions Arguments That Contain an Array

    Function arguments can contain a array of values, useful if you want to perform a calculation on a  range. With the following function we can specify a range of cells in the Sales argument.  The function would then calculate a commission value.

    Function COMMISSION(Sales) As Double
    COMMISSION = WorksheetFunction.Sum(Sales) * 0.08
End Function

    With this next example, the function adds up cells with a specified cell background colour.  With the first argument you select a cell (formatted with a cell colour) and with the second argument you select the range of cells you want to add up.

    Function SUMBASEDONCELLCOLOUR(ColourToAddUp As Range, RangeToAddUp As Range)
Dim rg As Range
Dim SelectedColour As Integer
Dim Total As Double
SelectedColour = ColourToAddUp.Interior.ColorIndex
For Each rg In RangeToAddUp
        If rg.Interior.ColorIndex = SelectedColour Then
            Total = WorksheetFunction.Sum(rg) + Total
        End If
Next rg
SUMBASEDONCELLCOLOUR = Total
End Function

    Adding Descriptions to User Defined Functions

    You can add descriptions to your User Defined Functions using the MacroOptions method as shown below.

    Sub ArgumentDescriptions()

Application.MacroOptions Macro:="STATUS", _
Description:="Calculates whether an account is over budget or on budget", _
ArgumentDescriptions:=Array("The budgeted amount specified for the _ account","The actual amount spent on account.")

End Sub

    A description can be applied to the macro itself as well as the individual arguments.  The code above has added the descriptions shown in the dialog box below.

    You only need to run the procedure once to permanently store the descriptions in the workbook.

    VBA Functions & Worksheet Functions

    Many Excel functions have an equivalent VBA function, for example LEFT, LEN, DATEDIFF, YEAR, ROUND, AND, OR, and PMT.  Some Excel functions have a VBA equivalent with a different name, for example the equivalent function for Excel’s TODAY function in VBA is DATE.

    There are also functions in VBA that don’t exist in Excel, for example CHDIR which can be used to change the current directory folder.

    A good resource containing a list of VBA Functions can be found here.

    https://www.techonthenet.com/excel/formulas/index_vba.php

    You can access nearly all Worksheet functions in VBA by just typing…

    WorksheetFunction.

    …followed by the function you want to employ.  The IntelliSense list will display the available functions.

    Example Procedure Using Both VBA & Worksheet Functions

    This procedure uses a mixture of VBA functions and Worksheet functions to evaluate the type of data contained in a cell and then format the cell with a different interior colour. Not all Worksheet logical functions have a VBA equivalent and visa versa. For example there is no Worksheet function equivalent to IsDate and there is no VBA function equivalent to IsText.

    Sub VBAFunctions()
Dim rg As Range
Dim list As Range
Set list = Range("A1:A8")

For Each rg In list
    Select Case True
        Case IsDate(rg)
        rg.Interior.Color = vbBlue
        rg.Font.Color = vbWhite
        Case IsError(rg)
        rg.Interior.Color = vbCyan
        Case IsEmpty(rg)
        rg.Interior.Color = vbBlack
        Case Is = WorksheetFunction.IsFormula(rg)
        rg.Interior.Color = vbRed
        rg.Font.Color = vbWhite
        Case Is = WorksheetFunction.IsText(rg)
        rg.Interior.Color = vbGreen
        Case IsNumeric(rg)
        rg.Interior.Color = vbYellow
    End Select
Next rg

End Sub

    The code above relates to the data shown below.

    Placing Formulas in Cells

    One thing you will notice with VBA functions and Worksheet Functions is that no formula is actually entered in worksheet cells.

    So for example, take this VLOOKUP example.

    If I use the VLOOKUP Worksheet Function in a procedure as shown below, although the VLOOKUP is performed correctly, no actual formulas are entered into cells, which means if the data changes I have to manually run the procedure again or place it in a worksheet event module.

    Sub StudentGradesWithWorksheetFunction()
Dim MarkCell As Range, MarkField As Range, GradeCell As Range
Dim GradesTable As Range
Set GradesTable = Range("E2:F9")
Set MarkField = Range("B2:B11")
        
For Each MarkCell In MarkField
    Set GradeCell = MarkCell.Offset(0, 1)
    GradeCell = WorksheetFunction.VLookup(MarkCell, GradesTable, 2)
Next MarkCell

End Sub

    To actually enter formulas into cells, write the formula into code as if it is a text value – see below.

    Sub StudentGradesWithFormulaInCell()
Range("C2") = "=VLOOKUP(B2,E$2:F$9,2)"
Range("C2").Copy Range("C3:C11")
End Sub