How to Show Range Address with MsgBox in Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Moving to the developer tab and clicking on the visual basic button

Once enabled, move to the Developer tab, then click on the Visual Basic button in the Code group.

Inserting module in the visual basic for applications window

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.

  • Syntax

MsgBox(Prompt, [Button As VbMsgBoxStyle = vbOkOnly], [Title], [HelpFile], [Context]) As VbMsgBoxResult

ARGUMENT REQUIRED/OPTIONAL EXPLANATION
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.

Overview of the dataset showing car models, maker, and horsepower values

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.

VBA code for basic usage of MsgBox

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.

VBA code for basic usage of MsgBox

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

Code Breakdown

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.

 


Introduction to Range.Address Property in Excel VBA

The Range Address property returns the reference of a range as a string of text.

  • Syntax

Range.Address (RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

  • Arguments
ARGUMENT REQUIRED/OPTIONAL EXPLANATION
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.

How to Showing Range Address with MsgBox in Excel VBA: 10 Examples

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

MsgBox showing the range address of the selected range

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.

VBA code for displaying range 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

Read More: Excel VBA MsgBox Examples


2. Showing Cell Address

MsgBox showing the cell address of the selected cell

Similarly, we can also display the address of the selected cell with the help of the Cell.Address property.

VBA code for displaying cell address of the selected cells

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

MsgBox showing the cell address of the active cell

The ActiveCell property can be implemented to show the Range Address of the currently active cell in the worksheet.

VBA code for displaying cell address of the active cell

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

MsgBox showing the range address of the variable

We can set the Range Address as a variable and return the result in a MsgBox.

VBA code for displaying range address of the variable

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

5. Viewing Row Number from Range Address

MsgBox showing the row number of the range address

In addition, we can determine the row number from the Range Address using the Range.Row property.

VBA code for displaying row number of the range address 

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

MsgBox showing the column number of the range address

Similarly, we can obtain the column number from the Range Address using the Range.Column property.

VBA code for displaying column number of the range address

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

Read More: Excel VBA: Develop and Use a Yes No Message Box


7. Displaying Range Address Through Different Cell References in Excel VBA

7.1 Using Absolute Reference

MsgBox showing the range address with an absolute cell reference

Whenever we return the Range Address value in a MsgBox, the references contain an absolute cell reference by default.

VBA code for displaying range address with absolute cell reference

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

MsgBox showing the range address with a mixed cell 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.

VBA code for displaying range address with mixed cell reference.

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

MsgBox showing the range address with no dollar 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.

VBA code for displaying range address with no dollar sign

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

MsgBox showing the range address in 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.

VBA code for displaying range address as 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

MsgBox showing the range address as 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.VBA code for displaying range address as R1C1 reference style.

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

MsgBox showing the range address with workbook and worksheet names.

We can also append the workbook and worksheet names when returning the Range Address in the MsgBox.

VBA code for displaying range address with workbook and worksheet names.

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

MsgBox showing the range address as a string

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.

VBA code for displaying range address as a string.

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

MsgBox showing the values instead of range address.

Alternatively, we can also return the values of the Range Address in a MsgBox.

VBA code for displaying the values instead of range address.

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

Code Breakdown:

  • 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

Pros:

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.

Cons:

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.

Wrapping Up

  • 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.

Download Practice Workbook

You can download the following practice workbook to practice by yourself.


Conclusion

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.


Related Article

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo