Excel memory management

[pulsecoding]

Hi all,

I wanted to know that how excel works when large amount of excel file is opened.
I have a workbook with 30000 rows and 20 columns filled data.
Using macro I read each line & then each column for some validation.

so when this workbook is opened is all 30000*20 cells are loaded into working memory or when cell request comes then only it gets loaded into memory?

is it good to use array to read some 200 rows first and then checking each row and each column in this array or read directly?


thanks

 

[ChrisOswald]

If i'm not mistaken, the entire excel file is put into RAM when it's opened. I read something online this morning (actually, it was about using Access over a WAN, but it mentioned Excel's use of ram) that supports that, but, take it with a grain of salt.

I did whip up a little bit of code to time out the methodology difference:
(spreadsheet had 20 columns, 1 header row and 30,000 rows of values from =rand() function))

Sub timetest()
    Dim a                               As Variant
    Dim t As Single, total1 As Single, total2 As Single, total3 As Single
    Dim b(1 To 20, 2 To 300001)         As Double
    Dim i As Long, j                    As Long
    Dim dummy As Double
 
    t = Timer()
    a = Range("A2:T30001").Value
    total1 = Timer() - t
 
    t = Timer()
    For i = 1 To 30000
         For j = 1 To 20
            dummy = a(i, j)
         Next j
    Next i
    total2 = Timer() - t
    t = Timer()
    With Worksheets("Sheet2")
        For i = 2 To 30001
            For j = 1 To 20
                b(j, i) = .Cells(i, j).Value
            Next j
        Next i
    End With
    total3 = Timer() - t
 
Debug.Print "Time to read array to variable array: " & total1 _
            & Chr(13) & "Time to loop through each element " & total2 _
            & Chr(13) & "Time to loop through spreadsheet " & total3
End Sub

and got these values in return

Time to read array to variable array: 0.203125
Time to loop through each element 0.046875
Time to loop through spreadsheet 3.171875

I do know that several of the MVP's state that reading a range array to a variable range is the quickest way to load an array, so it'd probably be faster to loop through an array than looping through the cells on the worksheet.