Sign In
Get Started

VBA Custom Functions for Excel Automation

by | Excel

Excel Content Article

Custom Date and Time Functions in VBA for Excel

This guide provides an in-depth look at creating custom date and time functions in VBA to enhance Excel automation. We’ll describe how to create these functions, which you can directly copy and apply in your Excel VBA environment.

Introduction

Excel’s built-in date and time functions are powerful but not always sufficient for more complex automation needs. Custom VBA functions can help bridge this gap by providing more customized functionality. This section will cover how to set up and implement some basic custom date and time functions in VBA.

Sales Now On Advertisement

Setup Instructions

  1. Open Excel.
  2. Press Alt + F11 to open the VBA editor.
  3. Insert a new module by clicking Insert > Module.

Example Functions

1. NextDayOfWeek

This function calculates the date of the next specified day of the week from a given date.

Function NextDayOfWeek(startDate As Date, targetDay As String) As Date
    ' List of days of the week
    Dim daysOfWeek As Variant
    daysOfWeek = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
    
    ' Get the current day of the week and target day index
    Dim currentDayIndex As Integer
    Dim targetDayIndex As Integer
    Dim i As Integer
    
    currentDayIndex = Weekday(startDate, vbSunday) - 1

    For i = LBound(daysOfWeek) To UBound(daysOfWeek)
        If StrComp(daysOfWeek(i), targetDay, vbTextCompare) = 0 Then
            targetDayIndex = i
            Exit For
        End If
    Next i
    
    ' Calculate the difference in days
    Dim daysDifference As Integer
    If targetDayIndex >= currentDayIndex Then
        daysDifference = targetDayIndex - currentDayIndex
    Else
        daysDifference = 7 - (currentDayIndex - targetDayIndex)
    End If
    
    ' Return the next target day
    NextDayOfWeek = startDate + daysDifference
End Function

2. DaysBetweenDates

This function calculates the number of days between two dates.

Function DaysBetweenDates(startDate As Date, endDate As Date) As Long
    DaysBetweenDates = DateDiff("d", startDate, endDate)
End Function

3. AddBusinessDays

This function adds a specified number of business days to a given date.

Function AddBusinessDays(startDate As Date, numBusinessDays As Long) As Date
    Dim currentDate As Date
    Dim businessDaysAdded As Long
    currentDate = startDate
    businessDaysAdded = 0
    
    Do While businessDaysAdded < numBusinessDays
        currentDate = currentDate + 1
        If Weekday(currentDate, vbMonday) <= 5 Then ' Monday to Friday are considered business days
            businessDaysAdded = businessDaysAdded + 1
        End If
    Loop
    
    AddBusinessDays = currentDate
End Function

Usage Example

Back in Excel, you can use these functions just like any other Excel function. Assume you have startDate in cell A1 and you want to find the next Monday:

=NextDayOfWeek(A1, "Monday")

Or to add 10 business days to a given date in cell A1:

=AddBusinessDays(A1, 10)

Conclusion

These custom VBA functions can significantly enhance your date and time manipulation capabilities within Excel. By extending Excel’s functionality, you can perform more complex operations and automate your tasks more efficiently. Copy and apply these VBA codes in your Excel environment to get started.

Advanced String Manipulation in VBA

1. Custom Function: ReverseString

This custom function reverses a given string.

Power BI Tools Advertisement 
Public Function ReverseString(inputString As String) As String
    Dim reversedString As String
    Dim i As Integer
    
    reversedString = ""
    For i = Len(inputString) To 1 Step -1
        reversedString = reversedString & Mid(inputString, i, 1)
    Next i
    
    ReverseString = reversedString
End Function

2. Custom Function: RemoveWhitespace

This custom function removes all whitespaces from the given string.

Public Function RemoveWhitespace(inputString As String) As String
    Dim cleanedString As String
    Dim i As Integer

    cleanedString = ""
    For i = 1 To Len(inputString)
        If Mid(inputString, i, 1) <> " " Then
            cleanedString = cleanedString & Mid(inputString, i, 1)
        End If
    Next i
    
    RemoveWhitespace = cleanedString
