In this article, we would discuss about 5 most useful VBA codes to add to your Excel VBA arsenal.
- MsgBox – Interact with the user by displaying messages
- If..Then…Else statement – Execute code based on conditions
- For…Next statement – Execute code repeatedly
- InputBox – Interact with the user by prompting user input
- Array function – A simple way to neatly pack elements into a variable
Table of Contents
If you are completely new to Excel VBA, you might want to check out how to access Visual Basic Editor – the coding environment for Excel VBA.
MsgBox – Interact with the user by displaying messages
One of the most useful VBA functions is MsgBox function. The MsgBox can display a message through a dialog box. It can also allow response from the user.
The syntax for MsgBox function: (the arguments in square brackets are optional)
MsgBox (prompt, [ buttons, ] [ title, ] [ helpfile, context ])
| Prompt | Required. The displayed message in dialog box. |
| Buttons | Optional. The value to indicate which buttons/icons to be displayed. |
| Title | Optional. The text in title bar of dialog box |
| Helpfile and Context | Optional. For simplicity, just ignore them at this moment. |
A basic application of the MsgBox:
The resulting dialog box:
You can customize the icon and title of the dialog box too:
Upon typing a comma, a drop-down menu appears. As a result, you can customize the buttons/icons to be displayed through choosing from the drop-down menu.
For example, a critical message icon would be displayed upon choosing vbCritical. The title of the dialog box is also specified. The resulting dialog box:
The MsgBox function also serves as a useful debugging tool. During code development, you may insert a MsgBox function to display the value of a certain variable. This allows the user to know whether the code runs as intended.
If…Then…Else statement – Execute code based on conditions
If…Then…Else statement executes code conditionally. The basic syntax is:
If condition Then [ statements ] [ Else elsestatements ]
If…Then statement in one line
First, we declare the variable Title and assign “Chris Chan – Excel VBA” as our MsgBox title. Using the above code would generate a MsgBox like this:
Clicking the “Yes” button stores vbYes into Ans. Since the condition is fulfilled, i.e. if Ans = vbYes, the statements would be executed. A new MsgBox would be generated:
Adding the elsestatements
If the user clicks the “No” button, nothing happens. To avoid that, we can add the elsestatements:
Using this Greeting_User4(), if the user doesn’t click the “Yes” button, a MsgBox showing “Unauthorized access.” would be displayed.
Execute multiple statements
To execute multiple statements, use this form:
Note that End If is included at the end of the If…Then statement. This form of If…Then statement is most commonly used in real practice.
InputBox – Interact with the user by prompting user input
InputBox is similar to MsgBox. However, InputBox allows the user input. The syntax is similar:
InputBox (prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfile, context ])
For simplicity, just ignore the optional parameters. Just specify the prompt and title would be sufficient.
Since the InputBox function prompts user input, you can declare a variable and store the user input in that variable. Let’s see an improved version of Greeting_User() utilizing both InputBox and MsgBox.
The code prompts the user to input the user’s name, stores the name into the variable Name, then greet the user by his/her name. The video below shows how the InputBox works with MsgBox:
For…Next statement – Execute code repeatedly
If…Then statement executes statements based on condition. On the contrary, For…Next statement utilizes looping, which repeatedly executes blocks of statements.
The For…Next statement syntax:
For counter = start To end [ Step step ]
[ statements ]
[ Exit For ]
[ statements ]
Next [ counter ]
A simple example of For-Next loop:
The count would start from 1, increases by 1 each time the loop repeats. The sum = sum + count statement is executed 10 times, so the result would be 55.
For…Next statement is powerful due to possible interaction with cells. We can loop through rows/columns to input data to cells. An example of inputting value:
Using cells function, we specify the cell by its row number and column number. When row number is 1, the cell A1 is specified (the cell of the first column and first row). We assign value of cell A1 as the RowNumber, i.e. 1. Repeat this loop for 10 times and we get:
Array function – A simple way to neatly pack elements into a variable
An array can store elements of the same type, e.g. numbers, strings, dates, etc. You can refer to a specific element in the array by using the array name and its index number. You can retrieve data from a worksheet using a For-loop and store the data into an Array.
Declaring arrays
Declare an array using a Dim statement. You can specify the number of elements in the array. For example:
This would declare an Array which can store 12 elements.
Retrieving the data and store in array
Let’s say you have the names of the 12 months typed in Column A. You can use a For-loop to loop through rows, retrieve the data and store in an array.
When Row = 1, the value “Jan” in Cells(1, 1), i.e. Cell A1 is retrieved and assigned to MonthArray(1). When Row = 2, the value “Feb” in Cells(2, 1), i.e. Cell A2 is retrieved and assigned to MonthArray(2). The code loops through the 12 rows and assign values accordingly to MonthArray.
Refer to a specific element using index number
After storing the value in the MonthArray, you can refer to a specific element in the array by specifying the index number. For example, MonthArray(1) would return “Jan”. You can design your code to prompt user to input a number from 1 to 12 and display the name of the month accordingly:
The result would look like this:
Summary and Key points
MsgBox can display messages whereas InputBox can prompt user input. If…Then statement allows execution of different code based on different conditions. For…Next statement allows repeat code executions, thus enables interactions with worksheets. As a result, we can store the data retrieved into an Array for further manipulation.
Ready to apply these skills in a real project? Check out my VBA project Template Helper. It is a good example demonstrating real-life application of these concepts:
- A For…Next statement recognizes parameters from the worksheet.
- The parameters are stored in an Array.
- The user can assign values to parameters through InputBox.
- If…Then…Else statements are used frequently to dictate actions taken for different scenarios.
Thank you very much for your support. Please feel free to leave any comments down below. 🙂 Your comments are greatly valued.