vba - Excel范围到平面字符串

Question

我正在尝试将 Excel 范围读取为字符串类型的变量。

目前我已经通过解决方法实现了它。我将范围复制到剪贴板并使用了 ReadClipBoard读取剪贴板作为分配给变量的函数。这种方法效率不高，而且有时由于 VBA 的剪贴板问题而导致错误。

解决方法代码:

 Dim variable as string
 Range("A1:C5").Copy
 variable = ReadClipBoard()'Function that returns clipboard text

有更好的方法吗？

Answer 1

这会将每一行转换为制表符分隔的字符串，并将整个范围转换为行分隔的字符串。

Public Function RangeToText(ByRef r As Range)

    Dim vaData As Variant
    Dim aOutput() As String
    Dim i As Long
    Dim wf As WorksheetFunction

    Set wf = Application.WorksheetFunction

    'Put range into a two dim array
    vaData = r.Value

    'Make one dim array the same number of rows
    ReDim aOutput(1 To UBound(vaData, 1))

    'Make strings With tabs out of each row
    'and put into one dim array
    For i = LBound(vaData, 1) To UBound(vaData, 1)
        aOutput(i) = Join(wf.Index(vaData, i), vbTab)
    Next i

    'join all the strings into one multi-line string
    RangeToText = Join(aOutput, vbNewLine)

End Function

在即时窗口中

?rangetotext(sheet1.Range("A1:C5"))
Here    Here    Here
is  is  is
some    some    some
column 1    column 2    column 3
text    text    text

Index工作表函数用于一次只处理一行，因为Join需要一个一维数组