End Function

3. Custom Function: ExtractNumbers

This custom function extracts all numeric characters from the given string.

Public Function ExtractNumbers(inputString As String) As String
    Dim numbersOnly As String
    Dim i As Integer
    Dim currentChar As String

    numbersOnly = ""
    For i = 1 To Len(inputString)
        currentChar = Mid(inputString, i, 1)
        If IsNumeric(currentChar) Then
            numbersOnly = numbersOnly & currentChar
        End If
    Next i
    
    ExtractNumbers = numbersOnly
End Function

4. Custom Function: CountOccurrences

This function counts the number of occurrences of a specific substring within a given string.

Public Function CountOccurrences(mainString As String, substring As String) As Integer
    Dim count As Integer
    Dim pos As Integer
    
    count = 0
    pos = InStr(mainString, substring)
    Do While pos > 0
        count = count + 1
        pos = InStr(pos + Len(substring), mainString, substring)
    Loop
    
    CountOccurrences = count
End Function

5. Custom Function: SplitStringByDelimiter

This function splits a given string by a specified delimiter and returns a VBA array.

Public Function SplitStringByDelimiter(inputString As String, delimiter As String) As Variant
    Dim result() As String
    
    result = Split(inputString, delimiter)
    
    SplitStringByDelimiter = result
End Function

6. Custom Function: ReplaceSubstring

This function replaces all occurrences of a specific substring with another substring.

Public Function ReplaceSubstring(mainString As String, oldSubstring As String, newSubstring As String) As String
    ReplaceSubstring = Replace(mainString, oldSubstring, newSubstring)
End Function

These functions can be accessed in Excel by entering formulas in cells, similar to how you would use built-in Excel functions. For example, to reverse the string in cell A1, you would enter =ReverseString(A1) in another cell.

Dynamic Data Validation in Excel Using VBA

To implement dynamic data validation using VBA in Excel, you can create a custom function that validates cell values based on specific criteria. Here’s an example of how you can achieve this:

Step 1: Open the VBA Editor

Press Alt + F11 to open the VBA editor in Excel.

Step 2: Create a New Module

Insert a new module by right-clicking on any existing module or directly in the Modules folder and selecting Insert > Module.

Step 3: Write the Custom VBA Function

Add the following code to the newly created module to perform dynamic data validation:

Function ValidateData(cell As Range, criteria As String) As Boolean
    ' Check if the cell matches the specified criteria
    Select Case criteria
        Case "PositiveInteger"
            If IsNumeric(cell.Value) And cell.Value > 0 And cell.Value = Int(cell.Value) Then
                ValidateData = True
            Else
                ValidateData = False
            End If
        Case "NonEmptyString"
            If IsString(cell.Value) And Len(cell.Value) > 0 Then
                ValidateData = True
            Else
                ValidateData = False
            End If
        Case "DateFormat"
            If IsDate(cell.Value) Then
                ValidateData = True
            Else
                ValidateData = False
            End If
        ' Add more criteria as needed
        Case Else
            ValidateData = False
    End Select
End Function

Function IsString(input As Variant) As Boolean
    IsString = TypeName(input) = "String"
End Function

Step 4: Applying the Dynamic Data Validation

To use this custom function for data validation, follow these steps:

  1. Select the cells where you want to apply the validation.
  2. Go to the Data tab on the ribbon and select Data Validation.
  3. In the Data Validation dialog box,

    • Select “Custom” from the “Allow” drop-down menu.

    • In the “Formula” field, enter a formula that calls the ValidateData function, like:


    =ValidateData(A1, "PositiveInteger")

    Adjust the range A1 to match the first cell in your selection, and change "PositiveInteger" to the appropriate validation criteria.


Step 5: Error Handling

Ensure an error message is set up in the Data Validation dialog to display a user-friendly message when validation fails. This can help the user correct their input according to the validation rules.

Conclusion

By using the provided VBA functions, you can dynamically validate data in Excel based on different criteria. Modify the criteria and add more cases to the Select Case structure as needed for your specific validation needs.

