为什么我不能将字符串变量传递给 VBA 的 Range 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7352165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can't I pass a String variable into VBA's Range function?
提问by Jean-Fran?ois Corbett
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 中的范围内动态转置 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 中转义双引号很丑陋，这就是为什么我的奇怪赋值表达式看起来很奇怪，但是当我使用 MsgBox 时，它实际上给了我正确的值。
回答by Dusty
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:
当您使用 code 时Range("A1:C16").Select，引号不是字符串的一部分，而只是描述它。因此，您不需要通过转义引号将引号插入到您创建的字符串中。以下测试用例对我有用：
Dim trange As String
Dim slength As Integer
slength = 5
trange = "A2:C" & slength
MsgBox (trange)
Range(trange).Select
Range(trange).Value = 5
回答by Jean-Fran?ois Corbett
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 16by slengthin any of the examples above to make your variable-size range.
更换16由slength以上任何使你的可变大小范围的例子。
Also, it's good practice specify what worksheet you're referring to. Instead of plain Range(anything), use e.g.
此外，最好指定您所指的工作表。而不是平原Range(anything)，使用例如
Sheet1.Range(anything)
Worksheets("Sheet1").Range(anything)
or even better,
甚至更好，
With Sheet1
.Range(anything)
' other stuff on Sheet1
End With
