vba 如何将字符串转换为范围对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2911577/
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
How to convert a String to a Range Object
提问by nimo
Is there anyway to convert a string value to a Range object ? I'm having a function which takes a Range object as a argument and need to pass a single string parameter to it
反正有没有将字符串值转换为 Range 对象？我有一个函数，它接受一个 Range 对象作为参数，并且需要向它传递一个字符串参数
Thank You
谢谢你
回答by Alex K.
A string with a cell address? if so:
带有单元格地址的字符串？如果是这样的话：
Dim r As Range: Set r = Range("B3")
MsgBox r.ColumnWidth
回答by Dick Kusleika
I don't like this one bit, but if you can't change the function that requires a range, you could create a function that converts a string to a range. You'd want to be sure that the only thing the first function cares about is the Value or Text properties.
我有点不喜欢这个，但是如果您不能更改需要范围的函数，则可以创建一个将字符串转换为范围的函数。您希望确保第一个函数唯一关心的是 Value 或 Text 属性。
Function FuncThatTakesRange(rng As Range)
FuncThatTakesRange = rng.Value
End Function
Function ConvertStringToRange(sInput As String) As Range
Dim ws As Worksheet
Set ws = Workbooks.Add.Sheets(1)
ws.Range("A1").Value = sInput
Set ConvertStringToRange = ws.Range("A1")
Application.OnTime Now + TimeSerial(0, 0, 1), "'CloseWB """ & ws.Parent.Name & """'"
End Function
Sub CloseWb(sWb As String)
On Error Resume Next
Workbooks(sWb).Close False
End Sub
Use in the Immediate Window like
在立即窗口中使用，如
?functhattakesrange(convertstringtorange("Myvalue"))
回答by sedreddin
Here is my solution that involves no need for a new function.
这是我的解决方案，不需要新功能。
1.Make dynamic string variable first
1.先做动态字符串变量
2.Then finalize it by creating a range object out of this string via a range method: Set dynamicrange= range (dynamicstring)
2.然后通过范围方法从这个字符串中创建一个范围对象来完成它：Set dynamicrange= range (dynamicstring)
You can manipulate dynamicstring as you want to, I just kept it simple so that you can see that you can make range out of a string variable.
您可以根据需要操作 dynamicstring，我只是保持简单，以便您可以看到可以从字符串变量中获取范围。
Sub test()
Dim dynamicrangecopystring As String
Dim dynamicrangecopy As range
dynamicrangecopystring = "B12:Q12"
Set dynamicrangecopy = range(dynamicrangecopystring)
End Sub
回答by Charles Williams
Why not change the function argument to a variant and then in the function determine Using VarType etc) if you have been passed a Range and use error handling to check for a string which can be converted to a range or a string that cannot be converted to a range ?
为什么不将函数参数更改为变体，然后在函数中确定 Using VarType 等），如果您已传递范围并使用错误处理来检查可以转换为范围的字符串或无法转换为的字符串范围？
回答by Daniel H
Let's say Sheet1!A1 has the text value "Sheet1!B1" and Sheet1!B1 has the value "1234". The following code will use the range address stored as text in A1 as an input and copy the range B1 to A2:
假设 Sheet1!A1 的文本值为“Sheet1!B1”，Sheet1!B1 的值为“1234”。以下代码将使用以文本形式存储在 A1 中的范围地址作为输入，并将范围 B1 复制到 A2：
Sub Tester()
Sheet1.Range(Range("A1")).Copy
Sheet1.Range("A2").PasteSpecial xlPasteAll
End Sub
回答by Andrew
This simple function will convert string arguments into a range object, usable in other excel functions.
这个简单的函数将字符串参数转换为一个范围对象，可用于其他 excel 函数。
Function TXT2RNG(text) As Variant
Set TXT2RNG = Range(text)
End Function
