Show List
Tutorials > VBA > VBA Coding Questions

VBA Coding Questions


  • Write a VBA function to calculate the factorial of a number.
vbnet
Copy code
Function Factorial(n As Integer) As Long
    If n = 0 Then
        Factorial = 1
    Else
        Factorial = n * Factorial(n - 1)
    End If
End Function
  • Write a VBA function to calculate the nth Fibonacci number.
vbnet
Copy code
Function Fibonacci(n As Integer) As Long
    If n = 1 Or n = 2 Then
        Fibonacci = 1
    Else
        Fibonacci = Fibonacci(n - 1) + Fibonacci(n - 2)
    End If
End Function
  • Write a VBA function to calculate the sum of the digits of a number.
vbnet
Copy code
Function DigitSum(n As Long) As Integer
    Dim sum As Integer
    Do While n > 0
        sum = sum + n Mod 10
        n = n \ 10
    Loop
    DigitSum = sum
End Function
  • Write a VBA function to reverse a string.
vbnet
Copy code
Function ReverseString(s As String) As String
    Dim result As String
    Dim i As Integer
    For i = Len(s) To 1 Step -1
        result = result & Mid(s, i, 1)
    Next i
    ReverseString = result
End Function
  • Write a VBA function to determine whether a number is prime.
vbnet
Copy code
Function IsPrime(n As Integer) As Boolean
    If n <= 1 Then
        IsPrime = False
    ElseIf n = 2 Then
        IsPrime = True
    ElseIf n Mod 2 = 0 Then
        IsPrime = False
    Else
        Dim i As Integer
        For i = 3 To Int(Sqr(n)) Step 2
            If n Mod i = 0 Then
                IsPrime = False
                Exit Function
            End If
        Next i
        IsPrime = True
    End If
End Function
  • Write a VBA function to remove duplicates from an array.
vbnet
Copy code
Function RemoveDuplicates(arr() As Variant) As Variant
    Dim result() As Variant
    Dim i As Integer, j As Integer
    For i = LBound(arr) To UBound(arr)
        For j = i + 1 To UBound(arr)
            If arr(i) = arr(j) Then
                GoTo NextElement
            End If
        Next j
        ReDim Preserve result(UBound(result) + 1)
        result(UBound(result)) = arr(i)
NextElement:
    Next i
    RemoveDuplicates = result
End Function
  • Write a VBA function to find the maximum value in an array.
vbnet
Copy code
Function MaxValue(arr() As Variant) As Variant
    Dim max As Variant
    Dim i As Integer
    max = arr(0)
    For i = 1 To UBound(arr)
        If arr(i) > max Then
            max = arr(i)
        End If
    Next i
    MaxValue = max
End Function


  • Write a VBA function to count the number of occurrences of a string in another string.
vbnet
Copy code
Function CountOccurrences(s As String, target As String) As Integer
    CountOccurrences = (Len(s) - Len(Replace(s, target, ""))) / Len(target)
End Function
  • Write a VBA function to check whether a string is a palindrome.
vbnet
Copy code
Function IsPalindrome(s As String) As Boolean
    Dim reversed As String
    reversed = ReverseString(s)
    IsPalindrome = (s = reversed)
End Function
  • Write a VBA function to calculate the average of an array.
vbnet
Copy code
Function Average(arr() As Variant) As Variant
    Dim sum As Double
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        sum = sum + arr(i)
    Next i
    Average = sum / (UBound(arr) - LBound(arr) + 1)
End Function
  • Write a VBA function to find the index of the first occurrence of a value in an array.
vbnet
Copy code
Function FindIndex(arr() As Variant, target As Variant) As Integer
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        If arr(i) = target Then
            FindIndex = i
            Exit Function
        End If
    Next i
    FindIndex = -1
End Function
  • Write a VBA function to reverse the order of words in a string.
vbnet
Copy code
Function ReverseWords(s As String) As String
    Dim words() As String
    words = Split(s, " ")
    Dim result As String
    Dim i As Integer
    For i = UBound(words) To 0 Step -1
        result = result & words(i) & " "
    Next i
    ReverseWords = result
End Function
  • Write a VBA function to check whether an array is sorted in ascending order.
vbnet
Copy code
Function IsSortedAscending(arr() As Variant) As Boolean
    Dim i As Integer
    For i = LBound(arr) To UBound(arr) - 1
        If arr(i) > arr(i + 1) Then
            IsSortedAscending = False
            Exit Function
        End If
    Next i
    IsSortedAscending = True
End Function
  • Write a VBA function to find the minimum value in an array.
