Controlling instruments with Excel and the bundled Visual Basic for Applications

Microsoft Excel provides a rich programming language called Visual Basic for Applications (VBA). You can use VBA, Excel, and the VISA COM libraries to control instruments and retrieve data.

Note: The Visual Basic for Applications help system is not installed as a part of a typical Excel installation.  If you cannot get help on VBA syntax, you may need to install the VBA help files using your Microsoft Excel or Microsoft Office setup disk.

Step 1: Open a new project

Start Excel and view the Control Toolbox (View > Toolbars > Control Toolbox).

Draw a command button on the worksheet by pressing the command icon in the Control Toolbox. By placing a control on the form, you automatically enter Design Mode. The top-left icon in the control toolbar also indicates your current mode. Use that icon to toggle in and out of Design Mode.

Double-click the command button to open the VBA editor. Alternately, click the View Code icon in the control toolbox. It should show the skeleton code for the command button click event.

Step 2: Include the appropriate references

On the menu bar of the VBA editor, click Tools > References to access all available libraries. Find VISA COM 1.0 Type Library, check the box next to it, and click OK. The Keysight IO Libraries Suite 14 uses VISA COM 3.0. Only in VB 6.0 and VBA can you call the VISA COM 1.0 Type Library and automatically get access to the VISA COM 3.0 Type Library.

Step 3: Connect to the instrument

Enter the following code to connect to most IEEE 488.2 compliant instruments:

Private Sub CommandButton1_Click()
    Dim ioMgr As VisaComLib.ResourceManager 
    Dim instrument As VisaComLib.FormattedIO488
    Dim idn As String

    Set ioMgr = New VisaComLib.ResourceManager
    Set instrument = New VisaComLib.FormattedIO488
    ' use instrument specific address for Open() parameter – i.e. GPIB0::22
    Set instrument.IO = ioMgr.Open("GPIB0::22")

    instrument.WriteString "*IDN?"
    idn = instrument.ReadString()
    ActiveSheet.Cells(1, 1) = idn
End Sub 

The WriteString method sends the *IDN? string to the instrument.  The ReadString method reads the instrument reply.  The returned string is entered in the worksheet in cell A1.

Step 4: Test the code

Return to the worksheet and exit design mode by clicking the Design Mode icon in the control toolbox. Click the command button on the worksheet. The instrument identification is entered in cell A1 on the worksheet.

That's all there is to it. The button raises a click event that is used to send an instrument query and return the result to the worksheet.

Using a form

Start Excel and open the Visual Basic editor by pressing Alt+F11, or by using the Tools > Macro > Visual Basic Editor menu.

To insert a new form into the default project, click Insert > User Form on the VBA menu bar.

To show the Properties Window, right click on the form select Properties. To add references, refer to Step 1 of this article. Continue using Excel's VBA like Visual Basic 6.0.