e005045.pdf

SOFT WARE

Word and Excel

using Visual BASIC for Applications

By H.-J. Berndt

Nowadays, an RS232 interface is a

standard feature of any better than

average multimeter. However, the

accompanying software can usually

only display a series of measure-

ments on the PC monitor or save the

data as a text ﬁle. If you want to fur-

ther process and evaluate the mea-

surements, this tabular text ﬁle must

be laboriously transformed using a

statistical program or imported into

a word processor.

However, Microsoft delivers the

Visual BASIC for Applications inter-

preter as part of the Office package.

This interpreter makes it possible to

have direct communication between

Word or Excel and the measuring

equipment. We can illustrate this

with an example.

The document template shown in

Figure 1 contains a small sample

program that reads data into Word

from a digital multimeter connected

to the COM1 serial interface. A tem-

perature-dependent resistor sensor

(type KTY10-6) is connected to the

multimeter. The resistance value can

be converted into a temperature

using a formula provided by the

manufacturer of the temperature

sensor. The temperature can then be

displayed in a VBA form. If you click

on the Measure button, the current

temperature will be displayed

(assuming that the RSMINI.DLL ﬁle

Microsoft Office 97 includes

a complete programming

language called Visual BASIC

for Applications (VBA). With

this interpreter, which is

largely compatible with

Visual BASIC, it is possible to

perform measurements

directly in Word or Excel.

The full power of the Office

applications is then available

for analysing the measured

values. Since VBA can

address external routines in

dynamic link libraries, it is

possible to directly import

measurement values.

5/2000

Elektor Electronics

Measuring in

SOFT WARE

R T = R 25 (1+α⋅∆T A +β⋅∆T A 2 ) =

f(T A )

loaded along with the sample pro-

grams used in this article from the

Elektor home page.

This small DLL provides all the

essential functions for communica-

tion via the RS2332 interface under

Windows 95/98. The following DLL

calls are needed to read out the dis-

played value from the multimeter:

where

α = 7.88⋅10 -3 K -1

β = 1.937⋅10 -5 K -2

This formula can be inverted to cal-

culate the temperature, as follows:

OPENCOM open the serial

interface

CLOSECOM close the

interface

SENDBYTE send a character

from the PC

READBYTE receive a char-

acter in the PC

RTS switch the

RTS line

DTR switch the

DTR line

DELAY delay in

milliseconds

Figure 1. A Word document template can serve

as a measurement program.

T = 25 + √(α

2 - 4⋅β + 4⋅β⋅k T ) - α /

2⋅β

where k T = R T /R 25

is present in the Windows directory). The

document template (KTY.DOT) and

RSMINI.DLL can be freely copied and can be

downloaded from the Elektor Electronics

homepage ( www.elektor-electronics.co.uk ). If

you do not have Internet access, you may

order diskette # 000053-11 from our Readers

Services.

For our example, we measure the

resistance of the KTY10-6 using a

Metax M-3650CR digital multimeter

with an RS232 connector. This is a

widely used type of meter, but any

compatible type of DVM could be

used. The resistance value is

imported into Word or Excel and con-

verted directly into a temperature.

The DVM is connected to a free

COM port of the PC using a special

cable. Optocouplers in the meter

provide electrical isolation between

the meter and the PC. They draw

their power from the RS232 lines,

which means that the RTS and DTR

lines must be set appropriately after

the interface is opened.

The multimeter sends the mea-

sured value as a 14-byte ASCII char-

acter string, using transfer parame-

ters of 1200 baud, no parity, 7 data

bits and two stop bits. The last char-

acter transferred is a CR character

(byte value 13). A ‘D’ must be sent

from the PC to the multimeter to ini-

tiate a transfer, but in practice

almost any character will do the job.

Sensors and measurement

equipment

The semiconductor temperature sensor

KTY10-6 is a temperature-sensitive resistor.

Unfortunately, it has slightly parabolic non-

linear characteristic (see Figure 2 ). This char-

acteristic curve can be described using the

formula

A new module

After starting Word 97 or Excel 97,

you can open the VBA editor by

pressing Alt+F11. Here you can

insert a new module using the

Insert/Module menu. The ﬁrst thing

in the DLL is a declaration of the

external functions of the DLL. In this

regard, it is important to use upper-

case letters for the names of all DLL

functions.

KTY 10-6

Declare Function OPENCOM

Lib “rsmini” (ByVal A$)

As Integer

Declare Sub CLOSECOM Lib

“rsmini” ()

Declare Sub SENDBYTE Lib

“rsmini” (ByVal b%)

Declare Sub RTS Lib

“rsmini” (ByVal b%)

Declare Sub DTR Lib

“rsmini” (ByVal onoff%)

Declare Function READBYTE

Lib “rsmini” () As Inte-

ger

Declare Sub DELAY Lib

“rsmini” (ByVal ms%)

R ( Ω )

6000

5000

Dynamic link library

4000

3000

The multimeter that is connected to

the COM interface of the PC is