vbnet
Copy code
Function MinValue(arr() As Variant) As Variant
    Dim min As Variant
    Dim i As Integer
    min = arr(0)
    For i = 1 To UBound(arr)
        If arr(i) < min Then
            min = arr(i)
        End If
    Next i
    MinValue = min
End Function
  • Write a VBA function to convert a decimal number to binary.
vbnet
Copy code
Function DecimalToBinary(n As Long) As String
    Dim result As String
    Do While n > 0
        result = CStr(n Mod 2) & result
        n = n \ 2
    Loop
    DecimalToBinary = result
End Function


  • Write a VBA function to remove duplicates from an array.
vbnet
Copy code
Function RemoveDuplicates(arr() As Variant) As Variant
    Dim unique() As Variant
    ReDim unique(0 To UBound(arr))
    Dim count As Integer
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        If Not IsInArray(unique, arr(i)) Then
            unique(count) = arr(i)
            count = count + 1
        End If
    Next i
    ReDim Preserve unique(0 To count - 1)
    RemoveDuplicates = unique
End Function

Function IsInArray(arr() As Variant, target As Variant) As Boolean
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        If arr(i) = target Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False
End Function
  • Write a VBA function to find the mode of an array.
vbnet
Copy code
Function Mode(arr() As Variant) As Variant
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        If Not dict.Exists(arr(i)) Then
            dict.Add arr(i), 1
        Else
            dict(arr(i)) = dict(arr(i)) + 1
        End If
    Next i
    Dim maxCount As Integer
    Dim result As Variant
    For Each key In dict.Keys
        If dict(key) > maxCount Then
            maxCount = dict(key)
            result = key
        End If
    Next key
    Mode = result
End Function

Here are 20 more VBA coding exercise questions and answers:

  • Write a VBA function to find the sum of the elements in a 2D array.
vbnet
Copy code
Function ArraySum(arr() As Variant) As Variant
    Dim result As Variant
    result = 0
    Dim i As Integer
    Dim j As Integer
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            result = result + arr(i, j)
        Next j
    Next i
    ArraySum = result
End Function
  • Write a VBA function to find the product of the elements in a 2D array.
vbnet
Copy code
Function ArrayProduct(arr() As Variant) As Variant
    Dim result As Variant
    result = 1
    Dim i As Integer
    Dim j As Integer
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            result = result * arr(i, j)
        Next j
    Next i
    ArrayProduct = result
End Function
  • Write a VBA function to find the maximum value in a 2D array.
vbnet
Copy code
Function ArrayMax(arr() As Variant) As Variant
    Dim result As Variant
    result = arr(1, 1)
    Dim i As Integer
    Dim j As Integer
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            If arr(i, j) > result Then
                result = arr(i, j)
            End If
        Next j
    Next i
    ArrayMax = result
End Function
  • Write a VBA function to find the minimum value in a 2D array.
vbnet
Copy code
Function ArrayMin(arr() As Variant) As Variant
    Dim result As Variant
    result = arr(1, 1)
    Dim i As Integer
    Dim j As Integer
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            If arr(i, j) < result Then
                result = arr(i, j)
            End If
        Next j
    Next i
    ArrayMin = result
End Function
  • Write a VBA function to concatenate all the elements in a 1D array into a single string.
vbnet
Copy code
Function ConcatenateArray(arr() As Variant, Optional separator As String = "") As String
    Dim result As String
    Dim i As Integer
    For i = LBound(arr) To UBound(arr)
        result = result & arr(i)
        If i < UBound(arr) Then
            result = result & separator
        End If
    Next i
    ConcatenateArray = result
End Function
  • Write a VBA function to check if a string is a palindrome.
vbnet
Copy code
Function IsPalindrome(str As String) As Boolean
    Dim i As Integer
    For i = 1 To Len(str) \ 2
        If Mid(str, i, 1) <> Mid(str, Len(str) - i + 1, 1) Then
            IsPalindrome = False
            Exit Function
        End If
    Next i
    IsPalindrome = True
End Function


  • Write a VBA function to find the factorial of a number.
vbnet
Copy code
Function Factorial(n As Integer) As Long
    Dim result As Long
    result = 1
    Dim i As Integer
    For i = 1 To n
        result = result * i
    Next i
    Factorial = result
End Function
  • Write a VBA function to check if a number is prime.
vbnet
Copy code
Function IsPrime(n As Integer) As Boolean
    Dim i As Integer
    If n < 2 Then
        IsPrime = False
        Exit Function
    End If
    For i = 2 To Sqr(n)
        If n Mod i = 0 Then
            IsPrime = False
            Exit Function
        End If
    Next i
    IsPrime = True
