Turn Excel range into VBA string

by

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

enter image description here

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

More Related Contents:

  1. Excel VBA: Range to String Array in 1 step
  2. Excel-VBA – Insert new first column in datafield array without loops or API calls
  3. Creating an Array from a Range in VBA
  4. Expanding column cells for each column cell
  5. How do I slice an array in Excel VBA?
  6. Check if a value is in an array or not with Excel VBA
  7. Matching values in string array
  8. Deleting Elements in an Array if Element is a Certain value VBA
  9. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  10. How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?
  11. Return Index of an Element in an Array Excel VBA
  12. Writing an array to a range. Only getting first value of array
  13. Faster way of using Sumifs()
  14. How to initialize a multidimensional array variable in vba for excel
  15. VBA using ubound on a multidimensional array
  16. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  17. Why am I having issues assigning a Range to an Array of Variants
  18. Subscript out of range error with an array – no idea why?
  19. VBA Excel large data manipulation taking forever
  20. Performance difference between looping range vs looping array
  21. Create Chart from Array data and not range
  22. Excel VBA Quickest way to sort an array of numbers in descending order?
  23. Simple VBA array join not working
  24. Excel VBA – How to Redim a 2D array?
  25. ReDim Preserve “Subscript Out of Range”
  26. how to repeat array and string [closed]
  27. How to search for string in an array
  28. Sorting a multidimensionnal array in VBA
  29. Call a Sub with a String
  30. How can I convert array to string in hive sql?

Leave a Comment