Why can't I pass a String variable into VBA's Range function?

Question

I'm trying to dynamically transpose a Recordset over a Range in Excel using VBA. I can do the transposition succesfully when I give it a static range such as this:

Range("A1:C16").Select
Range("A1:C16").Value = Application.WorksheetFunction.Transpose(scores)

When I try to use an equivalent string and pass it in to the Range function, however, it fails. My variable trange when printed out is "A1:C16" (including the double quotes). The reason I need to pass it a string is because the string is derived from a length variable which could be any value.

This code below fails:

Dim trange As String
trange = """A1:C" & slength & """"
MsgBox (trange)
Range(trange).Select
Range(trange).Value = Application.WorksheetFunction.Transpose(scores)

Unfortunately, escaping double quotes in VBA is ugly and that's why my trange assignment expression looks strange but when I MsgBox it, it is in fact giving me the correct value.

Answer 1

When you use the code Range("A1:C16").Select, the quotes are not part of the string, but simply delineate it. Therefore, you don't need to insert quotes into the string you're creating by escaping them. The following test case works for me:

Dim trange As String
Dim slength As Integer

slength = 5
trange = "A2:C" & slength
MsgBox (trange)
Range(trange).Select
Range(trange).Value = 5

Answer 2

Using string concatenation to construct range addresses is a bad idea. It's messy and error-prone (as your example illustrates!).

Instead of Range("A1:C16"), you can say any of the following:

Range("A1").Resize(16, 3)
Cells(1, 1).Resize(16, 3)
Range(Cells(1, "A"), Cells(16, "C"))
Range(Cells(1, 1), Cells(16, 3))

There are probably more possibilities. The key is that none of them involve string concatenation.

Replace 16 by slength in any of the examples above to make your variable-size range.

Also, it's good practice specify what worksheet you're referring to. Instead of plain Range(anything), use e.g.

Sheet1.Range(anything)
Worksheets("Sheet1").Range(anything)

or even better,

With Sheet1
    .Range(anything)
    ' other stuff on Sheet1
End With