How To / Tips and Troubleshooting

SpBasic makes it simple to run user defined macros, VBA subroutines, and VBA Functions from within an spBasic program. To run macros and VBA subroutines, use the spBasic 'Run' statement. The form of the 'Run' statement is,

Run <workbook.xls>!<macro or subroutine> arg1, arg2, arg3, etc.

This statements runs a Excel Macro or VBA subroutine. The arguments 'arg1', 'arg2', etc. are used if the VBA subroutine has arguments. The arguments can be any VBA variant type. Make sure you have a space between the subroutine name and any arguments, and include the '.xls' extension in the workbook name. The correct Run statement is, "Run WorkbookName.xls!subName arg1,arg2, etc.", "Run WorkbookName.xls!subName(arg1,arg2, etc.)" will not work. The parenthesis are only used for User Defined functions, e.g., "x = functionName(arguments)". Note that any User Defined Functions (UDF's) that are defined in VBA (VBA Function) are automatically detected by Excel and will be evaluated when they are used in a Spb statement on the right side of an equation.

spBasic Statement Examples:

The simple VBA routine 'testSub' takes the sum of the last two arguments and displays the results in a msgbox with the prefix "Sum = ". The VBA subroutine for testSub is,

Important: A note about using spBasic and VBA message boxes and Forms. SpBasic Windows will normally stay on top of the Excel window. Excel treats VBA as part of it's window, so you have to be careful if you're using a VBA messagebox or an input form at the same time that an spBasic window is displayed. If you use a message box from VBA, and a spBasic window is showing (Edit Form. Help, etc.) , the msgbox might appear behind the window. Press ‘Enter’ or 'Esc' to terminate the msgbox and return control to the spBasic form. To avoid this situation, keep the spBasic windows off to the side when you're debugging a VBA routine containing a msgbox or input form. You can also uncheck the Edit Form "On Top" button, or minimize the Edit form and create a Run button to run the program ( run the program from the ‘Run’ button).. This is also true for any Excel User Forms that you may generate. Note for some versions of Excel, the spBasic windows may not stay on top the first time you use spBasic after turning on the computer, but they will stay on top if you close Excel and open it again.

So, when you try these examples, keep the Edit Form off to one side so the VBA message box shows when it's display in the center of the screen. You can also uncheck the Edit Form "On Top" button, or create a Run Button to run the examples.

The spBasic statement,

displays the message box,

A VBA function 'testFunction' is,

The spBasic statement,

displays the same message box shown above.

SpBasic allows you to add your own spBasic statements to the spBasic language. The statements will be recognized by spBasic, and any Help descriptions for the statements you add will be displayed in the spBasic Help window. These statements are contained in an Add-In (.xla) file. See How to make an spBasic Add-In (.xla) file on the Custom Code page.

You can run spBasic Programs, Statements, and Functions by using the VBA statements shown below,

   - To run an spBasic Program from VBA , run 'SpBasicAddIn.xla!runProgram' as,

     Application.Run "SpBasicAddIn.xla!runProgram", "<spBasic program name>"

     For example, to run an spBasic program called 'testProgam', use the VBA statement,

     You can stop any spBasic program that is running by using the VBA statement,

   - To execute an spBasic Statement Line , run 'SpBasicAddIn.xla!executeSpbStatementLine' as,

     errMsg = Application.Run(Application.Run "SpBasicAddIn.xla!executeSpbStatementLine", "<program statement line >")

    This function returns an error message string, which is blank if there are no errors. However, before you run this function, you must run an spBasic program at least once. The statement below will run an spBasic program ('MyProgram' can be any valid spBasic program) from VBA.

      You can also run the progam from the Edit Form. After an spBasic program has been run, you can call 'SpBasicAddIn.xla!executeSpbStatementLine' by itself as many times as you want.
     After running an spBasic program, you can execute an spBasic statement. For example, to run a spBasic statement line which sets cell d5 to the sum of e4 to e8, use the VBA statement,

     ----------------------------------------------------------------------------------------------
     NOTE: To execute an spBasic statement line for an Instrument, use "executeSpbInstrStatementLine"
     ----------------------------------------------------------------------------------------------

    Running Statements or Functions for Instrument Control

   - There are two methods to execute an spBasic Statement Line for Instruments
    
     Method 1
     
     Run 'SpBasicAddIn.xla!executeSpbInstrStatementLine' as,

          Application.Run "SpBasicAddIn.xla!executeSpbInstrStatementLine", "<instrument statement line >"

     For example, to run a spBasic statement line which configures an instrument AC volts using a command string, use the VBA statement,

       Note that this method using the arguments as part of the command string "a401_ConfigACVolts 10, .0001". This function returns an error message which is blank if there are no errors.
       Remember, you must run an spBasic program at least once before you can use the 'SpBasicAddIn.xla!executeSpbStatementLine'  or 'SpBasicAddIn.xla!executeSpbInstrStatementLine' functions (see 'execute an spBasic Statement Line' above) . After an spBasic program has been run, you can call these functions as many times as you want.   

      Method 2

      Run 'SpBasicAddIn.xla!executeSpbInstrStatementOrFunct' as,

     Application.Run "SpBasicAddIn.xla!executeSpbInstrStatementOrFunct", "<statement>", arg1, arg2, arg3, etc.

     For example, to run the statement 'a401_ConfigACVolts' which has two arguments, use the VBA statement,

         Note that this method uses the arguments separate from the command string. The first argument is the name of the spBasic statement ("a401_ConfigACVolts"), and the 2nd and 3rd arguments are separate (10 and .0001). You can use VBA variables for these directly instead of converting them to strings, which you would have to do in Method 1.

    - To use an spBasic function for Instrument Control, use 'SpBasicAddIn.xla!executeSpbInstrStatementOrFunct' as a function,

     returnValue = Application.Run ( "SpBasicAddIn.xla!executeSpbInstrStatementOrFunct", "<statement>", arg1, arg2, arg3, etc.)

      For example, to run a function called 'a401_Read' which has one argument and returns a measured value, use the VBA statement,

See also Running spBasic Programs, Statements, and Functions from VBA Forms on the Agilent 34401A DMM Test Programs page

The code for the Form "TestForm" is,

You can display this form by using a Macro to show the form, and you can create a Button on the worksheet to run the macro and display the form when it is pressed.

You can display the form through a Macro by creating a subroutine in a VBA module, such as "Module1". the subroutine would be,

To add a button or other simple Form to a worksheet
 - Select menu item View -> Toolbars -> Forms

                        

 - Click on the Button Form

                        

 - On the worksheet, click and hold down the left mouse button and draw the button on the worksheet
 - When the mouse button is released, a Macro list will pop up. Click on the macro "showTestForm" and press OK.

    

 - Click on the button text to change the button label.

                      

 - Click on the worksheet when finished.

You can change the properties of the button at any time by right-clicking on it. Pressing the button will display the "TestForm". Enter an existing spBasic program name in the Form's textbox window, click 'Run Program', and the spBasic program will run. You can stop the spBasic program by clicking the 'Stop Program' button on the form. For examples of using forms with spBasic statements and functions, see Running spBasic Programs, Statements, and Functions from VBA Forms on the Agilent 34401A Digital Multimeter example program page.

Another example below shows how to change cell values and run a program from a VBA Form.

Here a maximum value for a random number, 'Max Curve Value', is set in the 'Run Curve Form'. When the 'Run Curve' button is pressed, an spBasic program is run which generates a random number sequence and the average, which is plotted on the Data Chart. The maximum value of the sequence is 20, the value set in the form text box.

The VBA Form subroutine code which is run when you press the 'Run Curve' button is,

The first line of the code sets the spBasic variable 'maxValue' on the 'developerPage' worksheetsheet to the value in the 'Run Curve Form' textbox. The spBasic 'graphTest' program is then run. The spBasic program is shown below,

The program first clears 30 rows in the data columns D, E, and F on the worksheet named 'data'. Then it loops 30 times, and the data columns are filled (using the replace function <<i>>) with the index value in column D, a random number in column E using the Excel's RAND() function ( this returns a value between 0 and 1), and the average of the random number in column F. The program then waits .1 second between values so you can see the graph change more slowly. The 'maxValue' variable used in computing the random value was set to 20 by the 'Run Curve Form'. For examples of using forms with spBasic statements and functions, see Running spBasic Programs, Statements, and Functions from VBA Forms on the Agilent 34401A Digital Multimeter example program page.

Handling duplicate user defined statements
You can't have duplicate spBasic custom statement names in open workbooks. These are custom statements defined by the user. If workbook1 has a Sub 'TestSub' and workbook2 has a Sub 'TestSub' , only 1 will run. Use the spBasic 'Run' statement to run duplicate Sub names in different workbooks, the statements 'Run workbook1.xls!TestSub' and 'Run workbook2.xls!TestSub' will work and run the routines.

Module code is not visible to the rest of the VBA modules
In VBA, if module code is not visible to the rest of the VBA modules (you can't call a module subroutine), CHECK TO SEE IF THE CODE IS CORRECT. References may be missing, etc. One way to check the code is to compile the module using the VBA menu item 'Debug -> Compile VBA Project'.

How to call Routines between VBA Projects
To run a routine in another VBA project, use
    o Calling a Function
             beginTime = Application.Run("MyAddIn.xla!Function1", arg1,arg2, etc.)
    o Calling a Subroutime
             Application.Run "MyAddIn.xla!Subroutine1", arg1,arg2, etc.
             NOTE: This will not pass arguments by Reference

• To run a routine in another VBA project, use
• Function -> beginTime = Application.Run("MyAddIn.xla!Function1", arg1,arg2, etc.)
• Subroutime -> Application.Run "MyAddIn.xla!sub1", arg1,arg2, etc.
• NOTE: don't need project name if there is only one function or sub with the called name
• Function -> beginTime = Application.Run("Function1", arg1,arg2, etc.)
• Subroutime -> Application.Run "sub1", arg1,arg2, etc.
• NOTE: This will not pass arguments by Reference

• To run a routine in another Workbook and pass arguments by reference,
• Define the routine in the Workbook 'Microsoft Excel Objects->ThisWorkbook' object
• In the calling program, put the following,

The arguments can be passed by reference using this code. Also, you can call a VBA module from VB and pass arguments by reference.

 Live Excel Help!

Free initial talk time, no charge until you're convinced that the expert can help you!

Expert Excel Help!
Powered by LivePerson