Enhanced Data Sorting and Filtering in VBA

This section will cover:

  1. Creating reusable VBA functions for sorting data.
  2. Implementing custom filters in VBA.

1. Custom Sort Function

To create a custom sort function, we will define a procedure that performs sorting based on specified columns and order.

Data Mentor Advertisement 
Sub CustomSort(sheetName As String, sortRange As String, keyRange As String, order As XlSortOrder)
    ' sheetName: Name of the sheet where sorting needs to be applied
    ' sortRange: The range of cells to be sorted in A1 notation
    ' keyRange: The column or range based on which sorting is applied
    ' order: Sorting order (xlAscending or xlDescending)
    
    With ThisWorkbook.Sheets(sheetName).Sort
        .SortFields.Clear
        .SortFields.Add Key:=ThisWorkbook.Sheets(sheetName).Range(keyRange), Order:=order
        .SetRange ThisWorkbook.Sheets(sheetName).Range(sortRange)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Example Usage

Sub SortData()
    ' Sort data in Sheet1 from A2 to D100 based on column A (ascending order)
    CustomSort "Sheet1", "A2:D100", "A2:A100", xlAscending
End Sub

2. Custom Filter Function

To create a custom filtering function, we will define a procedure that applies a filter based on specified criteria.

Sub CustomFilter(sheetName As String, filterRange As String, field As Integer, criteria1 As String, Optional criteria2 As String = "")
    ' sheetName: Name of the sheet where filter needs to be applied
    ' filterRange: The range of cells to be filtered in A1 notation
    ' field: The column number on which filtering is applied
    ' criteria1: The primary filter criteria
    ' criteria2: The secondary filter criteria (optional)
    
    With ThisWorkbook.Sheets(sheetName).Range(filterRange)
        If criteria2 = "" Then
            .AutoFilter Field:=field, Criteria1:=criteria1
        Else
            .AutoFilter Field:=field, Criteria1:=criteria1, Operator:=xlAnd, Criteria2:=criteria2
        End If
    End With
End Sub

Example Usage

Sub FilterData()
    ' Filter data in Sheet1 from A1 to D100 where the second column equals "Approved"
    CustomFilter "Sheet1", "A1:D100", 2, "Approved"
End Sub

Combining Sort and Filter

You can also combine sorting and filtering in a single subroutine.

Sub SortAndFilter(sheetName As String, sortRange As String, keyRange As String, order As XlSortOrder, filterRange As String, field As Integer, criteria1 As String, Optional criteria2 As String = "")
    ' Apply filter
    CustomFilter sheetName, filterRange, field, criteria1, criteria2
    
    ' Apply sort
    CustomSort sheetName, sortRange, keyRange, order
End Sub

Example Usage

Sub ApplySortAndFilter()
    ' First filter then sort the data in Sheet1
    SortAndFilter "Sheet1", "A2:D100", "A2:A100", xlAscending, "A1:D100", 2, "Approved"
End Sub

These VBA functions provide enhanced capabilities for sorting and filtering data within Excel, allowing for greater automation and reuse.

Complex Financial Calculations in VBA for Excel

In this unit, we will develop VBA custom functions to perform complex financial calculations such as Net Present Value (NPV), Internal Rate of Return (IRR), and Loan Amortization Schedule. Here are the implementations:

Net Present Value (NPV)

To calculate the NPV, create a custom function in a VBA module.

Function CustomNPV(rate As Double, cashFlows As Range) As Double
    Dim sumNPV As Double
    Dim cashFlow As Double
    Dim period As Integer
    
    sumNPV = 0
    period = 1
    
    For Each cell In cashFlows
        cashFlow = cell.Value
        sumNPV = sumNPV + (cashFlow / (1 + rate) ^ period)
        period = period + 1
    Next cell
    
    CustomNPV = sumNPV
End Function

Internal Rate of Return (IRR)

To calculate the IRR, we can use the Newton-Raphson method for finding roots. Create this custom function in a VBA module.

