How to Check If a Number Is Odd or Even with Excel VBA

In this article, we will demonstrate a step-by-step procedure to check if a number is odd or even with VBA in Excel. We used Office 365, but you may use any version at your disposal.

Step 1 – Select Dataset

We’ll use the following dataset which has two columns, Number and Type. The Number column contains numeric values, and in column Type we will determine whether the number in the adjacent cell is Odd or Even.

Step 2 – Insert VBA Code to Check If Odd or Even

• Press Alt + F11 to open the Microsoft Visual Basic Applications window.
• Click Insert and select Module from the menu to create a module.

• In the module window that opens, enter the following VBA code:

Sub CheckEvenOrOddNumber()
    Dim p As Range
    For Each p In Worksheets("VBA").Range("B5:B12")
        If IsNumeric(p.Value) Then
            If p.Value Mod 2 = 0 Then
                p.Offset(0, 1).Value = "Even"
            Else
                p.Offset(0, 1).Value = "Odd"
            End If
        End If
    Next p
End Sub

Sub CheckEvenOrOddNumber()

Dim p As Range

For Each p In Worksheets("VBA").Range("B5:B12")

   If IsNumeric(p.Value) Then
            If p.Value Mod 2 = 0 Then
                p.Offset(0, 1).Value = "Even"
            Else
                p.Offset(0, 1).Value = "Odd"

End If
        End If
    Next p

End Sub

• Press F5 to run the code.

Final Output

The output is returned in column Type, showing whether the adjacent number is odd or even.

Things to Remember

• Enter any number to check whether it is even or odd.
• Provide the Range where you want to check the values according to your own datasheet.
• Modify the VBA code as required.
• After running, the macro will show nothing in the output cell is the input cell does not contain an integer or text value.
• We can either return the output in a particular cell range or in a MsgBox. Modify the code accordingly.

Download Practice Workbook

<< Go Back to | Excel for Math | Learn Excel