acba-tools
About Worksheet Functions
acba-tools
About Worksheet Functions
There are several hundreds of Worksheet Functions and they grouped by class for convenience.
All the 'Worksheet Functions' on the ACBA Audit Tools package are classed as 'User Defined'.
All formulae take up your computers working memory (RAM). In particular they re-calculate each time you amend a spreadsheet. If your fomulae link several spreadsheets together, the a change in one value can generate thousands of recalculations in all the linked spreadsheets. You will certainly notice the impact on the speed with which the file readjusts itself.
The standard worksheet formulae (SUM, COUNT, SUMIF, MID, LEN) and the standard arithmetic operators (+, -, /, *) all work very efficiently. Even so you will notice that worksheet recalculation slows up if you use enough of them.
Some types of worksheet formulae like (VLOOKUP, INDIRECT, OFFSET) take longer to recalculate because they may have to iterate through a process several times before they resolve. But they are built in Excel's primary programming language and they are still very fast.
The slowest of the worksheet functions are the 'User-Defined' ones. These are built in VBA (visual basic for applications). This is a compiled language provided by Microsoft for developers like ACBA(UK)LTD to construct additional specialised software and formulae for Excel users. Developers can build their software efficiently but even so it is not as fast Excel's original programming language.
Also the speed of the 'User-Defined' functions is affected by the complexity of their programming. Broadly speaking the ACBA user-defined split into two levels of complexity.
The direct number extraction and comparison functions are very efficient in terms of their programmed processes.
The extraction from string array functions have to process information from the whole string before the particular extraction activity can start. It an individual level it is too fast for a user notice the processing time required, but if replicated over many thousands of cells this will slow the re-calculation time of your spreasheet.
If processing large volumes of data which only needs to be calculated or analysed once, the user may choose over paste cells with values only. The practice within ACBA is to take a list of the formulae used to undertake the calculation before overwriting with values only.