XL speed problem

[yrwyddfa]

Are there any techniques, tricks or tips to accessing cell data from Excel t VB6.

The basic way seems horrendously slow. I am using the .cells(x,y).value method to retrieve data.

[yrwyddfa]

Here is the function that (badly) needs optimisation

Private Function EnumerateCells(ByRef oSheet As Excel.Worksheet, RestrictColumnsTo As Long, ByVal TotalCellCount As Long, ByRef TotalComplete As Long, ByRef UserCancelled As Boolean) As EXECL_CELL()
 
    On Error GoTo ERR_EnumerateCells
    
    Dim lRow As Long
    Dim lCol As Long
    Dim aCells() As EXECL_CELL
    Dim oRange As Excel.Range
    Dim fCancel As Boolean
    Dim lColumns As Long
    Dim lRows As Long
    
    fCancel = False
    
    With oSheet
    
        Set oRange = .UsedRange
        
        lColumns = oRange.Columns.Count
        lRows = oRange.Rows.Count
        
        If lColumns > RestrictColumnsTo Then
            lColumns = RestrictColumnsTo
        End If
            
        ReDim aCells(lColumns, oRange.Rows.Count)
        
        For lCol = 1 To lColumns
        
            For lRow = 1 To lRows
            
                With .Cells(lRow, lCol)
                    aCells(lCol, lRow).Colour = .Interior.Color
                    aCells(lCol, lRow).Value = .Value
                End With
                
                TotalComplete = TotalComplete + 1
                RaiseEvent ExcelScan((TotalComplete / TotalCellCount) * 1000, fCancel)
                
                If fCancel Then
                    Erase aCells
                    UserCancelled = True
                    Exit Function
                End If
                
                DoEvents
                
            Next lRow
            
        Next lCol
        
    End With
    
    Exit Function
    
ERR_EnumerateCells:
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Function

[si_the_geek]

From the other thread on this issue:

One major comment based on the code shown above - it is MUCH faster (about 10*) to read/write arrays of cells rather than a single cell at a time.

I think the property is FormulaArray (on a range), eg:

tmp_Array = Array (0, 1, 3.3, 2, 4.2) (or whatever code you want to fill the array!)

xlSheet.Range ("A1:E1").FormulaArray = tmp_Array

and:

tmp_Array = xlSheet.Range ("A1:E1").FormulaArray


(nb: I think you can omit FormulaArray, but the results are slightly different)

Although I can see that in your code you don't just work with the values - which means there is a high chance you couldn't optimise it, but fortunately in this case you set the same values so you could do:

oSheet.Range ( < name of range > ).Colour = .Interior.Color
or:
oSheet.UsedRange.Colour = .Interior.Color