为什么我不能将String变量传递给VBA的Range函数？

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:

我正在尝试使用VBA在Excel中的Range上动态转置Recordset。当我给它一个静态范围时,我可以成功地进行换位:

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.

但是,当我尝试使用等效字符串并将其传递给Range函数时,它会失败。打印出来时我的变量trange是“A1:C16”(包括双引号)。我需要传递一个字符串的原因是因为字符串是从长度变量派生的,该变量可以是任何值。

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.

不幸的是,在VBA中转义双引号是丑陋的,这就是为什么我的trange赋值表达式看起来很奇怪但是当我使用MsgBox时,它实际上给了我正确的值。

2 个解决方案

#1

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:C16”),您可以说以下任何一种:

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.

在上面的任何一个例子中,用slength替换16来制作你的可变尺寸范围。

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

#2

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:

当您使用代码范围(“A1:C16”)。选择时,引号不是字符串的一部分,而只是描述它。因此,您不需要通过转义它们来创建您正在创建的字符串中的引号。以下测试用例适用于我:

Dim trange As String
Dim slength As Integer

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

#1