问题描述
将单元格范围转换为字符串的最佳方式是什么?
我有一个只需要一个字符串作为输入的函数,所以我需要将范围转换为一个字符串,同时保留尽可能多的格式(即它需要看起来像一个表或列表,而不仅仅是一个字符串)。
我尝试使用CStr(),以及从范围转换为数组,然后转换为字符串,但我只是收到错误。
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.
编辑:代码尝试
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
推荐答案
要在一个范围内使用逗号分隔的单元格值列表:
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.
要使用此功能:
Sub AnySubNameHere()
Dim rng As Range
Set rng = ActiveSheet.Range("A3:A10")
Dim myString as String
myString = RangeToString(rng)
End Sub
这篇关于如何将范围转换为字符串(VBA)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!