The numerous functionalities of Excel VBA can automate various processes and simplify your workflow. One such feature is the MsgBox function, which shows the user a message box. The MsgBox function can be used to verify cell addresses, get the range of cells causing errors, and improve the user experience. However, excessive usage can lead to interruptions of work, limited display options, and limited interaction with cells. Therefore, we have to weigh the pros and cons and adapt the usage of the VBA MsgBox function in Excel with Range Address according to our requirements.
A basic understanding of the syntaxes and parameters of the MsgBox and Range Address combined with extensive examples provides an insightful understanding. Displaying the range address, cell address, active cell address, etc., can help us quickly verify the correct cells, locate errors, and improve user interaction.
This tutorial starts with the syntax and explains the arguments of a basic MsgBox, followed by a simple example. In turn, we’ll discuss the syntax and arguments of Range Address. Next, using our understanding of MsgBox, we’ll concentrate on displaying the range address, cell address, active cell address, etc. of a cell or range of cells using the MsgBox function.
The above video is an overview of this article, which represents how to display Range Address using MsgBox in Excel VBA. Here, the returned Range Address has the default A1 reference style which outputs the B5:B14 range.
Download Practice Workbook
You can download the following practice workbook to practice by yourself.
How to Launch and Insert Code in Visual Basic Editor in Excel
The Developer tab contains the VBA applications including creating and recording macros, Excel Add-ins, Forms controls, importing and exporting XML data, etc. This is our starting point for running VBA codes; just follow along.
📝 Note: By default, the Developer tab remains hidden. You can learn to enable the Developer tab by following this linked article.
Once enabled, move to the Developer tab, then click on the Visual Basic button in the Code group.
This launches a new window that has a familiar look to other Microsoft products. Now, to run VBA code, we need to insert our code somewhere. So, click the Insert tab and choose Module from the list. Afterward, we get a small Module window to insert our VBA code, and we hit the Run button or F5 key to execute the code.
The advantage of inserting the code in the Module is that we can apply this code to all the worksheets in this Excel workbook. Conversely, we can make our code only available for a specific worksheet.
Introduction to MsgBox Function in Excel VBA
In Excel VBA, the MsgBox function displays a message box with icons and prompts, so the user can interact with the interface or see the results of an input. Whether you are a beginner or an experienced Excel VBA programmer, understanding how to use the MsgBox function to display range addresses is an important skill to have in your toolkit.
MsgBox(Prompt, [Button As VbMsgBoxStyle = vbOkOnly], [Title], [HelpFile], [Context]) As VbMsgBoxResult
|Prompt||Required||A statement will be shown in the message box.|
|[Buttons]||Optional||Button and icon codes to display buttons and icons in the message box.|
|[Title]||Optional||Title or name of the message box.|
|[HelpFile]||Optional||Index or link which is assigned to the Help button in the message box.|
|[Context]||Optional||Index or specific topic number of the Help file.|
- Practical Example of Using MsgBox
Enough said about the MsgBox, let’s move on to the practical usage of this function.
Let’s suppose the Power Rating of Cars dataset is shown in the B4:D15 cells, where we have the Car Model, Maker, and Horsepower columns respectively.
In this scenario, we’ll check if the entered age is eligible for voting, that is if the age is greater than “18”. In this case, a MsgBox will display the confirmation message. However, if the age is less than “18”, then the MsgBox returns a non-eligibility message.
Moreover, the MsgBox also displays an error message in the case of invalid or blank entries.
Now, to apply this VBA code (follow the steps shown previously to insert a Module). To do this, insert the code into the Module window >> press the Run button.
Sub msgbox_voting_age_check() v_age = InputBox("Please enter your age: ") 'If statement checks whether age is less than 18 If v_age < 18 Then MsgBox "Sorry, you are not eligible to vote!" _ , vbCritical, "Error" 'ElseIf handles blank entries ElseIf v_age = vbNullString Then MsgBox "Please enter a valid age!" _ , vbCritical, "Error" 'Else statement runs if age greater than 18 Else MsgBox "Congratulations, you are eligible to vote!" _ , vbInformation, "Success" End If End Sub
In this section, we’ll explain the VBA code used to check the voter’s age and return a message in MsgBox.
- Initially, name the sub-procedure, here it is “msgbox_voting_age_check”.
- Next, use an InputBox function where the user can enter their age and store this entry in the “v_age” variable.
- Then, use an If Then Else statement to check if the entered age is greater or less than “18”.
- If the age exceeds “18”, then the Msgbox displays the confirmation of eligibility message, otherwise, it returns a non-eligibility message.
- In addition, in case the user enters an invalid or blank entry, then the MsgBox asks the user to enter the correct information.
Read More: How to Use MsgBox and InputBox in VBA Excel
Introduction to Range.Address Property in Excel VBA
The Range Address property returns the reference of a range as a string of text.
Range.Address (RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
|RowAbsolute||Optional||Returns row number as absolute reference when set to True.|
|ColumnAbsolute||Optional||Returns column number as absolute reference when set to True.|
|ReferenceStyle||Optional||Specifies the A1 or R1C1 reference style|
|External||Optional||Returns external or local reference based on True or False.|
|RelativeTo||Optional||Range object defining the starting point.|
10 Examples of Showing Range Address with MsgBox in Excel VBA
In this portion, we’ll apply the Range Address property and return the results in a MsgBox. The video below shows a demonstration of the application of the Range Address property in Excel VBA.
1. Displaying Range Address
The picture below shows the Range Address of the selected range in the MsgBox. In this case, we’ve chosen the B4:D15 range, and clicking the Run button shows the address of the selected range.
Insert the VBA code into the Module as shown below. In the following code, the Selection.Address property gives the Range Address as a text string, and the MsgBox concatenates the prompt with the Range Address using the Ampersand (&) symbol.
Sub Range_address() 'Returning the range address of selection MsgBox "The selected range is: " & Selection.Address End Sub
2. Showing Cell Address
Similarly, we can also display the address of the selected cell with the help of the Cell.Address property.
Copy and paste the code into the Module window >> hit the F5 key to run the code. In this situation, the Cells property takes two arguments “5” and “2” that represent the row and column numbers respectively.
Sub Cell_address() 'Returning the range address of cell in row 5 col 2 MsgBox "The cell address is: " _ & Cells(5, 2).Address End Sub
3. Demonstrating ActiveCell Address
The ActiveCell property can be implemented to show the Range Address of the currently active cell in the worksheet.
Enter the VBA code into the Module window >> click on Run. In this situation, the ActiveCell.Address method gives the cell address of the active cell as a string.
Sub ActiveCell_address() 'Returning the range address of active cell MsgBox "The range address of active cell is: " _ & ActiveCell.Address End Sub
4. Setting Range Address in a Variable
We can set the Range Address as a variable and return the result in a MsgBox.
For instance, in this code define the “var_address” variable as a string and store the Range Address of the B4:B15 range.
Sub Rng_address_variable() 'Defining var_address as string Dim var_address As String 'Setting the B5:B14 range in var_address var_address = Range("B4:B15").Address 'Returning the range address of var_address MsgBox "The range address is: " _ & var_address End Sub
Read More: Excel VBA: Show Multiple Values with Multiple Variables in MsgBox
5. Viewing Row Number from Range Address
In addition, we can determine the row number from the Range Address using the Range.Row property.
It is important to note, that the Range.Row property returns the row number of the first row in the range. For example, selecting the B4:C5 cells returns the first-row number of 4.
Sub row_num_from_address() 'Returning row number from range address MsgBox "The row number of the range address is: " _ & Range("B4:C5").Row End Sub
6. Getting Column Number from Range Address
Similarly, we can obtain the column number from the Range Address using the Range.Column property.
As a note, the Range.Column property provides the column number of the first column, that is to say, choosing the B4:C5 cells returns the first-column number of 2.
Sub col_num_from_address() 'Returning column number from range address MsgBox "The column number of" & _ " the range address is: " _ & Range("B4:C5").Column End Sub
7. Displaying Range Address Through Different Cell References in Excel VBA
7.1 Using Absolute Reference
Whenever we return the Range Address value in a MsgBox, the references contain an absolute cell reference by default.
Follow the steps discussed previously to insert a Module >> Copy and paste the code into the Module window >> hit the F5 key to run the code.
Sub Absolute_ref() 'Setting the B5:B14 range in the Rng variable Set Rng = Range("B5:B14") 'Returning the range address MsgBox "The range address with" & _ " absolute reference is: " & Rng.Address End Sub
In this code, we used the Set statement and Range object to store the B5:B14 range address in the “Rng” variable. Then we used the MsgBox function to return the range address.
7.2 Applying Mixed Reference
We can also provide mixed cell references to the Range Address by adding an extra argument, which is setting the RowAbsolute to False. This means that the columns are anchored (have a dollar sign), whereas the rows are not anchored.
Afterward, follow the steps shown above to insert code into the Module >> hit the F5 key to run the code.
Sub Mixed_ref() 'Setting the B5:B14 range in Rng variable Set Rng = Range("B5:B14") 'Returning the range address with mixed reference MsgBox "The range address with mixed reference is: " _ & Rng.Address(RowAbsolute:=False) End Sub
7.3 Displaying Range Address Without $ Sign
Last but not least, we can display the Range Address without the dollar sign by choosing the False value for both the RowAbsolute and ColumnAbsolute arguments.
Now, paste the VBA code into a Module >> hit the Run option.
In this case, the RowAbsolute and ColumnAbsolute arguments are taken as False, so the dollar signs are completely removed.
Sub without_dollar_sign() 'Setting the B5:B14 range in the Rng variable Set Rng = Range("B5:B14") 'Returning range address without dollar sign MsgBox "The range address with no dollar sign is: " _ & Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) End Sub
8. Showing Range Address Utilizing Reference Style in Excel VBA
In Excel VBA, we can reference cells using the A1 reference style and the R1C1 reference style, so let’s see them in action.
8.1 Using A1 Reference Style
The A1 reference style is the most common form of referencing that we use. For example, when we write the B5:B14 range, we are using the A1 reference style.
In the Range Address property, by default, the A1 reference style is set to True, but we can also enter xlA1 in the ReferenceStyle argument as shown below.
Sub A1_ref_style() 'Setting the B5:B14 range in the Rng variable Set Rng = Range("B5:B14") 'Returning the range address as A1 reference style MsgBox "The selected range is: " _ & Rng.Address(ReferenceStyle:=xlA1) End Sub
8.2 Employing R1C1 Reference Style
The R1C1 reference style is the other style of reference, where instead of the usual letters and numbers, we have row and column numbers. Here, R5C2:R14:C2 is the same as the B5:B14 reference.
In the Range Address property, we’ve entered xlR1C1 in the ReferenceStyle argument.
Sub R1C1_ref_style() 'Setting the B5:B14 range in the Rng variable Set my_rng = Range("B5:B14") 'Returning the range address as R1C1 reference style MsgBox "The range address" & _ " with R1C1 reference style is: " _ & my_rng.Address(ReferenceStyle:=xlR1C1) End Sub
9. Finding Range Address with Workbook and Worksheet Names in Excel VBA
We can also append the workbook and worksheet names when returning the Range Address in the MsgBox.
First, use the Worksheets and Range objects to specify the worksheet name (“Dataset”) and Range Address (B5:B15). Then, store them in the “Rng” variable and set the External argument to True. Eventually, this displays the Range Address including the workbook and worksheet names.
Sub rng_address_ws_wb_names() 'Setting the B5:B14 range in Dataset worksheet in Rng variable Set Rng = Worksheets("Dataset").Range("B5:B15") 'Returning workbook and worksheet name and range address MsgBox "The Range Address with Workbook and Worksheet Name is:" _ & vbLf & Rng.Address(External:=True) End Sub
10. Getting Range Address as a String in Excel
Moreover, we can store the Range Address as a String data type by inserting the code into the Module window >> pressing the F5 key to run the macro.
In this scenario, use the Set statement to enter the Range Address of the B4:B15 cells in the “Rng” variable. In turn, store this string data in the “rng_ads” variable which is shown in the MsgBox.
Sub rng_address_as_str() 'Setting the B5:B14 range in the Rng variable Set Rng = Range("B5:B14") 'Storing range address in rng_ads variable rng_ads = Rng.Address 'Returning range address of rng_ads MsgBox "The range address is: " & rng_ads End Sub
How to Display Values of a Range Address in MsgBox with Excel VBA
Alternatively, we can also return the values of the Range Address in a MsgBox.
Open the Visual Basic editor and insert the code into the Module window.
Sub return_val_of_range_address() 'Setting my_rng variable as selection Set my_rng = Selection 'Using For loop to count rows and columns and return their values For Row = 1 To my_rng.Rows.Count For col = 1 To my_rng.Columns.Count Values = Values & my_rng.Cells(Row, col).Value _ & vbTab & vbTab Next col Values = Values & vbNewLine Next Row 'Returning row and column values in message box MsgBox "The values of the selected range is: " _ & vbLf & vbLf & Values End Sub
- First, we used the Set statement and Selection property to set my_rng variable.
- Next, we applied a nested For Next statement to loop through the values in the rows and columns, and store them in the “Values” variable.
- Here, the vbTab generates four white spaces and vbLf is the linefeed character which moves the text to the next line.
- Lastly, we have employed MsgBox to return the values in a dialog box.
Pros and Cons of Using VBA MsgBox with Range Address Property in Excel
1. Verification of cell addresses: Using the MsgBox function to display the Range Address can quickly identify that we are working with the correct cells.
2. Debugging: We can find the range of cells causing an error by displaying the Range Address in our VBA code.
3. Improved user experience: When creating a VBA macro for others, displaying the Range Address in MsgBox can help users understand which cells are being affected thus making it more user-friendly.
1. Interruption of workflow: The MsgBox function can interrupt the execution of a VBA code by displaying a dialog box to the user. Extensive usage of this function means having to close lots of message boxes which can become ungainly.
2. Limited display options: Due to its limited display options and space, the MsgBox function may not be suitable for cases where we need to display a large amount of information about a cell or range of cells.
3. Limited interaction with cells: The MsgBox function is unable to interact with the cells; rather, it only displays information about the cells. In this case, we need to use a different approach to change the cell value
Frequently Asked Questions
1. What does Range Property do in VBA?
The VBA Range property represents a given cell or range of cells in a worksheet. You can specify a cell or range of cells using the Range property. For instance, Range(“B4”) returns the cell at row 4 and column B, while Range(“B4:D15”) returns the range of cells from B4 to D15.
2. How do I specify a Range in Excel VBA?
To specify a range, we may use the Set statement to select the Range Address with the help of the Range object in VBA. Conversely, we can also define a variable and directly enter the Range Address, like Range(“B4:D15”). Note that the Range Address must be enclosed in double quotation marks.
3. Can I display multiple range addresses in a MsgBox in Excel VBA?
Yes, by concatenating the addresses using the Ampersand (&) operator, you can display several Range Addresses in a MsgBox. A MsgBox containing the addresses for the ranges A1 and B1 is displayed, for instance, by the code that follows:
MsgBox Range("A1").Address & ", " & Range("B1").Address
4. How can I use VBA Range Address to refer to a dynamic range in VBA?
You can refer to a dynamic range using its Range Address in VBA with the help of the Offset function. An example of how to use the Offset function to refer to a dynamic range using its range address:
Sub Dynamic_rng() Dim rng As Range' set the starting cell and the size of the range to work with the' dynamic "rng" variable Set rng = Range("B4").Resize(10).Offset(0, 0) End Sub
Things to Remember
- We can refer to certain ranges using range variables rather than simply declaring range addresses in our code. Since defining range variables can improve the readability and maintenance of our code.
- It’s crucial to know the distinction between relative and absolute addressing when employing range addresses. A range that is provided relative to the current cell or selection is referred to as relative addressing. In contrast, absolute addressing designates a range that is determined by its precise address, regardless of the current cell or selection.
- By default, Range Address returns the absolute reference, but we can specify True/False to the AbsoluteRow and AbsoluteColumn arguments to obtain a mixed reference or remove the dollar sign completely.
- We can choose the reference style in Range Address by entering the A1 reference style or R1C1 reference style
- If the need arises, Range Address can also return the worksheet and workbook names along with the address of the range. Just set the External argument to True.
- Get the Range Address as a string by storing the Range object in a variable and returning the variable in MsgBox.
To sum up, displaying the Range Address using the MsgBox function in Excel VBA provides a powerful tool for verifying cell addresses, debugging, and improving the user experience. While there are some downsides to consider, such as the interruption of workflow and limited display options and interaction, generally, using the MsgBox function is worthwhile depending on the specific requirements.
We hope this article has provided a solid understanding of how to use the MsgBox function to display Range Addresses in Excel VBA. Using the abovementioned techniques, we hope you can become a more efficient programmer. However, if you have any suggestions or comments, don’t forget to share them with us.