NeuroAgent

VBA: Find Min Max with Invalid Input Handling

Learn how to properly find minimum and maximum values in VBA while skipping invalid inputs. Complete guide with code examples and error handling techniques.

#error-handling#excel-vba#input-validation#numeric-processing#vba-programming
Question

VBA Problem: Finding Min and Max Values While Skipping Invalid Inputs

I’m working on a VBA problem to find the maximum and minimum values in a sequence while skipping invalid inputs.

The issue I’m facing is that when the first value in the sequence is invalid, the variable minnum automatically gets assigned a value of 0. For example:

  • Entering 3, a, 1, 2 gives min = 0, max = 2

However, this error doesn’t occur if the first input is valid. For example:

  • Entering 3, 1, a, 2 gives min = 1, max = 2

Here’s the VBA code I’m working with:

vba
Sub minmaxnum()
    Dim maxnum As Long
    Dim minnum As Double
    Dim n As Integer
    Dim num As Double
    Dim i As Integer

    n = InputBox("How many numbers do you want to enter?")
    maxnum = 0
    minnum = 1E+39

    On Error Resume Next
    For i = 1 To n
        num = InputBox("Enter those numbers: ")
        If num > maxnum Then
            maxnum = num
        End If
        If num < minnum Then
            minnum = num
        End If
    Next i

    MsgBox "Maxnum is: " & maxnum & vbCrLf & "Minnum is: " & minnum
End Sub

How can I modify this code to properly handle invalid inputs, especially when the first input is invalid?

NeuroAgent

The issue occurs because On Error Resume Next causes invalid inputs like “a” to be converted to 0, making 0 the minimum value when the first input is invalid. Here’s how to fix this and properly handle invalid inputs:

Contents

Understanding the Problem

When On Error Resume Next is active and an invalid input like “a” is provided to InputBox, VBA converts it to 0 automatically. Since your minnum starts at 1E+39, this 0 becomes the new minimum, leading to incorrect results.

The key insight from Stack Overflow is that while On Error Resume Next prevents crashes, “it will just about guarantee that it won’t do what you want” when used without proper error checking.

Solution 1: Using IsNumeric Validation

The most straightforward approach is to validate input before processing:

vba
Sub minmaxnum()
    Dim maxnum As Double
    Dim minnum As Double
    Dim n As Integer
    Dim num As Double
    Dim i As Integer
    Dim inputStr As String

    n = InputBox("How many numbers do you want to enter?")
    maxnum = -1E+39  ' Initialize to very small number
    minnum = 1E+39   ' Initialize to very large number
    Dim hasValidInput As Boolean
    hasValidInput = False

    For i = 1 To n
        inputStr = InputBox("Enter number " & i & ": ")
        
        ' Check if user clicked Cancel
        If inputStr = "" Then Exit For
        
        ' Validate if input is numeric
        If IsNumeric(inputStr) Then
            num = CDbl(inputStr)
            hasValidInput = True
            
            If num > maxnum Then
                maxnum = num
            End If
            If num < minnum Then
                minnum = num
            End If
        Else
            ' Handle invalid input
            MsgBox "Invalid input '" & inputStr & "'. Please enter a valid number.", vbExclamation
            i = i - 1  ' Retry this iteration
        End If
    Next i

    If hasValidInput Then
        MsgBox "Max is: " & maxnum & vbCrLf & "Min is: " & minnum
    Else
        MsgBox "No valid numbers were entered.", vbInformation
    End If
End Sub

Solution 2: Error Checking with Err Object

As recommended by Automate Excel, you can combine On Error Resume Next with proper error checking:

vba
Sub minmaxnum_with_error_checking()
    Dim maxnum As Double
    Dim minnum As Double
    Dim n As Integer
    Dim num As Double
    Dim i As Integer
    Dim hasValidInput As Boolean
    
    n = InputBox("How many numbers do you want to enter?")
    maxnum = -1E+39
    minnum = 1E+39
    hasValidInput = False

    On Error Resume Next
    
    For i = 1 To n
        num = InputBox("Enter number " & i & ": ")
        
        ' Check if user clicked Cancel
        If Err.Number <> 0 Or num = 0 Then
            Err.Clear
            ' Check if input was actually empty (Cancel was clicked)
            If num = 0 Then Exit For
            ' Otherwise continue - this was an invalid numeric input
            i = i - 1
            GoTo ContinueLoop
        End If
        
        hasValidInput = True
        
        If num > maxnum Then
            maxnum = num
        End If
        If num < minnum Then
            minnum = num
        End If
        
