Course Catalog

Getting Started with Excel & VBA in the Laboratory

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.

Course Details

Key Topics

  • Importing data into Excel
  • Reading and writing files with VBA
  • Writing Excel Macros
  • Creating Excel Add-Ins
  • Programmatically manipulating worksheet data
  • Programmatically formatting worksheets
  • An introduction to objects and the Excel object model

Information

Excel is widely used in scientific laboratories to automate tedious data manipulation and presentation tasks. This course will review many of the tools built-in to Excel for handling problems commonly encountered in the laboratory. It will also introduce the Visual Basic for Applications scripting language as a way to customize Excel and expand upon its functionality.

This is a hands-on laptop required course. All participants are required to provide their own computer with Excel 2007 or Excel 2010 installed with the full Help documentation. Power will be available.

Who Should Attend

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. A practical knowledge of the use of Excel is required and familiarity with a procedural programming language such as C, Java or BASIC is advantageous.

Benefits

  • You will learn techniques for loading data into Excel from text files of different formats
  • You will learn how to write and edit Macros to process data
  • You will learn the fundamentals of the VBA language
  • You will be introduced to the interaction between VBA and common Excel objects such as Workbooks, Worksheets, and Cells
  • You will learn ways to display data in custom graphic formats

Agenda

  • 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 Locations

Date

TBA

Check-in opens at 7:30 a.m. on the first day of the course.

Course runs from 8:30 a.m. to 5:00 p.m. each day.

Register Online Register Via Mail

Venue


Pricing
  Member Non-Member
Advanced $1,495 (ends July 15, 2014) $1,695 (ends July 15, 2014)
Standard $1,895 $2,095

The course fee includes a course binder and a continental breakfast each day.

Five for Four! Register five people for one course, one person for five courses, or any combination in between and your fifth registration is free. Note: This discount is only available if you register by fax, mail or phone and mention this discount and may not be combined with any other offer.

About the Instructors

  • William Neil

    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

    Martin Echols received his Bachelor’s degree in Biology from the University of Denver in Colorado.