如何将范围转换为字符串（VBA）？ [英] How can I convert a range to a string (VBA)?

问题描述

将单元格范围转换为字符串的最佳方式是什么？
我有一个只需要一个字符串作为输入的函数，所以我需要将范围转换为一个字符串，同时保留尽可能多的格式（即它需要看起来像一个表或​​列表，而不仅仅是一个字符串）。
我尝试使用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）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！