Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Access Efficiency: Arrays versus Worksheet Data
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 -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Access Efficiency: Arrays versus Worksheet Data
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" wrote in message ... 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 -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Access Efficiency: Arrays versus Worksheet Data
My data's not nearly that big, but I'll take the tip.
thanks. -- Neal Z "alondon" wrote: 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" wrote in message ... 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 -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading data arrays from multiple data files in excel | Excel Discussion (Misc queries) | |||
Excel worksheet retrieving data from Access | Links and Linking in Excel | |||
Free Tool (Add-In) for Improving Your Data Processing Efficiency | Excel Programming | |||
access my data from my master worksheet while calculation is don. | Excel Worksheet Functions | |||
Populating worksheet with data from Access | Excel Programming |