如何将范围转换为字符串 (VBA)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41777996/
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 can I convert a range to a string (VBA)?
提问by 1937827
What is the best way to convert a range of cells to a string? I have a function that only takes a string as input so I need to convert the range to a string, while retaining as much of the formatting as possible (i.e. it needs to look like a table or list, not just a string of characters). I've tried working with CStr(), as well as converting from a range to an array and then to a string, but I just get errors.
将一系列单元格转换为字符串的最佳方法是什么？我有一个只接受字符串作为输入的函数，所以我需要将范围转换为字符串，同时尽可能多地保留格式（即它需要看起来像一个表格或列表，而不仅仅是一串字符） . 我尝试过使用 CStr()，以及从范围转换为数组然后转换为字符串，但我只是遇到错误。
Edit: Code attempt
编辑：代码尝试
Dim email_answer As Integer
email_answer = MsgBox("Do you want to be emailled a copy of this schedule?", vbYesNo)
If email_answer = vbYes Then
Dim wb As Workbook
Dim to_send As Range
to_send = Range("D3", "D10")
If Val(Application.Version) < 14 Then Exit Sub
Set wb = ActiveWorkbook
With wb
MailFromMacWithMail body content:=CStr(to_send), _
mailsubject:="Schedule", _
toaddress:="email address", _
ccaddress:="", _
bccaddress:="", _
attachment:=.FullName, _
displaymail:=False
End With
Set wb = Nothing
End If
回答by Wolfie
To make a comma separated list of cell values in a range:
要制作一个范围内的单元格值的逗号分隔列表：
Function RangeToString(ByVal myRange as Range) as String
RangeToString = ""
If Not myRange Is Nothing Then
Dim myCell as Range
For Each myCell in myRange
RangeToString = RangeToString & "," & myCell.Value
Next myCell
'Remove extra comma
RangeToString = Right(RangeToString, Len(RangeToString) - 1)
End If
End Function
You could add extra functionality like inserting a semicolon instead of a comma if the row number increases.
如果行号增加，您可以添加额外的功能，例如插入分号而不是逗号。
To use this function:
要使用此功能：
Sub AnySubNameHere()
Dim rng As Range
Set rng = ActiveSheet.Range("A3:A10")
Dim myString as String
myString = RangeToString(rng)
End Sub
回答by user3598756
you could use this function:
你可以使用这个功能：
Function Rang2String(rng As Range) As String
Dim strng As String
Dim myRow As Range
With rng
For Each myRow In .Rows
strng = strng & Join(Application.Transpose(Application.Transpose(myRow.value)), "|") & vbLf
Next
End With
Rang2String = Left(strng, Len(strng) - 1)
End Function
which would return a string with linefeed character as range rows separator and pipes ("|") as columns separator
这将返回一个带有换行符的字符串作为范围行分隔符和管道（“|”）作为列分隔符
回答by Harm van Eerten
I know this question is already almost a year old, but I found a quick solution that works for me: You do have to create a reference to Microsoft Forms 2.0 Object Library to use the DataObject.
我知道这个问题已经快一年了，但我找到了一个对我有用的快速解决方案：您必须创建对 Microsoft Forms 2.0 对象库的引用才能使用 DataObject。
Public Function GetStringFromRange(RNG As Range) As String
Dim DOB As New MSForms.DataObject
RNG.Copy
DOB.GetFromClipboard
GetStringFromRange = DOB.GetText
End Function
回答by ASH
Any one of these functions will do it for you.
这些功能中的任何一个都可以为您完成。
Function ConCatRange2(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & ","
Next
ConCatRange2 = Left(sbuf, Len(sbuf) - 1)
End Function
OR
或者
Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k <> 1 Then
mergem = mergem & "," & rr.Value
End If
k = 2
Next
End Function
OR
或者
Function spliceUm(r As Range) As String
spliceUm = ""
For Each rr In r
spliceUm = spliceUm & rr.Value & ";"
Next
End Function
回答by Lerisson Felipe Pereira
You can use the following solution to convert a range to a string in VBA:
您可以使用以下解决方案在 VBA 中将范围转换为字符串：
Sub convert()
Dim rng As Range, cell As Range
Dim filter As String
filter = ""
Set rng = Selection
For Each cell In rng
If Not cell Is Nothing Then
filter = """" & cell & """" & "," & filter
End If
Next cell
End Sub
回答by Rohan Moore
Given the apparent need to iterate the range, I'd imagine it'd be considerably quicker to copy the range to an array first, and build the string by looping the array.
鉴于显然需要迭代范围，我认为首先将范围复制到数组并通过循环数组来构建字符串会快得多。
回答by Pixel_95
This does the job, even with the Range in another Worksheet
即使使用另一个工作表中的范围，这也可以完成工作
Function RangeToString(ByVal myRange As range) As String
RangeToString = ""
If Not myRange Is Nothing Then
RangeToString = "=" & myRange.Worksheet.Name & "!" & myRange.Address
End If
End Function
回答by user9813775
There is a much easier way. Assuming the variable rng is a range, then writing:
有一个更简单的方法。假设变量 rng 是一个范围，那么写成：
rng = Left(rng,Len(rng))
rng = Left(rng,Len(rng))
will miraculously turn rng into a string.
将奇迹般地将 rng 变成字符串。
