XL speed problem

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.

Here is the function that (badly) needs optimisation

VB Code:

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

From the other thread on this issue:

Quote:

---

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