In excel/vba what is the most efficient way to dump a string array into a worksheet?

Question

I need to write a lot of strings into a lot of cells quickly.

Here is the code I am currently using

Sub CopyArrayToWorksheet(myarray() As String, myworksheet As Worksheet, ArrayStart As Long, ArrayEnd As Long, SheetFirstRow As Long)
    
    If SheetFirstRow = -1 Then SheetFirstRow = GetLastRow(myworksheet) + 1
    
    myworksheet.Cells(1, 1).Select
    
    Dim x As Long, y As Long
    Application.ScreenUpdating = False
    For x = ArrayStart To ArrayEnd
        For y = LBound(myarray, 2) To UBound(myarray, 2)
            'myworksheet.Cells(SheetFirstRow, y + 1).NumberFormat = "@"
            myworksheet.Cells(SheetFirstRow, y + 1) = myarray(x, y)
        Next y
        SheetFirstRow = SheetFirstRow + 1
    Next x
    Application.ScreenUpdating = True
    
End Sub

This works, this used the be fast yesterday, it would write about 30'000 cells in a few seconds and now it takes minutes ! Nothing has changed but alas I cannot find what is wrong.

The reason why my data is in a 2 dimension string array is that it was easier and faster for me to perform operations on an array first and I really prefer it that way.

I searched before this and got found to try the Cells(1, 1).Select , this does nothing apparent for me. There is also the screenupdating, no effect again. For reference this is an i5-9500 cpu with 16gb ram.

I have two leads I don't know yet how to implement

First is using "MS project tasks" which I do not yet understand how to use. And my worry is about putting that in a spreadsheet and it not working on my random colleage's computers ? Slow VBA macro writing in cells

The other is using the transpose but I can't find a good example AND it seems to be only for unidimensionnal arrays

I also found this suggestion but I'm not sure if that's a good fit for my case or if it would be any faster VBA Excel large data manipulation taking forever

you can try this: Application.Calculation = xlCalculationManual. It might be while inserting your strings excel is going to update the calculations. — Aldert, Oct 08 '22 at 05:34

score 1 · Answer 1 · answered Oct 08 '22 at 06:43

String Array to Worksheet

A Quick Fix

Sub CopyArrayToWorksheet( _
        myArray() As String, _
        ByVal myWorksheet As Worksheet, _
        ByVal ArrayStart As Long, _
        ByVal ArrayEnd As Long, _
        ByVal SheetFirstRow As Long)
    
    Dim rCount As Long: rCount = ArrayEnd - ArrayStart + 1
    
    Dim cStart As Long: cStart = LBound(myArray, 2)
    Dim cEnd As Long: cEnd = UBound(myArray, 2)
    Dim cCount As Long: cCount = cEnd - cStart + 1
    
    Dim Data() As String: ReDim Data(1 To rCount, 1 To cCount)
    
    Dim x As Long, y As Long
    Dim r As Long, c As Long
    
    For x = ArrayStart To ArrayEnd
        r = r + 1
        For y = cStart To cEnd
            c = c + 1
            Data(r, c) = myArray(x, y)
        Next y
        c = 0
    Next x
    
    If SheetFirstRow = -1 Then SheetFirstRow = GetLastRow(myWorksheet) + 1
    
    With myWorksheet.Cells(SheetFirstRow, "A").Resize(rCount, cCount)
        .Value = Data
        .NumberFormat = "@"
    End With
    
End Sub

Thanks, if I understand correctly, you evaluate the row and column end values before executing the for loop, instead of executing them on each iteration of the loop ? And all the data is places in the new data string array and only at the end the whole thing gets written with .value data. This last part does a huge difference as I discovered when implementing Red Hare's solution (from almost a minute to about half a second) — Shodan, Oct 19 '22 at 22:10

freeflow · Answer 2 · 2022-10-08T08:38:28.063

1

The following code may help you to a solution

