Turn Excel range into VBA string

Question

I would like to turn values in given range into VBA string where original cell values are separated by any chosen column delimiter and row delimiter. Delimiters could be one character or longer strings. The row delimiter is the string at the end of the line. The string should be done just as we read text from left top corner, from left to right, to bottom right corner. Here is an example of the VALUES in range A1:C5: ``` +----+----+----+ | A1 | B1 | C1 | +----+----+----+ | A2 | B2 | C2 | +----+----+----+ | A3 | B3 | C3 | +----+----+----+ | A4 | B4 | C4 | +----+----+----+ | A5 | B5 | C5 | +----+----+----+ ``` Desired results is a VBA string: ``` A1,B1,C1@$A$2,$B$2,$C$2@A3,B3,C3@A4,B4,C4@A5,B5,C5@ ``` For the sake of readability I will show it like this: ``` A1,B1,C1@ A2,B2,C2@ A3,B3,C3@ A4,B4,C4@ A5,B5,C5@ ``` As a column delimiter I have chosen `,` (comma), and as a row delimiter `@` sign. Of course these could be any characters like `
`. The reason why I want fast cooking of the s…

Accepted Answer

To optimize performance my function emulates a String Builder.

Variables

- Text: A very large string to hold the data

- CELLLENGTH: A contant that determines the size of the BufferSize

- BufferSize: The initial size of Text string

- Data(): An Array derived from the source range

As the rows and columns of the Data() array are iterated over the current element (`Data(x, y)`) value replaces a portion of the Text string. The text string is resized as needed. This reduces the number of concatenations immensely. The initial BufferSize is set pretty high. I got my best results, 0.8632813 Second(s), by reducing CELLLENGTH to 25.

Download Sample Data from Sample-Videos.com

Results

Code

```
Function getRangeText(Source As Range, Optional rowDelimiter As String = "@", Optional ColumnDelimiter As String = ",")
    Const CELLLENGTH = 255
    Dim Data()
    Dim text As String
    Dim BufferSize As Double, length As Double, x As Long, y As Long
    BufferSize = CELLLENGTH * Source.Cells.Count
    text = Space(BufferSize)

Data = Source.Value

For x = 1 To UBound(Data, 1)
        If x > 1 Then
            Mid(text, length + 1, Len(rowDelimiter)) = rowDelimiter
            length = length + Len(rowDelimiter)
        End If

For y = 1 To UBound(Data, 2)
            If length + Len(Data(x, y)) + 2 > Len(text) Then text = text & Space(CDbl(BufferSize / 4))
            If y > 1 Then
                Mid(text, length + 1, Len(ColumnDelimiter)) = ColumnDelimiter
                length = length + Len(ColumnDelimiter))
            End If

Mid(text, length + 1, Len(Data(x, y))) = Data(x, y)
            length = length + Len(Data(x, y))
        Next
    Next

getRangeText = Left(text, length) & rowDelimiter
End Function
```

Test

```
Sub TestGetRangeText()
    Dim s As String
    Dim Start: Start = Timer

s = getRangeText(ActiveSheet.UsedRange)

Debug.Print "Execution Time: "; Timer - Start; "Second(s)"
    Debug.Print "Rows: "; ActiveSheet.UsedRange.Rows.Count; "Columns: "; ActiveSheet.UsedRange.Columns.Count
    Debug.Print "Result Length: "; Format(Len(s), "#,###")
End Sub
```

Turn Excel range into VBA string

Solution

Problem

Related problems