SLAS

Intermediate Excel and VBA in the Laboratory:Resource Page

From LabAutopedia

Resource page for the "Intermediate Excel and VBA in the Laboratory" short course

To be taught at the SLAS 2012 Annual Conference, February 2012

Contents

Synopsis

Microsoft Excel is the "Swiss Army Knife" of experimental data. It is often the first tool a scientist turns to when there is a need for a quick way to manage, analyze and display data collected in a scientific laboratory. As data handling requirements get more demanding, standard Excel features run out of steam. More powerful capabilities are possible by customizing Excel with purpose-built scripts.

Visual Basic for Applications (VBA) is Microsoft's programming language and integrated development environment that is embedded within many desktop and benchtop applications, including Excel. VBA is the built-in way to customize and automate Excel's capabilities. For "quick hit" laboratory data handling needs, Excel and VBA are natural choices.

Instructors

Mark F. Russo, Ph.D.
Haddonfield, NJ
russomf at hotmail dot com

Steve Carafello
Partner, Integrated Systems & Services Group
scarafello at gmail dot com



Course Topics

This course builds upon Getting Started with Excel and VBA in the Laboratory by exploring more advanced features of Excel 2007 and the VBA programming language. Topics covered include the following.

  • Learn how to import external data from files, databases and web pages.
  • Record and edit custom macros in the Visual Basic Environment
  • Write and run custom scripts on a periodic basis, including scripts that look for and parse data files.
  • Create custom VBA cell formulas.
  • Learn how to use ActiveX Components and Controls in Excel.
  • Build custom forms and control panels.
  • Invoke Win32 API functions
  • Curve fitting with VBA
  • Customizing the Excel 2007 Ribbon

Programming Examples 

  1. Open/Save a Text File
  2. Get External Data
  3. Import from an Access Database
  4. Import from a Web Page
  5. Record a Reformatting Macro
  6. Enabling Excel Scripts
  7. Streamline the Macro
  8. A Custom VBA Cell Function
  9. Creating a Log File
  10. Programmatically Load File Data
  11. Iterating Over Files in a Folder
  12. Debug a Program
  13. Running Tasks Periodically
  14. Create a Chart Programmatically
  15. Start and Stop Buttons
  16. Hidden Parameter Sheets
  17. Create a Parameters Form
  18. Create an Array Formula
  19. Create a Curve Fitting Array Formula
  20. Customize the Ribbon
  21. Putting it all Together

Supplementary Material

Links to supplementary tools used in the class.

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.org/mw/index.php/Intermediate_Excel_and_VBA_in_the_Laboratory:Resource_Page"