Used within VBA

SOME WORKSHEET FUNCTIONS USED WITHIN VBA CREATE AN ARRAY... 

If you use a worksheet function within VBA that returns an array, the lower array index will be 1. Such worksheet functions include LI NEST, TRANSPOSE, MINVERSE, MMULT. Other functions that return arrays include the VBA function Caller when used with a menu command or toolbutton. 

In summary, the FT rheometry protocols described above and the associated data treatment yield a considerable number of information about the viscoelastic character of materials. Within less than 1 h, two samples are tested and the full data treatment is performed (using the present combination of VBA macros and MathCad routines). 

There are literally hundreds of VBA objects, properties and methods, and this can be confusing for the beginner. Chapter 14 focused cn the basic tools needed to create macros to automate chemical worksheet calculations how to transfer values from a sheet to a VBA module, how to perform calculations within a VBA module, how to perform logical branching and iterative looping, and how to send values back from a VBA module to a worksheet. In this chapter we ll use these tools to create some useful Sub procedures. 

First we discuss VBA, a powerful development environment used directly from within the Microsoft Office suite of programs. Typically, Word or Excel is most frequently used, since many LIMS and instrument software directly export reports in these formats. One of the primary advantages with VBA is the rich, intuitive object model that is provided by each of the Microsoft Office applications. The implementation of VBA as a cost-effective and first-line approach to data automation in drug discovery has been reported [76]. Bisset s book entitled Practical Pharmaceutical Automation contains many examples of Excel VBA in the laboratory setting [67]. 

Microsoft Office allows the option to record macros for performing repetitive tasks. Although macros are somewhat limited for the development of true VBA applications, macro recording can be used effectively to learn VBA or create templates of code that can be generalized with the addition of variables. If VBA for Microsoft Office is enabled, you only need to press alt-fi i to open the VBA Integrated Development Environment (IDE). A search of the on-line help for Application Object will provide an interactive object hierarchy that describes (with detailed code examples) each object or collection. There are literally thousands of objects within the Microsoft Office application object models. Another very useful tool within the VBA environment is the Object Browser, which can be invoked with the F2 key. The Object Browser can be particularly useful to learn the objects of external components that have been referenced from within VB or VBA. This method is an excellent way to get started with new object types and follow along with the documentation. An example is shown in Figure 18.3 from the Excel 2000 VBA IDE. In this example, the Excel is the automation client and Sciex Analyst method component... 

As its name suggests, VBA is closely related to Visual Basic but can typically only run code from within a host application rather than as a stand-alone application. It can, however, be used to control one application from another, for example, automatically creating a Word report from Excel data. 

The Act ivate method tells the current VBA program that any further references to an object (Range, for example) refer to cells of the active worksheet. Effectively, this statement makes the current worksheet the active one. For Excel files with multiple worksheets, this can be an important distinction. A VBA program with this statement in it can then be invoked from any of the worksheets, and all references to, for example. Cells (I, J) refer to objects within the worksheet from which the VBA program is executed. In other words, if the VBA program was created while Sheet 1 was active and later it is used when Sheet 2 is active, then Ce 11 s (l, J) refers to cells on Sheet 2. Without the Activate command, the cells in Sheet 1 would be referred to by default. 

Excel functions that have no VBA counterpart can be called from VBA. Eor example, if it is desired to use the Pi() function within a VBA program, the Appl ication method can be used as in the following Function ... 

---