directly addressed and read from

Excel via a small dynamic link library

(DLL). A few lines of program code

filter the received resistance value,

and a suitable equation is used to

convert the non-linear resistance

function of the sensor into a dis-

played temperature value.

The DLL that has been specially

developed for this project

(RSMINI.DLL) supports basic access

to the multimeter. It can be down-

2000

1000

All parameters must be transferred

by values (ByVal), rather than as ref-

erence parameters (presets). The

DLL should be located in the Win-

dows or System directory. Other pos-

-50

100

150

T (

000053 - 13

Figure 2. Characteristic curve of the KTY10-6

sensor.

Elektor Electronics

5/2000

SOFT WARE

sible locations for the DLL are the

directory for spreadsheets or the

templates directory.

The declarations are followed by the

individual routines for reading the

multimeter:

fourth position (“01.98”). Before the

extraction takes place, the validity of

the string is tested by checking the

value of the decimal point character

at position 6. Finally, the result is

converted to ohms and the result is

assigned to the function name:

Cells(i, 2) =

Temp(StringToOhm(GetString))

DELAY 1000

Next i

CLOSECOM

End If

End Sub

Function ComOpen() As Inte-

ger

Function StringToOhm(ByVal

A$) As Double

StringToOhm = 0

If Mid$(A$, 6, 1) <> “.”

Then Exit Function

b$ = Mid$(A$, 4, 5)

StringToOhm = Val(b$) *

1000

End Function

If you do not have Excel available, you can

also carry out the measurements using Word.

The VBA macros will run in the word pro-

cessing program almost unchanged. The rou-

tine ‘Get10’ must naturally be modiﬁed, since

there are no cells in Word. In order to insert

letters and text into the currently open docu-

ment in Word 97, you can use

ComOpen =

OPENCOM(“COM1:1200,N,7,2”):

RTS 0: DTR 1

End Function

Function GetString() As

String

Dim A$

SENDBYTE 33

A$ = “”

Selection.TypeText Text:=”Hello”.

Calculate the temperature

The above mentioned formula for

calculating the temperature from the

resistance of the sensor is formu-

lated as a VBA function as follows:

All declarations and functions are the same

as those previously presented. The new

‘Get10’ routine for Word inserts the measure-

ment data into the current document:

e = READBYTE

If e > 13 Then A$ = A$

+ Chr$(e)

Loop Until e < 0

GetString = A$

End Function

Function Temp(ByVal r As

Double) As Double

Const Alpha = 0.00788, Beta

= 0.00001937, R25 = 2000

If r <= 0 Then r = R25

kt = r / R25

Temp = 25+(Sqr(Alpha ^ 2 -

4 * Beta + 4 * Beta *

kt)-Alpha) / (2 * Beta)

End Function

Sub Get10()

If ComOpen > 0 Then

For i = 1 To 10

Selection.TypeText

Opening the interface

ComOpen expects a character string

containing the interface parameters.

These open COM1 with 1200 baud, 7

data bits, no parity and 2 stop bits.

If the interface cannot be opened,

ComOpen returns a null value.

Text:=Str$(i)

Selection.TypeText

Text:=vbTab

Selection.TypeText

Text:=Str$(Temp(StringToOhm(Get-

String)))

Selection.TypeParagraph

DELAY 1000

Next i

CLOSECOM

End If

End Sub

Read in a character string

The request for a measurement value

is initiated by the character ‘D’. After

this, characters are received and

packed into the character string A$

until READBYTE provides the value

–1, which indicates that the waiting

time has expired. All 14 bytes should

have been received within this inter-

val.

These two functions can be tested

using a small test routine:

This function receives the resistance

in ohms and delivers the tempera-

ture in °C as a result. This formula

can also be entered into a spread-

sheet and then used to generate a

suitable graphic, as shown in the

illustration at the beginning of this

article.

You can use the control element toolbox to

assign a command button to the document.

A double-click on the button opens the VBA

editor and shows the subroutine that is exe-

cuted when this button is pressed. If you

enter ‘Get10’ here, a measurement will be

made when the button is clicked.

Measurement series in

Excel and Word

A small program loop can be used to

input a series of measurements. The

subroutine ‘Get10’ takes in ten mea-

surement values. It use DELAY 1000

to wait 1000 ms between successive

measurements. The time is entered

in column 1 and the temperature in

column 2.

Sub Test

If ComOpen>0 then MsgBox

(GetString)

End Sub

Private Sub

CommandButton1_Click()

Get10

End Sub

Parse a character string

The resistance of the sensor is ini-

tially contained in a character string,

such as:

“ 01.98 kOhm”

This is now converted into a double-

precision ﬂoating-point variable. Five

characters are extracted from the

character string, starting from the

Naturally, you must exit the design mode

before you can make a measurement.

(000053-1)

Sub Get10()

Const Interval = 1000

If ComOpen > 0 Then

For i = 1 To 10

Cells(i, 1) = i - 1

5/2000

Elektor Electronics

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: