Data Access Efficiency: Arrays versus Worksheet Data

[Neal Zimm]

Hi All,
In my app I have a worksheet, DataStore, with "validating" tables
of character and numeric data. It's hidden and the
data changes rarely.

As I wrote the function below, I cloned it from the code
that loads two arrays from DataStore, at workbook
open.
1) reserved worksheet names
2) Those in 1) that each workbook must have.

(The arrays are global public declared variables)
The code works fine, and I have not done any timings
yet, but I began to wonder, Why have the arrays at all ?

What are the pro's and cons of having the function below
go directly after the cells in DataStore versus checking
the arrays as it does now? With a computer of decent size,
I would assume that just about all the Excel data is in
memory anyway.
Thanks, Neal

Function zWsNames_AllReqdF(IWrkBkName As String) As Boolean
' Check a workbook to see if all required worksheets are there.
Dim Qty As Integer
zWsNames_AllReqdF = True
For Qty = 1 To UBound(RmRsrvWsNamesAy) 'reserved w/s names
' Exist/NotExist homegrown worksheet function
If NotExist = zWs_ExistF(IWrkBkName, RmRsrvWsNamesAy(Qty)) Then
If RMbReqdWsNamesAy(Qty) = True Then 'required sheets
MsgBox RmRsrvWsNamesAy(Qty) & " worksheet is missing from
workbook." _
, vbCritical, "Workbook: " & IWrkBkName & " Error"
zWsNames_AllReqdF = False
End If
End If
Next Qty
End Function

 

[alondon]

Neal,

You can read/write to an array about 100 times faster than reading or
writing to a worksheet cell. So if you are minipulating a lot of data,
always use arrays. Read the entire range you are working with into a
variant arrray, make the necessary changes then write the arrray back to the
range. Lightening fast.

My distributor sales forecast package uses ranges that have 50,000 elements.
I can change 5000 elements in less than 2 seconds using arrays. If I wrot
to cells with Application.ScreenUpdating = False, it would probably take 3
or 4 minutes. Of course using arrays can take a lot more memory so always
redimention the array when you are done with it!

APL

 

[Neal Zimm]

My data's not nearly that big, but I'll take the tip.
thanks.