Excel spreadsheet measures analog voltages

Yury Magda, Cherkassy, Ukraine -- EDN Europe, 01 Feb 2010

You can use the parallel port, which many computers still have, with some VBA (Visual Basic for Applications) code and Microsoft (www.microsoft.com) Excel 2007 to measure, record, and analyze analog signals. The hardware comprises a 12-bit Microchip (www.microchip.com) SAR (successive-approximationregister) ADC (Figure 1). The binary code that represents this signal passes to the parallel port through an SPI (serial-peripheral-interface)-compatible port, which includes three signal lines.

The analog input signal on Pin 2 of the MCP3201 ranges from 0 to 5V. The ADC’s reference voltage is 5V, which provides an LSB (least-significant bit) of 5/212, or 1.2207 mV. You can substitute the MCP3201 ADC with the Linear Technology (www. linear.com) LTC1286 chip, which is pincompatible with MCP3201, but this replacement requires some changes in the source code. The circuit includes a 1- to 10-F bypass capacitor that you should place as close as possible to the MCP3201’s power pin. You can improve perform ance by placing a lowpass active filter between the signal source and the converter’s input (Pin 2).

The design uses the PC’s default parallel interface, LPT1, which has three hardware registers: data, status, and control. Most PCs have only one parallel port with the base address 0x378 assigned to the data register. The status register has the address 0x379, and the unused control register has the 0x37A address. These settings are the defaults for most motherboards. This design uses addresses 0x378 and 0x379. The parallel port sometimes uses hardware addresses in the range of 0x278 to 0x27A. The hardware addresses may also vary when you apply a PCI (Peripheral Component Interconnect) expansion board with a built-in parallel interface. You should check the hardware addresses of the selected parallel interface and set appropriate values in the source code.

The program uses the inpout32.dll library, which is free and accessible for downloading at www.logix4u.net. The inpout32.dll includes two useful functions, Inp32 and Out32. The Inp32 function reads a data byte from the hardware register (port), and Out32 writes the byte into the hardware port. Before using the inpout32. dll, you should copy the inpout32.dll into the \system32 directory so that the VBA application can find it. You can download Listing 1, the VBA code, from the online version of this Design Idea at www.edn-europe.com/article.asp?articleid=3768.

To write a VBA control program, you should create a new Excel workbook and open the Visual Basic editor where you enter the source code. After doing so, you must insert a new user form (Figure 2). You need to drop a button component from the toolbox onto the user form. When the application is running, clicking the button causes the value of the analog voltage to appear in the current cell of Column A of Spreadsheet 1.

You should declare functions Out32 and Inp32 from the inpout32.dll library with the Declare directive so that the application can use them. The Out32 (port, portVal) holds the hardware address of the port to write data to. The value of the byte goes in the portVal parameter. The Inp32 function takes the port parameter where the address of the port is held. When successful, this function returns the byte read from this port.

The program implements the timing diagram of the conversion and outputs data when the CommandButton1_ Click event handler is called. The statements Call Out32(dataPort, &H80) Call Out32(dataPort, &HC0) Call Out32(dataPort, &H40) start the conversion. The for() loop passes data bits beginning from the MSB (most-signifi cant bit) to the parallel port. The 12 bits of the result are saved in the binData variable, and the total variable holds the final result. Figure 3 shows the main window of the running application. After you click the “get input voltage” button, the measurement result appears in the next cell in Column A of Spreadsheet 1.

Listing 1:

Private Declare Sub Out32 Lib «inpout32» _
(ByVal port As Integer, ByVal portVal As Integer)
Private Declare Function Inp32 Lib «inpout32» _
(ByVal port As Integer) As Integer
Public row As Integer

Private Sub CommandButton1_Click()
Dim s1 As String
Dim rows As Integer

Dim binData As Integer
Dim total As Double
Dim mask As Integer
Dim dataPort As Integer
Dim statusPort As Integer

Dim dataStatusPort As Integer
Dim i1 As Integer

binData = 0
mask = &H4000
dataPort = &H378
statusPort = &H379

Call Out32(dataPort, &H80) ‘CS goes high
Call Out32(dataPort, &HC0) ‘CS stands high, CLK goes high
Call Out32(dataPort, &H40) ‘CS goes low allowing the conversion

For i1 = 0 To 14
Call Out32(dataPort, &H0) ‘CLK goes low
dataStatusPort = Inp32(statusPort) And &H8
If dataStatusPort <> 0 Then
binData = binData Or mask
End If
Call Out32(dataPort, &H40) ‘CLK goes high
mask = mask / 2
Next i1
Call Out32(dataPort, &HC0) ‘CS goes high
binData = binData And &HFFF
total = 5.00 / 4096 * binData

s1 = Format(total, «#.###») + « V»
Sheet1.Cells(row, 1).Value = s1
row = row + 1
End Sub

Private Sub UserForm_Initialize()
Sheet1.Columns.Range(«A:A»).ColumnWidth = 30
row = 1
End Sub

For more information
Avago: www.avago.com	Cree: www.cree.com	Energy Star: www.energystar.gov
Lumex: www.lumex.com	Nichia: www.nichia.com	Osram: www.osram.com
Philips Lumileds: www.philipslumileds. com