Spreadsheets macros

Finally, we note that programming of a sort is possible using the spreadsheet Macro feature. This allows one to record a sequence of operation (Copy, Move, Sort, Regress, etc.) carried out in the spreadsheet. This sequence is given a name and can then be repeated later by Playing this macro. Macros can be fairly sophisticated since it is possible to use them to branch to different locations, to ran other Macros, and to perform Do loops in a manner as done in Visual Basic, C, or other programming languages. 

Level 1 If the data is to be manipulated using spreadsheet macros, the spreadsheet application must be considered as a GAMP Category 5 component. Macros in Microsoft Excel are Visual Basic computer programs written specifically for that application. While they may be simpler than the other customized elements of the integrated CDS, they stiU carry the same liabilities. 

A spreadsheet macro is a computer program that can be called from inside a spreadsheet. The simplest macros merely allow the user to record a sequence of spreadsheet operations, which the computer then memorizes, and can repeat upon demand. All modern spreadsheets have facilities for recording and using macros to repeat a given series of instructions. While these can be quite useful for routine, repetitive computational tasks, their benefit in the present context is small, although we may occasionally use them to find out how Excel encodes visually entered information. 

A spreadsheet macro is a computer program, and in the spreadsheet is listed on a separate sheet called a module. It differs from a normal worksheet in that it does not contain any cells, but is blank except for the text printed on it. In Excel 5 and Excel 95, modules are conveniently stored as sheets of the workbook to which they belong. In fact, when you select Insert => Macro, you will find three choices Module, Dialog, and MS Excel 4.0 Macro, of which you select Module. Likewise, when you right-click on the sheet tab, and select Insert, you will get a choice of Worksheet, Chart, Module, Dialog, and MS Excel 4.0 Macro, of which you again select Module. When you start a new project, use either method to generate a new module sheet, with the default... 

Application software LIMS, EDMS, MRP II system, spreadsheet macros Bug fix, patch, new version, changed macro in the application software... 

The simple spreadsheet macro dryco.xls (available on the companion website) calculates the distribution of temperatures in a core (in downloadable file DRYCORE) without any refrigeration except for the radiation heat transfer towards the vessel and towards the surrounding concrete cavity. The calculation is a simplified one and is based on that used for the Rasmussen Report (Rasmussen, 1978). 

A typical problem solved by the spreadsheet macro is the following one ... 

Unlike most non-parametric methods, Theil s method involves fairly tedious calculations, so a computer program such as a spreadsheet macro is necessary in practice. It should be noted that non-parametric methods for fitting curves are also available, but these are beyond the scope of the present book. 

Figure 11.21 Typical codes used to simulate the current transient for a diffusion-controlled reaction on a planar electrode. Written in Visual Basic as found in a spreadsheet macro. For a given time f, diffusion does not affect the concentration profile beyond a distance equal to hence the...

Solution The following is a complete program for performing the calculations. It is written in Basic as an Excel macro. The rather arcane statements needed to display the results on the Excel spreadsheet are shown at the end. They need to be replaced with PRINT statements given a Basic compiler that can write directly to the screen. The programming examples in this text will normally show only the computational algorithm and will leave input and output to the reader. 

For reasons discussed above, we needed a complementary, ancillary tool for comparison of the mass spectra of components from multiple urine samples. We desired that the procedure have several characteristics (1) requires little if any manual data entry by the operator (2) utilizes data automatically generated by ChemStation and organized into Microsoft Excel spreadsheets (3) displays both retention times and mass spectral data in the same window (4) minimizes subjective operator judgments and (5) is simple and rapid to use. What emerged after several iterative improvements are the FindPeak macros discussed below. These are largely due to the expertise of Y. Aubut, with valuable input from J. Eggert. 

The Excel macro findpeak.xls imports the data from the. CSV files into an Excel spreadsheet. This imports all of the. CSV files containing all the mass spectra of one Total Ion Chromatogram. The user may import as many data files as desired for comparisons. 

A simple model of the batch process was compiled using a commercial spreadsheet program, using finite time elements. A macro was written to obtain convergence of start and end of batch conditions. This model was simple to construct and proved satisfactory in calculating the batch profiles for operation without, and subsequently with, the catalytic reactor on-line. The values obtained for operation without the inloop catalyst compared well with plant values. 

Huber has published primers with detailed information on the validation of macros and spreadsheet applications [11] and on the qualification of network infrastructure and the validation of networked systems [12]. 

Validation of software loaded on a computer that is used to control the equipment, to acquire analytical raw data, to process the data, and to print and store the data. Software typically includes operating systems, standard applications software, and software written by a specific user, such as macros or spreadsheet calculations. 

Figure 17.3. Life cycle of macros and spreadsheet programs. (With permission from Ref. [11].)...

L. Huber, Using Macros Spreadsheets in a Regulated Environment, part of the Macro Spreadsheet quality package, Labcompliance, www.labcompliance.com/books/macros, May 2002. 

The design section will include information about purpose, system and functional overview, data flow, data sheet formats, and test strategy. The section describes how the requirements are addressed in the spreadsheet. The main part of the design documentation contains a printout of the spreadsheet in the formula view, with line and column headers. The purpose of the individual formulas and/or macro must be described, including the sequence in which the calculation will be performed. In addition, the following information should be provided ... 

If spreadsheets are to be used it is prudent to ensure that any macros and procedures are correct and that the in-built statistical functionality is appropriate It is very easy to select the s function instead of s i. Remember that... 

The next type of standard output is the spreadsheet. These file formats use comma-separated ASCII code, but also add calculation information and addressing information for the columns and rows they occupy. The simplest of these are. DIF files, which originated to allow information transfer between VisiCalc worksheets in the Apple II computer and have been retained as a standard format.. WK files are Lotus-1,2,3 formats and. XLS are Microsoft Excel formats that have become spreadsheet standards, allowing transfer of data, calculations, addresses, and macro programs. 

Write user-defined macros for applications routines to automate or enhance a spreadsheet for a particular purpose. 

There are a number of ways of creating macros. The most straightforward is via the Visual Basic Editor, which can be accessed via the Macro option of the Tools menu. Depending on your set-up and the history of the spreadsheet, you may then enter a blank screen. If this is so, insert a Module . This contains a series of user defined procedures (organised into Subroutines and Functions) diat can be run from Excel. 

The data must occupy a region of the spreadsheet starting at cell B2 so that the first row and column can be used for names of the objects or variables if necessary, or left blank. Of course, this limitation can easily be changed by instructors or readers wishing to modify the macro. A function size is also required. Each row must represent one sample, and each column a variable. 

Macro, spreadsheet calculations. Installation Qualification, Operational Qualification. 

Huber, L., Macro and Spreadsheet Quality Package, Labcompliance, 2002, www.labcompli-ance.com/books/macros. 

The powerful macro language capabilities of spreadsheets support a second category of application the control of simple analytical devices or production equipment. Such applications are again typically based on preconfigured templates and functions. 

With macros practically everything can be done in a spreadsheet. However, because macros catapult the class of the spreadsheet from 3 to 5 and lead to much more validation and maintenance work. 

---