Function CustomIRR(cashFlows As Range, initialGuess As Double) As Double
    Dim rate As Double
    Dim precision As Double
    Dim iteration As Integer
    Const maxIterations As Integer = 1000
    Const tolerance As Double = 0.00001
    
    rate = initialGuess
    precision = 1
    iteration = 0
    
    Do While (iteration < maxIterations And precision > tolerance)
        Dim npv As Double
        Dim dNpv As Double
        Dim cashFlow As Double
        Dim period As Integer
        
        npv = 0
        dNpv = 0
        period = 1
        
        For Each cell In cashFlows
            cashFlow = cell.Value
            npv = npv + (cashFlow / (1 + rate) ^ period)
            dNpv = dNpv - (period * cashFlow / (1 + rate) ^ (period + 1))
            period = period + 1
        Next cell
        
        precision = Abs(npv)
        rate = rate - npv / dNpv
        iteration = iteration + 1
    Loop
    
    CustomIRR = rate
End Function

Loan Amortization Schedule

To generate a loan amortization schedule, create custom functions to calculate payment, principal, and interest amounts.

Payment Calculation

Function LoanPayment(loanAmount As Double, annualRate As Double, totalPayments As Integer) As Double
    Dim monthlyRate As Double
    monthlyRate = annualRate / 12
    LoanPayment = (loanAmount * monthlyRate) / (1 - (1 + monthlyRate) ^ -totalPayments)
End Function

Principal and Interest Calculation

Function LoanSchedule(loanAmount As Double, annualRate As Double, totalPayments As Integer) As Variant
    Dim payment As Double
    Dim balance As Double
    Dim schedule() As Double
    Dim monthlyRate As Double
    Dim principal As Double
    Dim interest As Double
    Dim i As Integer
    
    monthlyRate = annualRate / 12
    payment = LoanPayment(loanAmount, annualRate, totalPayments)
    balance = loanAmount
    ReDim schedule(totalPayments, 3)
    
    For i = 1 To totalPayments
        interest = balance * monthlyRate
        principal = payment - interest
        balance = balance - principal
        
        schedule(i, 0) = i
        schedule(i, 1) = payment
        schedule(i, 2) = principal
        schedule(i, 3) = interest
        schedule(i, 4) = balance
    Next i
    
    LoanSchedule = schedule
End Function

You can create a macro to display this schedule in a worksheet, making it easier to visualize and analyze the data.

By implementing these VBA custom functions, you will enhance your financial analysis capabilities in Excel.

Interactive User Input Forms in VBA for Excel

To create an Interactive User Input Form in Excel using VBA, follow these steps to implement a VBA UserForm:

' Step 1: Create the UserForm
' Open the VBA editor, insert a UserForm, and name it "UserForm1"
' Add the following controls to the form:
' - 2 TextBoxes for user input, named TextBox1 and TextBox2
' - 1 ComboBox, named ComboBox1
' - 1 CommandButton, named CommandButton1

' Code for the UserForm
Private Sub UserForm_Initialize()
    ' Initialize ComboBox with options
    ComboBox1.AddItem "Option 1"
    ComboBox1.AddItem "Option 2"
    ComboBox1.AddItem "Option 3"
End Sub

Private Sub CommandButton1_Click()
    ' Retrieve values from the UserForm controls
    Dim input1 As String
    Dim input2 As String
    Dim selectedOption As String
    
    input1 = TextBox1.Value
    input2 = TextBox2.Value
    selectedOption = ComboBox1.Value
    
    ' Transfer values to the active Excel worksheet
    With ActiveSheet
        .Cells(1, 1).Value = "Input 1"
        .Cells(2, 1).Value = input1
        .Cells(1, 2).Value = "Input 2"
        .Cells(2, 2).Value = input2
        .Cells(1, 3).Value = "Selected Option"
        .Cells(2, 3).Value = selectedOption
    End With
    
    ' Close the UserForm
    Unload Me
End Sub

' Code to show the UserForm
Sub ShowUserForm()
    UserForm1.Show
End Sub

