How to print range object as string

[Special K]

Suppose I have the following range object defined:

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:

MsgBox "test_range = " & test_range

but receive a "type mismatch" error.

The following also generates a "type mismatch" error:

MsgBox "test_range = " & CStr(test_range)

 

[Fazza]

hi,

from within VBA hit F2 to open the object browser. Search for range and review the properties & methods of ranges for options. Look at 'address'

for info http://msdn.microsoft.com/en-us/library/office/gg264621.aspx

and http://msdn.microsoft.com/en-us/library/office/aa164805(v=office.10).aspx

and one can F1 for help, too

hth

 

[Special K]

Thanks.

This works:

MsgBox "test_range = " & test_range.Address

 

[Fazza]

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

Name	Required/Optional	Data Type	Description
RowAbsolute	Optional	Variant	True to return the row part of the reference as an absolute reference. The default value is True.
ColumnAbsolute	Optional	Variant	True to return the column part of the reference as an absolute reference. The default value is True.
ReferenceStyle	Optional	XlReferenceStyle	The reference style. The default value is xlA1.
External	Optional	Variant	True to return an external reference. False to return a local reference. The default value is False.
RelativeTo	Optional	Variant	If 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]

this may be what you asked for

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