How to print range object as string

Special K

Board Regular
Joined
Jun 20, 2011
Messages
62
Suppose I have the following range object defined:

Code:
Set test_range = Range(Cells(test_row_start, test_col), Cells(test_row_end, test_col))

Suppose test_range maps to the range A1:A10 (i.e. test_row_start = 1, test_col = 1, test_row_end = 10). How can I display "A1:A10" in a popup box for debug purposes?

I have already tried this:

Code:
MsgBox "test_range = " & test_range

but receive a "type mismatch" error.

The following also generates a "type mismatch" error:

Code:
MsgBox "test_range = " & CStr(test_range)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Great. VBA's help has info on address. So you can have the result in different ways. I've tried to copy it below to give the idea, though best to refer to help.

regards

Excel Developer Reference
Range.Address Property

<tbody>
</tbody>

Returns a String value that represents the range reference in the language of the macro.Syntax
expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
expression A variable that represents a Range object.
Parameters
NameRequired/OptionalData TypeDescription
RowAbsoluteOptionalVariantTrue to return the row part of the reference as an absolute reference. The default value is True.
ColumnAbsoluteOptionalVariantTrue to return the column part of the reference as an absolute reference. The default value is True.
ReferenceStyleOptionalXlReferenceStyleThe reference style. The default value is xlA1.
ExternalOptionalVariantTrue to return an external reference. False to return a local reference. The default value is False.
RelativeToOptionalVariantIf RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1, you must include a starting point for the relative reference. This argument is a Range object that defines the starting point.

<tbody>
</tbody>

Remarks

If the reference contains more than one cell, RowAbsolute and ColumnAbsolute apply to all rows and columns.
Example

The following example displays four different representations of the same cell address on Sheet1. The comments in the example are the addresses that will be displayed in the message boxes.
 

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524
this may be what you asked for

Code:
Sub aaaa()


    Dim i As Integer
    Dim msg As String
    
    debug.print "this prints to Immediate window in VBA editor, best for debugging"
    
    For i = 1 To 10
    '    msg = msg & i & vbTab & Cells(1, 1).Cells(1, i).Value & vbCrLf
        msg = msg & i & vbTab & Cells(test_row_start, test_col).Cells(1, i).Value & vbCrLf
    Next i
    
    MsgBox msg
    


End Sub
 

Forum statistics

Threads
1,137,302
Messages
5,680,710
Members
419,929
Latest member
Atlas Quinn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top