CODEWITHSUNDEEP
vbaexcel
user567677
user567677

Reputation:

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

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.

Upvotes: 2

Views: 45966

Answers (2)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

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

Upvotes: 5

Dusty
Dusty

Reputation: 2333

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

Upvotes: 8

Related Questions