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

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

CodePudding user response：

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

CodePudding user response：

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