ContinueLoop:
    Next i

    If hasValidInput Then
        MsgBox "Max is: " & maxnum & vbCrLf & "Min is: " & minnum
    Else
        MsgBox "No valid numbers were entered.", vbInformation
    End If
End Sub

Complete Robust Implementation

Here’s the most robust solution that handles all edge cases:

vba
Sub robust_minmax()
    Dim maxnum As Double
    Dim minnum As Double
    Dim n As Integer
    Dim num As Double
    Dim i As Integer
    Dim inputStr As String
    Dim hasValidInput As Boolean
    Dim validCount As Integer
    
    ' Get number of inputs
    n = InputBox("How many numbers do you want to enter?")
    If n <= 0 Then Exit Sub
    
    ' Initialize with extreme values
    maxnum = -1E+39
    minnum = 1E+39
    hasValidInput = False
    validCount = 0

    For i = 1 To n
        inputStr = InputBox("Enter number " & i & ": ")
        
        ' Check for Cancel
        If inputStr = "" Then Exit For
        
        ' Validate numeric input
        If IsNumeric(inputStr) Then
            num = CDbl(inputStr)
            hasValidInput = True
            validCount = validCount + 1
            
            ' Update min and max
            If validCount = 1 Then
                ' First valid input sets both min and max
                maxnum = num
                minnum = num
            Else
                If num > maxnum Then
                    maxnum = num
                End If
                If num < minnum Then
                    minnum = num
                End If
            End If
        Else
            ' Handle invalid input with option to retry
            Dim response As VbMsgBoxResult
            response = MsgBox("Invalid input '" & inputStr & "'. Try again?", vbQuestion + vbYesNo, "Invalid Input")
            
            If response = vbNo Then
                Exit For
            Else
                i = i - 1  ' Retry this iteration
            End If
        End If
    Next i

    ' Display results or error message
    If hasValidInput Then
        MsgBox "Results:" & vbCrLf & _
               "Valid inputs entered: " & validCount & vbCrLf & _
               "Maximum value: " & maxnum & vbCrLf & _
               "Minimum value: " & minnum, vbInformation, "Analysis Complete"
    Else
        MsgBox "No valid numbers were entered.", vbExclamation, "No Valid Input"
    End If
End Sub

Best Practices for VBA Input Validation

Based on research findings, here are key best practices:

  1. Use IsNumeric function - As suggested by FasterCapital, this is the standard way to validate numeric input in VBA.

  2. Proper error handling - When using On Error Resume Next, always check the Err object and use Err.Clear as recommended by Automate Excel.

  3. Initialize variables appropriately - Avoid using 0 as initial values for min/max calculations since 0 might be a valid input.

  4. Handle user cancellation - Check for empty strings when users cancel input dialogs.

  5. Provide clear feedback - Give users informative messages about invalid inputs.

Testing and Edge Cases

Test your solution with these scenarios:

Test Case Expected Result
First input invalid, then valid numbers Skip invalid, process valid numbers
All inputs invalid Show error message, no min/max
Mixed valid/invalid inputs Only process valid numbers
User cancels input Exit gracefully
Empty string as input Handle as cancellation
Decimal numbers Work with Double type
Very large numbers Handle scientific notation

The complete solution above handles all these cases while providing clear feedback to users and maintaining robust error handling throughout the process.

How to handle empty inputs in VBA InputBox without crashing?What's the best way to validate decimal numbers in VBA InputBox?How to create a custom input dialog with validation in VBA?What are the common pitfalls in VBA error handling with InputBox?How to improve user experience when collecting multiple inputs in VBA?What's the difference between IsNumeric and IsDate functions in VBA?
Ask NeuroAgent