Explanation

  1. UserForm Design:
    • Insert a new UserForm in the VBA editor and add the specified controls (2 TextBoxes, 1 ComboBox, and 1 CommandButton).
    • Name the controls appropriately: TextBox1, TextBox2, ComboBox1, and CommandButton1.
  2. Initialize Event:
    • Populate the ComboBox with options when the UserForm is initialized.
  3. CommandButton Click Event:
    • Retrieve input values from TextBox1, TextBox2, and ComboBox1.
    • Transfer the retrieved values to specific cells in the active worksheet.
    • Unload the UserForm after transferring the values.
  4. Show UserForm:
    • Create a macro, ShowUserForm, to display the UserForm.

Applying the Solution

  1. Open the VBA editor in Excel (Alt + F11).
  2. Insert a UserForm and add the specified controls to the form.
  3. Copy and paste the above code snippets into the appropriate sections:
    • The initialization and click event code within the UserForm’s code module.
    • The ShowUserForm subroutine in a standard module.

By following these steps, you can implement an interactive user input form in Excel using VBA, enhancing the functionality of your project.

Error Handling and Debugging Techniques in VBA for Excel

Overview

This section covers a comprehensive approach to error handling and debugging in VBA custom functions within Excel. Implementing robust error handling and debugging techniques helps ensure that your VBA code runs smoothly and is easier to troubleshoot when issues arise.

Error Handling Techniques

Using On Error Statement

In VBA, error handling is primarily done using the On Error statement. It allows you to specify how to handle errors when they occur.

Function SafeDivision(dividend As Double, divisor As Double) As Variant
    On Error GoTo ErrorHandler
    
    If divisor = 0 Then
        Err.Raise vbObjectError + 1, , "Division by zero error."
    End If
    
    SafeDivision = dividend / divisor
    Exit Function
    
ErrorHandler:
    SafeDivision = "Error: " & Err.Description
End Function

Error Handling Using Err Object

The Err object provides runtime error information. You can use its properties like Number, Description, and Source to gain insights into the error.

Function ReadCellValue(sheetName As String, cellAddress As String) As Variant
    On Error GoTo ErrorHandler
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(sheetName)
    ReadCellValue = ws.Range(cellAddress).Value
    Exit Function
    
ErrorHandler:
    ReadCellValue = "Error " & Err.Number & ": " & Err.Description
End Function

Debugging Techniques

Using Debug.Print

Debug.Print is an essential tool for printing information to the Immediate Window, which helps in understanding the flow and state of your program.

Function SumTwoNumbers(a As Double, b As Double) As Double
    Debug.Print "Debug: Summing " & a & " and " & b
    SumTwoNumbers = a + b
    Debug.Print "Debug: Result is " & SumTwoNumbers
End Function

Using Breakpoints and Step Into

Breakpoints allow you to pause execution at a particular line of code. Once the execution is paused, you can use the Step Into (F8) command to execute your code line-by-line.

Immediate Window for Testing and Queries

You can use the Immediate Window to interactively run code snippets, evaluate expressions, and inspect variable states.

Sub TestImmediateWindow()
    ' Type below line in Immediate Window to test:
    ' ?SumTwoNumbers(3, 5)
    Debug.Print "You can test in Immediate Window."
End Sub

Combining Error Handling and Debugging

Combining these techniques can significantly enhance your debugging capability and error resilience.

Function EnhancedDataProcessing(dataRange As Range) As Variant
    On Error GoTo ErrorHandler
    
    Dim total As Double
    Dim cell As Range
    
    For Each cell In dataRange
        If Not IsNumeric(cell.Value) Then
            Err.Raise vbObjectError + 2, , "Non-numeric value encountered in range."
        End If
        total = total + cell.Value
        Debug.Print "Debug: Adding " & cell.Value & " to total. New total is " & total
    Next cell
    
    EnhancedDataProcessing = total
    Exit Function
    
ErrorHandler:
    EnhancedDataProcessing = "Error " & Err.Number & ": " & Err.Description
End Function

Summary

By implementing effective error handling with On Error and using debugging tools like Debug.Print, Breakpoints, and the Immediate Window, you enhance the robustness and maintainability of your VBA custom functions in Excel.

Related Posts