End Function
  • Write a VBA function to convert a decimal number to binary.
vbnet
Copy code
Function DecimalToBinary(n As Integer) As String
    Dim result As String
    Do While n > 0
        result = (n Mod 2) & result
        n = n \ 2
    Loop
    DecimalToBinary = result
End Function
  • Write a VBA function to convert a binary number to decimal.
vbnet
Copy code
Function BinaryToDecimal(s As String) As Integer
    Dim result As Integer
    Dim i As Integer
    For i = 1 To Len(s)
        result = result * 2 + Val(Mid(s, i, 1))
    Next i
    BinaryToDecimal = result
End Function
  • Write a VBA function to calculate the sum of the digits in a number.
vbnet
Copy code
Function DigitSum(n As Integer) As Integer
    Dim result As Integer
    Do While n > 0
        result = result + (n Mod 10)
        n = n \ 10
    Loop
    DigitSum = result
End Function
  • Write a VBA function to reverse the digits in a number.
vbnet
Copy code
Function ReverseDigits(n As Integer) As Integer
    Dim result As Integer
    Do While n > 0
        result = result * 10 + (n Mod 10)
        n = n \ 10
    Loop
    ReverseDigits = result
End Function
  • Write a VBA function to find the greatest common divisor of two numbers.
vbnet
Copy code
Function GCD(a As Integer, b As Integer) As Integer
    Dim temp As Integer
    Do While b <> 0
        temp = b
        b = a Mod b
        a = temp
    Loop
    GCD = a
End Function
  • Write a VBA function to find the least common multiple of two numbers.
vbnet
Copy code
Function LCM(a As Integer, b As Integer) As Integer
    LCM = (a * b) / GCD(a, b)
End Function
  • Write a VBA function to check if a string is a valid email address.
vbnet
Copy code
Function IsValidEmail(str As String) As Boolean
    Dim pattern As String
    pattern = "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$"
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = pattern
    IsValidEmail = regex.Test(str)
End Function


  • Write a VBA function to remove duplicates from an array.
vbnet
Copy code
Function RemoveDuplicates(arr As Variant) As Variant
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        dict(arr(i)) = 1
    Next i
    RemoveDuplicates = dict.Keys
End Function
  • Write a VBA function to count the number of words in a string.
vbnet
Copy code
Function WordCount(str As String) As Long
    Dim words() As String
    words = Split(str, " ")
    WordCount = UBound(words) + 1
End Function
  • Write a VBA function to count the number of occurrences of a character in a string.
vbnet
Copy code
Function CharCount(str As String, ch As String) As Long
    Dim i As Long
    Dim count As Long
    For i = 1 To Len(str)
        If Mid(str, i, 1) = ch Then
            count = count + 1
        End If
    Next i
    CharCount = count
End Function
  • Write a VBA function to find the largest and smallest elements in an array.
scss
Copy code
Function MinMax(arr As Variant) As Variant
    Dim min As Variant
    Dim max As Variant
    Dim i As Long
    min = arr(LBound(arr))
    max = arr(LBound(arr))
    For i = LBound(arr) + 1 To UBound(arr)
        If arr(i) < min Then
            min = arr(i)
        End If
        If arr(i) > max Then
            max = arr(i)
        End If
    Next i
    MinMax = Array(min, max)
End Function
  • Write a VBA function to remove all non-alphanumeric characters from a string.
vbnet
Copy code
Function RemoveNonAlphanumeric(str As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "[^A-Za-z0-9]"
    RemoveNonAlphanumeric = regex.Replace(str, "")
End Function
  • Write a VBA function to find the second largest element in an array.
scss
Copy code
Function SecondLargest(arr As Variant) As Variant
    Dim first As Variant
    Dim second As Variant
    If UBound(arr) < 1 Then
        SecondLargest = CVErr(xlErrNA)
        Exit Function
    End If
    first = arr(LBound(arr))
    second = arr(LBound(arr))
    Dim i As Long
    For i = LBound(arr) + 1 To UBound(arr)
        If arr(i) > first Then
            second = first
            first = arr(i)
        ElseIf arr(i) > second And arr(i) <> first Then
            second = arr(i)
        End If
    Next i
    If second = arr(LBound(arr)) Then
        SecondLargest = CVErr(xlErrNA)
    Else
        SecondLargest = second
    End If
End Function
  • Write a VBA function to remove all vowels from a string.
vbnet
Copy code
Function RemoveVowels(str As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "[aeiouAEIOU]"
    RemoveVowels = regex.Replace(str, "")
End Function
    Leave a Comment

  • captcha text