This page may be out of date. Please visit our home page
Case study - Visual Basic for Applications in Excel (Excel VBA macros)
This major oil company wanted to check that test equipment was running true. To do this we proposed using Visual Basic for Applications in Excel (Excel VBA) to create automatic charts of data points:
|
|
Example chart produced by the Excel macros in the model
You can click on any of the links below to see the detail of how we used Visual Basic for Applications in Excel to solve the client's problem.
The engine testing equipment used by this major oil company exports data for a variable number of measurements and stages, according to the test being run. The new system needed to use Microsoft Excel macros to import this data in whatever format, and then use Excel VBA to chart it graphically.
The challenge with this problem was to produce an Excel VBA system that was easy to use, so that all members of the engine testing group would be able to use it. In addition, we wanted to produce a system using Microsoft Excel macros that was sufficiently flexible to allow any format of test to be run.
Our solution was to use Microsoft Excel spreadsheets and Visual Basic for Applications in Excel to produce custom software. This custom software included a free-format spreadsheet allowing the client to input any test in any format. This approach meant that the client wasn't restricted by the field-and-record structure of a relational database.
The final system was built using Excel 2000, with Visual Basic for Applications for Excel driving and controlling the system. Our solution was to build one workbook containing the Excel VBA code and forms, providing access to a number of data files.
The difficulty with this system was the amount of validation involved, to check that the user couldn't inadvertently crash the system. Class modules in Excel VBA were used to capture events for workbook opening and closing, and hence prevent unauthorised actions. All illegal keystrokes were also intercepted using Microsoft Excel macros.
This form shows the customised menu allowing users to import, view, sort or chart test data:
Sample screen showing menus dynamically created by Microsoft Excel macros
The Microsoft Excel macros system has now been implemented, and is being used successfully throughout the engine testing group. Not only has it met the core need, but the group have now found that it is sufficiently flexible to model types of engine tests not originally envisaged.
MICROSOFT TRAINING
FIND YOUR COURSE
OTHER SERVICES
YOUR TRAINING
- Microsoft Access training / Access VBA training
- Microsoft Excel training / Excel VBA macros training
- Microsoft .NET training / ASP.NET training
- Microsoft Project training / MS Project training
- Visual Basic training
- Visual C# training
- Microsoft Visio training
- Microsoft Publisher training
- Microsoft PowerPoint training / MS PowerPoint training
- Excel VBA training / macros training
- Microsoft Office training / MS Office training
- TNA - Training Needs Analysis
- Tailored onsite training
- Training courseware / manuals
- Microsoft Office 2010 training
- Industry experience
- Access and SQL Server database development
- ASP.NET web site development
- CRM/Customer Relationship Management software
- List of training courses by venue
- Hire our computer training rooms
- Test your Microsoft IQ
Recommended sites: TechTutorials | Manually edited page