SLAS

Getting Started With Excel and VBA in the Laboratory:Resource Page

From LabAutopedia

Resource page for the "Getting Started With Excel and VBA in the Laboratory" short course

To be taught at the SLAS LabAutomation2011 conference, January 2011


Contents

Synopsis

This course is designed to help laboratory scientists take Microsoft Excel to the next level by learning how to customize and automate its behavior. The primary tool that is used for this customization is the Visual Basic for Applications (VBA) progamming language, embedded within Excel. Attendees of this short course will learn the VBA language from the beginning, with a particular emphasis on solving problems commonly encountered in the laboratory.

Numerous topics will be covered over the course's two days. The VBA programming language and Visual Basic Editor will be introduced progressively using numerous exercises. Excel's internal objects will be explored in detail with relevant examples. Various methods for customizing the Excel user interface will also be demonstrated and tested. Finally, you will find out how to pack up and distribute your custom scripts for others to use. Laboratory related problems involve custom data loading, processing and visualization techniques.

Computers and software will be provided. Although, you are welcome to bring your laptop with Excel 2007 installed.

Instructors

William Neil
Princeton, NJ 		Martin Echols
Yardley, PA



Detailed Course Outline

  1. Introduction
    1. What is Visual Basic for Applications (VBA)?
    2. What can you do with VBA?
  2. Reading Files Into Excel
    1. Text Versus Binary Files
    2. ASCII Encoding
    3. Text File Format Terminology
    4. Example 1: Opening Text Files
    5. Example 2: Opening Delimited Files
    6. Example 3: Opening Fixed-Width Files
  3. The Macro Recorder
    1. The Visual Basic Editor
    2. Example 4: Record a Macro
    3. Macros, Subroutines and Functions
  4. VBA Variables
    1. Data Types
    2. Variable Declarations
    3. Assignments
  5. Objects
    1. Understanding Object Oriented Programming
    2. Object Properties
    3. Object Methods
    4. Example 5: Using Objects to Improve a Recorded Macro
    5. Example 6: Further Improving a Recorded Macro
    6. Example 7: Testing the New Macro
  6. The Excel Object Hierarchy
    1. Learning the Excel Object Model
  7. The Range Object
    1. The Immediate Window
    2. Getting Values from Cells
    3. Putting Values into Cells
    4. Example 7: Directly Copying Data Between Cells
  8. String Manipulation
  9. Data Conversion Functions
    1. Example 8: Convert Well to Row / Column
  10. Looping
    1. Example 9: Use Well Label to Copy Data
  11. Application Object
    1. Getting a File Name
    2. Example 10: GetOpenFileName
  12. Files
    1. Opening Files
    2. Closing Files
    3. Reading Files
    4. Writing Files
    5. Creating a Log File
    6. Example 11: Read Lines from a File
  13. Arrays
    1. Splitting String into Arrays
    2. Example 12: Read Data From the File
    3. Example 13: Parse a File Header
    4. Array Bounds
    5. Rounding Numbers
    6. Example 14: Multiple Measurements
  14. Cell Formatting
    1. Adding Cell Comments with VBA
    2. Colors in Excel
    3. Setting Cell Colors with VBA
    4. Example 15: Cell Formatting with VBA
  15. Handling Runtime Errors
    1. Example 16: Handling Errors
  16. Variable Scope, Visibility and Lifetime
    1. Visibility Examples
  17. Customising Excel's User Interface
    1. Example 17: A Custom Menu
    2. Example 18: Processing Multiple Files at Once
  18. Custom Add-Ins
    1. Setting Project Properties
    2. Protecting the Add-In
    3. Saving the Add-In
    4. Testing the Add-In

Sample Course Video Clip

Related LabAutopedia articles

Programming Automation List_of_programming_and_computer_science_terms
[+] Data storage and organization
[+] Statistics and data analysis
A very basic overview of TCP/IP communications Electronic interfaces
Retrieved from "http://www.labautopedia.com/mw/index.php/Getting_Started_With_Excel_and_VBA_in_the_Laboratory:Resource_Page"