Short Course Listings
 
Short Course

Course Information
Course Title: Getting Started with Excel and VBA in the Laboratory (Laptop Required)
Categories: 1 - Data Analysis
2 - Data Management
3 - Electronic Laboratory
4 - Laboratory Information and Management
5 - Management/Professional Development
Instructor(s): William Neil / Martin Echols Course Number: 2
Affiliation: Bristol Myers Squibb
Course Date: 03/05/2016 - Saturday Course Length: 2 Day Course
Start Time: 08:30 AM End Time: 05:00 PM
Course Date 2: 03/06/2016 - Sunday    
Start Time: 08:30 AM End Time: 05:00 PM
Fee: $1050 ($1450 after 2/12/16) Textbook Fee:

Course Description
This is a computer course. All participants are required to provide their own computer with Excel 2007, 2010 or 2013 (Office 365) installed with the full Help documentation. Power will be available. This course will give you practical experience in creating Excel Macros to automate tasks that are common to the Laboratory. Most intermediate and/or advanced Excel training courses include macro writing but often focus on solving business problems and often fail to translate to the laboratory. The modern chemistry laboratory employs HPLC’s, GC’s, liquid handlers, etc., that read or write work lists. Our examples start with learning the import wizard and recording macros, but we dissect the macro and transform it into a practical application where several work lists can be imported simultaneously, processed and graphically displayed. Many attendees are able to modify the course examples for their specific application and achieve significant productivity enhancement by reducing or eliminating data entry. Many have gone on to present their work in poster publications.

Target Audience
This short course is for bench scientists, laboratory managers, and students interested in automating basic laboratory tasks using Microsoft Excel and its scripting language, Visual Basic for Applications (VBA). The course material is targeted at beginners having no prior experience with VBA. Familiarity with a introductory programming language is helpful.

Course Outline
Configuring the Visual Basic for Applications (VBA) Macro Environment
Trust Center configuration
VBA Develop Tab configuration
What you can do with VBA
Same functionality as UI
Modify the interface
Customize the functionality of Excel
Reading Files into Excel
Purpose
Source
Import Methods
Formats
Terminology
The Text Import Wizard
Comma Separated Values (CSV)
Tab delimited
Space(s) delimited
The Macro Recorder
The Visual Basic Editor
Recording a macro
VBA Syntax
Functions
Subs
Reviewing the Recorded Macro
Observing recorded statements
Problems
Variables
Data Types
Declarations
Assigning Data
Pitfalls
Introduction to Objects
Properties
Methods
Events
Fixing the Recorded Macro
Fixing the destination worksheet
Fixing the source worksheet
The Excel Object Model
Objects
The Immediate window
Getting and putting into cells
String Manipulation
Example functions
Example custom functions
Files
Programmatically opening, reading, writing and closing files
Creating log files
Arrays and Looping
Split function
Join function
For Loop and Do While Statements
Parsing
Formatting A Worksheet Cell
Cell Comments
Cell Colors
Run Time Errors
On Error GoTo
Resume and Resume Next
Creating a Custom Macro User Interface
Quick Access Toolbar
Creating a Custom Menu
Importing Multiple Files Simultaneously
GetOpenFileName object
Wrapping in a loop
Creating an Excel Add-In
Protecting the Add-In
Saving the Add-In
Testing the Add-In

Course Instructor's Biography
William Neil received a Bachelor’s degree in Chemistry and Biology from the College of NJ. For several years he worked with the Toxicology Division of Mobil Oil Corporation where he developed his interest in computer programming and laboratory automation. He has been employed in the pharmaceutical industry since 1992, where he has written several applications in C#, Iron Python, Visual Basic and VBA to automate the Drug Discovery process, particularly in the area of instrument integration. Martin Echols received his Bachelor’s degree in Biology from the University of Denver in Colorado. Martin has worked with a variety of research and pharma companies over his career, migrating into laboratory automation in the 1980s. Martin has worked extensively in the area of laboratory instrument integration and robotics.