Practical Pharmaceutical Laboratory Automation

MICROSOFT EXCEL VISUAL BASIC MACROS The Importance of Consistent Laboratory Notebooks and Reports Running VBA Macros in Excel Setting Up a Microsoft Excel Visual Basic Macro Getting the Data into Excel Finding and Extracting Data Analysis and Manipulation of Data Activating Sheets within Different Workbooks Creating a Custom Report Worksheet Saving the Custom Report Worksheet Creating an Uploadable File for a Database ADVANCED MICROSOFT EXCEL VB MACRO TECHNIQUES Using Hidden Workbooks to Store and Run Macros Adding Custom Menu Items to Run Macros Keeping a Macro from Crashing When a Cell has Invalid Data Not all InputBoxes are Equal Using Templates and GUIs Opening and Using Other Applications within Excel The Importance of "Idle Time" The Magic of the "SendKeys" Statement Exchanging Data between Excel and Other Applications Programming Functions into Workbook Cells Adding Drop Down Boxes to Worksheet Cells Opening Files in Other Applications Using Macros Interworkbook Recognition of Variables Waiting for User Selections The End: Closing all Nonhidden Workbooks Saving Settings to the Windows Registry Self Writing Macros ROBOTIC AUTOMATION USING VISUAL BASIC Communications Overview The MSComm Control Using the Timer Control Deciphering a Communication Protocol Sending a Command Interpreting a Reply Setting up a Que Using the Timer Control Controlling Multiple Instruments from Differing Manufacturers Hardware Considerations in Robotic Automation Summary LOW LEVEL HARDWARE INTERFACING USING VB Controller Cards and Addressing Installing and Using the PortIO DLL Controlling Relays The MEM-08 Relay Card The MEM-32 Relay Card The MBB-32 Digital I/O Card The MAI-16 Analog Input Card Discrete Time Measurement Sampling Theory and the Nyquist Theorem Aliasing Sampling Limitations of Visual Basic Sampling of Non Periodic Signals Pseudo Continuous Time Measurement Possible Causes of Inconsistent Data Methods for Faster Acquisition Adding Auxiliary Power to the Metrabus System Summary ELECTRONICS FOR AUTOMATION Overview Voltage Division Diode Waveform Shaping The Transistor The Comparator Compensating for Noise Effects with Hysteresis Window Comparator Peak Detection Low Pass Filtering Decoupling Capacitors Creating Custom Instrument Interfaces Liquid Level Sensing Circuits Solenoid Valve Driver Circuits Summary ROBUST DATA ANALYSIS USING ORIGIN LAB'S ORIGIN Labtalk Fundamentals Button Scripts Script Files Passing Parameters in Labtalk Curve Fitting Using the "nlsf" Commands Harnessing Origin Remotely Dynamic Data Exchange (DDE) with VB Dynamic Data Exchange (DDE) with VBA AGILENT (HP) CHEMSTATION MACRO WRITING Basics of Chemstation Macro Writing Chemstation Macro Variables Using Macros as Functions and Subroutines Tools to Aid Macro Writers Registers, Objects and Tables Loading Sequences into an HPLC Loading Sequences which Contain both Plates and Vials Exporting Data after each Sample is Analyzed Exporting Actual XY Data from the Chromatograms Adding Macros to Chemstation Menus Some Generic Macros and Techniques Summary Appendices Appendix A: Downloading and use of sample files from Web site http://www.pharmalabauto.com Appendix B: Setting Up the Excel VBA Macro Examples Appendix C: Kloehn Syringe Protocol Appendix D: Communications Datasheets Appendix E: Electronic Devices Datasheets Appendix F: Setting Up the Origin Examples Appendix G: Setting Agilent Chemstation Macros Appendix H: HPLC Chemstation Macro Hook Information Appendix I: For Further Reading