Tutorial: Using MS Excel VBA in Controls Technology
Using Excel as a Real-time data logging tool
Introduction
MS Excel is a
tool, which has a short learning curve, widely used in industry in; analyzing,
filtering, manipulating and displaying the data, preparing query reports from
databases. However, Excel is much more than an application program. It provides
a sophisticated programming language to automate frequently performed tasks; macros
written in Visual Basic Editor of Excel are often deployed. Excel objects can
also be embedded in HTML pages and MS Outlook messages.
Motivation
What are the
advantages of programming? Why don’t we do everything manually in Excel? The
obvious answer is: saving time. In addition to saving time, usage of Excel
programming, helps us reduce the errors, enforces standards, and as this
tutorial will partially cover; provides an interface to integrate Excel with
other Applications.
In some
industrial environments, events are logged on a continuous basis. The data
which is logged, is often, not usable before being processed. Using the Control
system software to process the data may not always be feasible, due to variety
of reasons. For instance one might want to experiment with different filtering
algorithms or the calibration of a signal may vary and the control software may
not have a user friendly interface to adjust for these variations especially if
this software was written in the old days where GUI (Graphical User Interface)
screens were not available. Therefore often times, the control system software
logs the raw data into a generic CSV or a TXT extension file and MS Excel
handles the rest. After the data is processed, Excel can be programmed to post
the results and the plots to a HTML page or send the results to a recipient via
MS Outlook.
Objective and Materials
This tutorial
is written for novice Excel users, who have basic knowledge of Visual Basic
Programming.
In this
tutorial, deploying VBA applications for data processing will be covered. The
reader will be introduced to reading data from a Comma Separated Value (.csv)
file or a Text file (.txt) and processing this data and generating a report by
simply running an excel macro. This tutorial shouldn’t considered to be a
complete Excel VBA programming reference. The user would have a jump-start in
VBA programming by reading this tutorial and also will get familiar to how VBA
can be used in Industrial Environments. However one should refer to books like:
Excel
Programming Weekend Crash Course by Peter Aitken, so that VBA programming
for Excel is fully understood.
Getting Started
The easiest
way of starting to learn a programming application is the simple, “Hello World”
program. Below is the procedure to program Excel to prompt “Hello World” to
cell A1 of a spreadsheet.
- Start Microsoft MS Excel
- Press Alt+F11 to open VBA editor
of MS Excel. (Menu Path: Tools>Macro>Visual Basic Editor) Figure-1
is the illustration of the VBA screen.
- At the top of the VBA Editor,
there is a window labeled “Project-VBAProject” that lists the currently
open worksheets
- Select procedure from Insert menu.
The Add procedure dialog box displays. Enter the name of your program such
as: “HelloWorld”
- Click OK. The VBA Editor inserts the
first and last lines of the procedure in the code editing window.
- At this point you have the
skeleton of the program
- Type the following line to in
between the inserted code:
Range("A1").FormulaR1C1
= "Hello World"
- Save the workbook to disk
- Press Alt+F8 to open the macro. (Menu
path Tools>Macro>Macros)
- Select the macro saved and run it
Figure-1 Screenshot from the HelloWorld
program
Examples on VBA programming
The following
program asks the user to enter values by Input Boxes and sums these values up:
Public Sub
MyFirstProgram()
Range(“A1”).Select
ActiveCell.Value=InputBox(“Please
Enter a Value”)
Range(“A2”).Select
ActiveCell.Value=InputBox(“Please
Enter a Value”)
Range(“A3”).Select
ActiveCell.Value=InputBox(“Please
Enter a Value”)
Range(“A4”).Select
ActiveCell.Value=InputBox(“Please
Enter a Value”)
Range(“A5”).Select
ActiveCell.Value=InputBox(“Please
Enter A Value”)
Range(“A6”).Select
ActiveCell.Value=InputBox(“Please
Enter A Value”)
Range(“A7”).Select
ActiveCell.Formula=”=sum(A1..A6)”
ActiveWorkbook.SaveAs
Filename:=”MyFirstProgram.xls”
End Sub
The following
program opens the file data.txt which contains the temperature values recorded
over time, finds the maximum temperature, plots the values and sends the chart
to the specified mail recipients.
Sub THF800()
ChDir "D:\My
Documents\class\mem800\take home final"
Workbooks.OpenText Filename:= _
"D:\My
Documents\class\mem800\take home final\data.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False,
Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), Array(2, 1))
Range("A1:B21").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData
Source:=Sheets("data").Range("A1:B21"), PlotBy:= _
xlColumns
ActiveChart.Location
Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text =
"temp(oC)"
.Axes(xlCategory, xlPrimary).HasTitle
= False
.Axes(xlValue, xlPrimary).HasTitle =
False
End With
Sheets("data").Select
Range("A23").Select
ActiveCell.FormulaR1C1 = "max
temp="
Range("B23").Select
ActiveCell.FormulaR1C1 =
"=MAX(R[-21]C:R[-2]C)"
Range("B24").Select
ActiveWorkbook.SaveAs Filename:= _
"D:\My
Documents\class\mem800\take home final\data.xls", FileFormat:=xlNormal _
, Password:="",
WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.SendMail
Recipients:="cyilmaz@comcast.net"
End Sub
This simple
macro can be extended to be a tool which can be used to generate reports, and
send to the multiple recipients. Furthermore, an ftp program can be programmed
to copy the Excel file created using this macro to a web server for review over
the web. Excel files can be viewed in web browsers if Excel is installed in a
PC.
Figure-2 Excel file generated by the
macro THF800
Using Excel as a Real-time data logging tool
The CPU of a
microcomputer can perform only one task at a time. Therefore, timing critical
control applications are not the best candidate for Windows based applications.
There are certain cases, which Excel is used in real-time applications in
controls technology, however these applications should not be used unless the
required sampling frequency is smaller than 1Hz. In the website: http://www.windmill.co.uk/excel.html,
retrieving and sending live data using Excel is explained, for a specific
hardware.
Conclusion
Excel VBA
applications although are not the best interface to implement controls
applications due to sampling rate considerations, they are great tools for
implementing automated post processing, reporting, sharing data via email or
web.
I found the
macros which was on the following page very useful:
http://www.xl-logic.com/pages/vba.html
http://mars.wnec.edu/~jerzy/XL/macros.html
Cagdas YILMAZ is a graduate student in the Department of Mechanical Engineering at Drexel University.