Intermediate Excel and VBA in the Laboratory:Resource Page
Resource page for the "Intermediate Excel and VBA in the Laboratory" short course
To be taught at the SLAS 2012 Annual Conference, February 2012
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.
Mark F. Russo, Ph.D.
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
- Open/Save a Text File
- Get External Data
- Import from an Access Database
- Import from a Web Page
- Record a Reformatting Macro
- Enabling Excel Scripts
- Streamline the Macro
- A Custom VBA Cell Function
- Creating a Log File
- Programmatically Load File Data
- Iterating Over Files in a Folder
- Debug a Program
- Running Tasks Periodically
- Create a Chart Programmatically
- Start and Stop Buttons
- Hidden Parameter Sheets
- Create a Parameters Form
- Create an Array Formula
- Create a Curve Fitting Array Formula
- Customize the Ribbon
- Putting it all Together
Links to supplementary tools used in the class.
- Thermo Scientific's ActiveRobot
- Microsoft Scripting Runtime Library
- InformIT: Understanding and Using Windows API Calls for Excel Programming
- Wikipedia: Curve fitting
- Levenberg-Marquardt algorithm in VBA
- Zip archive format detailed in the Open Packaging Conventions Standard
- Andy Pope's RibbonX Visual Designer
- AC/DC Video in "Excel Vision"
- Excel as a "3D Game Engine"
Related LabAutopedia articles
|A very basic overview of TCP/IP communications||Electronic interfaces|