How to Use Variables in VBA MsgBox and InputBox in Excel and Word?

In this tutorial, we will explore how to utilize variables with the msgbox and inputbox functions to create dynamic dialog boxes. The code provided below will run in Microsoft 365 (or earlier) Excel, PowerPoint and Word applications.

Using variables with MsgBox and InputBox can make your VBA dialog interactions more dynamic and user-friendly. In this example we will use an Input Box to get a user input about the test score. Then, our program will evaluate the input and determine whether the user passed the test or failed.

#1 – Add a module to your Excel or Word

In Excel, Word or PowerPoint, hit the Developer tab.

Then, press the Visual Basic button – or hit the ALT +F11 combination.

Then right click the VBAProject entry, select Insert and then Module. This adds a VBA module, in which you will paste the code below.

#2 – Write your VBA code

In the macro code below, i used the studentScore variable alongside with an input box to capture the student score. We then use that variable to determine the pass or fail result.

Here’s is the code you can use – obviously, feel free to enhance it as needed:

Sub DisplayPassFailBasedOnScore()
    Dim studentScore As Integer
    studentScore = InputBox("Enter your test score:", "Score Input")
    ' Check if the studentScore is greater than 80
    If IsNumeric(studentScore) Then
        If studentScore >= 80 Then
            MsgBox "You have passed the test.", vbInformation,"Your Grade Result"
            MsgBox "You should review the material and take another test.", vbExclamation,"Your Grade Result"
        End If
        MsgBox "This program needs enter a numeric input. Please enter your test score.", vbCritical, "Invalid Input"
    End If
End Sub

#3 – Run you VBA program

Save your module, then hit ALT + F8 and select your macro named DisplayPassFailBasedOnScore.

Once your program executes, enter the user score and see your results!

Here’s you input box:

I passed the test 🙂