在 VBA 中将范围转换为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7563381/
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
Convert range to string in VBA
提问by bavman
I need help converting a range to a usable string. I'm not 100% sure of what I need, because I have little VBA or VB.NET experience, but ultimately i want to take a bunch of cells like B32to J52and perform linest function on C-J(as y values) and B(as x value).
我需要帮助将范围转换为可用字符串。我不是 100% 确定我需要什么，因为我几乎没有 VBA 或 VB.NET 经验，但最终我想拿一堆像B32to一样的单元格J52并在C- J（作为 y 值）和B（作为 x价值）。
I think if I can learn to pop out a string that says "Bxx:Jyy"Then i'd be able to tell it do linest(Cxx:Cyy, Bxx:Byy, true, false).
我想如果我能学会弹出一个字符串，上面写着"Bxx:Jyy"Then 我就可以告诉它 do linest(Cxx:Cyy, Bxx:Byy, true, false)。
Is there an easier way to do this? Anyway, all I have right now is some code to get the range from the user (just got it from Google). If someone could just help me get the string like stated above, I think I can manage the rest.
有没有更简单的方法来做到这一点？无论如何，我现在所拥有的只是一些代码来从用户那里获取范围（刚刚从谷歌那里得到）。如果有人可以帮助我获得如上所述的字符串，我想我可以处理其余的。
Dim oRangeSelected As Range
Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
"SelectARAnge Demo", Selection.Address, , , , , 8)
回答by brettdj
In this case you want the user selected range (not pre-existing selection), so
在这种情况下，您需要用户选择的范围（不是预先存在的选择），所以
MsgBox oRangeSelected.Address
[Update] Actually, reading your question more closely, you can use ranges as is with LINEST, ie this sub gets a user range for X and Y and then feeds LINEST. The results are stored in an array, MyArr, which can be returned to the user.
[更新] 实际上，更仔细地阅读您的问题，您可以像使用 LINEST 一样使用范围，即此子获取 X 和 Y 的用户范围，然后提供 LINEST。结果存储在数组 MyArr 中，该数组可以返回给用户。
Sub Sample()
Dim oRangeSelected1 As Range
Dim oRangeSelected2 As Range
Dim myArr()
Set oRangeSelected1 = Application.InputBox("Please select a X range of cells!", "Select X Demo", Selection.Address, Type:=8)
Set oRangeSelected2 = Application.InputBox("Please select a Y range of cells!", "Select Y Demo", , Type:=8)
If oRangeSelected1.Columns.Count + oRangeSelected2.Columns.Count > 2 Then
MsgBox "Please select a single column range only"
Exit Sub
End If
If oRangeSelected1.Cells.Count <> oRangeSelected2.Cells.Count Then
MsgBox "Ranges are of different length"
Exit Sub
End If
myArr = Application.WorksheetFunction.LinEst(oRangeSelected1, oRangeSelected2)
MsgBox "slope is " & Format(myArr(1), "0.00") & " & intercept is " & Format(myArr(2), "0.00")
End Sub
回答by PaulStock
If you use Selected.Addressthat will display your range as a string.
如果您使用Selected.Address它，则会将您的范围显示为字符串。