Sub AssignArrayToExcelRange()

    Dim myArray() As String
    ReDim myArray(1 To 5, 1 To 5)
    myArray(1, 1) = "Hello"
    myArray(1, 2) = "Hello"
    myArray(1, 3) = "Hello"
    myArray(1, 4) = "Hello"
    myArray(1, 5) = "Hello"
    
    myArray(3, 1) = "Hello"
    myArray(3, 2) = "Hello"
    myArray(3, 3) = "Hello"
    myArray(3, 4) = "Hello"
    myArray(3, 5) = "Hello"
    
    myArray(5, 1) = "Hello"
    myArray(5, 2) = "Hello"
    myArray(5, 3) = "Hello"
    myArray(5, 4) = "Hello"
    myArray(5, 5) = "Hello"
    Sheets(1).Range("A1:e5").Value = myArray
    
    Sheets(1).Range("f1:j5").Value = WorksheetFunction.Transpose(myArray)
    
End Sub

edited Oct 08 '22 at 08:38

answered Oct 08 '22 at 08:20

freeflow

4,129
3
10
18

Thank you, I hope this works, it would be so elegant to replace most of my code with just one line basically ! Could you explain what is the difference between writing the array with .Range("A1:e5").Value = myArray and using WorksheetFunction.Transpose(myArray) ? Is one faster than the other ? I didn't know a 2d string array could just be assigned directly to a range's value property, that changes a lot ! – Shodan Oct 08 '22 at 21:49
And this is a bit off topic, but can the same thing be done from one array to another. Support I have array1(100,10) and array2(5,5) could I insert array2 into array1 at (28,3) for instance ? Something like array1(28,3) = array2 ? I don't think this syntax works but is there a function that does this efficiently somewhere ? At least, something faster than looping through each individual cell in the array ? – Shodan Oct 08 '22 at 22:02
""Could you explain what is the difference between writing the array with .Range("A1:e5").Value = myArray and using WorksheetFunction.Transpose(myArray) "". Open a new empty workbook. Add a new module. Run the code and look at the output. – freeflow Oct 10 '22 at 08:07
You cannot overwite blocks withing a VBA array by assigning a smaller array. This can be done, but not in a way you imagine. Create a new sheet to be used for temporary array calculations. Assign the first array to a worksheet range, assign the second array to a range within the first array range, then assign the first worksheet range back to a VBA array. – freeflow Oct 10 '22 at 08:10

score 1 · Answer 3 · answered Oct 08 '22 at 13:03

I wrote for myself for these occasions (And use it many times):

 Public Sub ArrayToRange(theArray As Variant, firstCell As Range)
        Dim w As Worksheet
        Dim iStartRow As Integer, iStartCol As Integer
            iStartRow = firstCell.Row
            iStartCol = firstCell.Column
       Set w = firstCell.Parent
            With w
                .Range(.Cells(iStartRow, iStartCol), .Cells(iStartRow + UBound(theArray, 1) - LBound(theArray, 1), iStartCol + UBound(theArray, 2) - LBound(theArray, 2))).Value = theArray
            End With
    End Sub

To Test it:

Sub testit()
Dim i As Long, k As Long
Dim a(1 To 10, 1 To 3) As String
For i = 1 To 10
 For k = 1 To 3
    a(i, k) = i & " : " & k
 Next k
Next i
ArrayToRange a, ActiveSheet.Range("b3")
End Sub

Thank you, I dropped in that function into my code, called it with ArrayToRange myarray, myworksheet.Range("A" & SheetFirstRow). The result for my 707 rows by 26 col query is times going for 51770 ms average down to 400ms and it seems not to increase with more rows. In fact copying a single row with this method takes about 30ms longer than writing all 707 rows ! — Shodan, Oct 13 '22 at 04:00

In excel/vba what is the most efficient way to dump a string array into a worksheet?

3 Answers3

String Array to Worksheet