Create Custom Code

Creating Custom Code

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. The steps below will show you how to create an Add-In file, then modify it to include your custom spBasic statements.

Example spBasic Add-Ins and Statement Text Files for Instrument Control are included in the following directories in the free fully-functional download.
'C:\Program Files\spBasic\AddIns\Agilent\34401ADmm\AddIn\' - for the Agilent 34401A DMM and most other DMM's
'C:\Program Files\spBasic\AddIns\Agilent\E3600PwrSupplies\AddIn\' - for the Agilent E36xx Series power supplies and others
'C:\Program Files\spBasic\AddIns\Instrument\AddIn\' - for general Instrument Control

How to make an spBasic Add-In (.xla) file

  The basic steps for creating an Add-In (.xla) file for spBasic are (don't do these steps yet, we'll go through the details below),

• Create a workbook with the first 3 letters = 'Spb', e.g., 'SpbExampleAddIn.xls'
• Save the workbook as an add-in file
• Create a text file called a Statement Text File
• Run the spBasic ‘Create Code’ tool to create the spBasic code from this text file
• Test and debug the add-in
• Protect the Add-In to prevent it from being viewed or edited after you create the add-in file. If you do not protect projects in the workbook, others can view and edit the Visual Basic modules in the add-in file. This is optional, you may not need or want the add-in protected.

  Let's go through the detail steps.

1. Open Excel with a new workbook, or create a new workbook if Excel is already open. Save the new workbook to a directory where you are going to be creating and testing the addin. You usually will put spBasic add-ins under the 'Program Files\spBasic\AddIns' directory, but this is not required. Name the workbook to the add-in name with the first 3 letters = 'Spb', for example, “SpbExampleAddIn.xls”.
   
2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
   
           
   
   For Excel 2007, you click on the Developer tab, then Visual Basic. Click here for instructions on how to set up the Developer tab.
   
   
   



3. In the Project Explorer window in the Visual Basic Editor, click the bold entry:
   

   <Project name> (<File name>) e.g., VBAProject (SpbExampleAddIn.xls)
   
   

   where Project name is the name of the Visual Basic project, and File name is the name of the workbook that you want to convert. If the Project Explorer window is not displayed, display it using the VBA window 'View' menu.
   
                 

4. Change the Project name in the Properties Window '(name)' to the same name as your file (without the .xls extension) e.g., 'SpbExampleAddIn'. If the Properties Window is not displayed, display it using the VBA 'View' menu.
   
            

   Now we'll save the Workbook as an Add-In File,

1. Close the VBA window and return to Microsoft Excel
   
2. On the File menu, click Properties
   
3. In the Properties dialog box, click the Summary tab
   
4. In the Title box, type a name for your add-in (e.g. ‘spBasic Add-In for <add-in description>’. The name that you use is the name that appears in Excel's Add-Ins dialog box (click Add-Ins on the Tools menu to see examples).
   
5. In the Comments box, type any text that describes your add-in (e.g., ‘This is an spBasic add-in for <add-in description, purpose>’). This description is displayed in the Add-Ins dialog box if you select your add-in from the list of available add-ins (click Add-Ins on the Tools menu to see examples).
   
   
   
6. Click OK to close the Properties dialog box
   
7. On the File menu, click 'Save As'
   
8. In the 'Save As' dialog box, click 'Microsoft Excel Add-In (*.xla)' at the end of the 'Save as type' list. You can change the file name if you want.
   
   
   
   For Excel 2007, click the Office Button, then 'Save As -> Excel 97-2003 Workbook'
   
   
   
   Then select 'Excel 97-2003 Add-In'
   
   
   
   
9. When you selected the (*.xla) file type, the directory to save the files was changed to the Microsoft 'AddIn' directory, In XP this is normally at "C:\Documents and Settings\<owner name>\Application Data\Microsoft\AddIns". You can change this to another directory if you want. The .xla files in the Microsoft AddIns directory are automatically added to the list of add-ins on Excel's 'Tools -> Add-Ins' menu. If you save the add-in to another directory (such as the 'Program Files\spBasic\AddIns' directory), use the Browse button on the 'Tools -> Add-Ins' dialog form to add the add-in file to the list.
   
10. Once you select the directory to save your add-in file, click Save and Excel creates the add-in in that directory.
    
11. Now save the Workbook and exit Excel. You have to do this because, in some cases, if you saved your add-in in Microsoft 'AddIn' directory, the Add-In will not show up in the Add-In list until Excel exits and is restarted.
    
12. Now re-open the workbook. On the Excel menu, click Tools -> Add-Ins. For Excel 2007, click here for instructions on how to display the Add-Ins.
    
    

  If you saved your add-in file in the Microsoft AddIn directory, your add-in should appear on the Add-Ins list. Check the checkbox next to the add-in and press OK. If you didn't save your add-in file in the Microsoft AddIn directory, press the 'Browse' button to get to the directory where you saved your file, then double-click on it to add it to your add-in list. Make sure the checkbox next to your add-in is checked, then press OK.

Your Add-In should now be loaded each time you start Excel.


The 'Statement Text File'

Your new spBasic Add-In has been created, but as yet it doesn’t contain any code. To create code for your Add-In, you generate code from a text file called a Statement Text File.

A Statement Text File is used to create and modify Excel Add-Ins for the spBasic language. It has different elements which are used for different Add-In functions. The Statement Text File used for the 'ExampleAddIn.xla' Add-In is shown below.

            Statement Text File - 'SpbExampleAddIn.txt'

The elements of the above file, in order of appearance in the file, are:.

"Help Group Name = "
Example:   Help Group Name = Statement Text File Example
The Help Group Name element defines the name of the Help Group which appears in the 'Group' drop down list in the spBasic Help Form. The first 'Help Group Name =' line in a Statement Text File indicates the start of code generation, all 'Sub' and 'Function' statements before this line will be ignored. All statement Subs and Functions defined after the 'Help Group Name' will appear in this Help Group. If a new 'Help Group Name = ' line appears in the Statement Text File, a new Help Group will be created and all Subs and Functions appearing after that line will be included in the new Help Group.
NOTE: If a Help Group is deleted from a Statement Text File, and new Add-In code is created, you must exit and restart Excel before the Help Group is removed from the Help Form.

"Category Name = "
Example:   Category Name = First Category
The 'Category Name' element defines the Help category for a list of statements. The Category Name will appear before a list of statements in the Help window. If a new Category Name is defined, all statements appearing after that Category Name will appear under that Category until a new 'Category Name = ' line is defined.

"Sub Name = "
Example:   Sub Name = Cat1_ExampleSub
The 'Sub Name' element creates an spBasic Statement with arguments in the form of,
Sub Name = <statement name> <argument 1>, <argument 2>, ... <argument n>
You can also use the 'optional' keyword to make the argument optional. In the statement,
Sub Name = a401_Initialize ResourceName, optional TerminationCharString
The argument 'TerminationCharString' is optional.

"Special Statement = Custom Code"
The 'Special Statement = Custom Code' element identifies a Sub or Function that uses ‘Custom Code’ that the user writes in VBA. This statement appears after a Sub Name or Function Name statement. When this is used, you must write your own code for the Statement or Function. The Sub or Function lines are generated, but you must fill in your own VBA code to be executed.

"Help String ="
This creates Help text for the Sub or Function. See the 'Help String' statements in the above file example.
*** NOTE: You must have a 'End Help' line at the end of each 'Help String' definition. ***

"Function Name = "
Example:  Function Name = Cat1_ExampleFunction(Argument1, optional Argument2)
The 'Function Name' element creates an Excel function with arguments in the form of,
Function Name = <function name>(<argument 1>, <argument 2>, ... <argument n>)
You can also use the 'optional' keyword to make the argument optional. In the statement,
Function Name = Cat1_ExampleFunction(Argument1, optional Argument2)
The argument 'Argument2' is optional. Note that parenthesis must be included around the arguments.

The next step is to create a Statement Text File for your application.

      - Create a new text file called 'Spb<your project name>.txt' e.g, 'SpbExampleAddIn.txt'
      - Copy the text in the outlined area above to the text file
      - Change the Help Group Name, Category Names, Sub and Function Names, and Help Strings to those needed in your custom code project. Add other Sub and Function Names, Categories, etc as needed. Follow the rules for Sub Name, Function Name, etc. given above when you fill in the text.

After the Statement Text File is created for your application, the next step is to create the code structure in the Add-In '.xla' file. This is done through the spBasic Edit Form window.

Creating the Code

We'll use the 'SpbExampleAddIn' project to illustrate using a Statement Text File. Follow the same steps for your own custom application.

Before we proceed to generating the custom code, rename the workbook 'SpbExampleAddIn.xls' to 'SpbExampleAddInTest.xls' ( 'Spb<your project name>.xls' to 'Spb<your project name>Test.xls'). We will use this 'Test' workbook to test the 'SpbExampleAddIn.xla'  add-in.  Now open 'SpbExampleAddInTest.xls', display the spBasic Edit Form ('spBasic -> Create, Edit, or Run spBasic Programs'), and select the spBasic ‘Create Custom Code’ menu item under the Edit Form’s ‘Tools’ menu. The 'Create Code' form will be displayed.

Select the Statement Text File you created by using the ‘Browse’ button for the Statement Text Files on the ‘Create Code’ Form. Then select the ‘.xla’ file you created using the ‘Browse’ button for thr ‘Add-In File Name’. Now click the ‘Write Code’ button, and a "Code Written" message should appear in the lower right hand corner of the form.

                     .                       

SpBasic created three VBA modules in the Add-In .xla file, an ‘Spb_CustomCode’ module, an ‘Spb_HelpModule’, and an ‘Spb_Statements’ module. These modules contain the code for all the Statements, Functions, and Help text that was in the Statement Text File. The ‘Spb_HelpModule’ and ‘Spb_Statements’ modules are overwritten each time you run the ‘Create Custom Code’ menu item, so it’s easy to make changes in the Statement Text File (like changing Help text wording). You simply change the Statement Text File, run the ‘Create Code’ menu item, and the changes are reflected in the VBA modules and in the spBasic Statement and Help list.

Now display the spBasic Help form by clicking on the Help Form Button (H) on the Edit Form.

The Help Form will be displayed, note there's a new Help Group called 'Statement Text File Example'. Select this group and the Statements for the two Categories defined in the Statement Text File will be shown with the Statements and Functions in the categories.

Right-Click on "Cat2_ExampleSub Argument1, opt Argument2" and choose "Show Item Help", the Statement Help Form will then be displayed showing the Help text for the statement that you included in the Statement Text File.

You now have the statement included in the spBasic language with statement Help, but the actual code for the statements is still not written. To include the code, you have to modify the VBA subroutines and functions in the ‘Spb_CustomCode’ module. When you created the module, the code in the CustomCode module for "Cat1_ExampleSub(Argument1)" and "Function Cat1_ExampleFunction(Argument1)"  was generated as,

Once created, the ‘Spb_CustomCode’ module is not overwritten, since you will be adding custom code to the module. New custom code statements from the text file are added to the VBA module if the code statement doesn’t already exist. After you change your code, make sure you save your project before you exit VBA, or your changes will be lost. Now enter some test code between 'start code here' and 'end code' and press the VBA 'Save' button.

   ' ----- start code here -----
   msgbox "Hello World " & Argument1      
   ' ----- end code -----

Now, on the spBasic Edit Form, create a new program called AddInTest and add the line shown below.

Move the Edit Form over to the side so the center of the computer screen is clear to display the message box. You have to move the window away from the center of the screen so the message box doesn't appear behind the form. It's best create a Run button to run a program with a VBA message box or user form. You can also uncheck the Edit Form 'Keep on Top' checkbox. See FAQ's - Message Box statement in VBA Macro for an explanation.

Now press the Edit Form 'Run' button ('R'). This will run the spBasic program, call the 'Cat1_ExampleSub' subroutine in VBA, and display the message box. If the message box does appear behind the Edit Form by mistake, just press 'Enter' and the message box will close.

You also can trace through the VBA code by putting a VBA break point in the code, but be sure to press the VBA 'Continue' button before the last 'Exit Sub' or Exit Function' statement. See FAQ's - Tracing through a VBA Program for an explanation.

Protecting the AddIn

  To Protect the code in the Add-In, double-click on a module in the add-in project in theVBA project explorer.

     - On the VBA Tools menu, click <project name> Properties, e.g., ExampleAddIn Properties.

     - Click the Protection tab and Select the 'Lock project for viewing' check box. Type a password in the Password box. Type the same password in the Confirm password box. Don't forget the password. If you do you won't be able to edit the file. Now click OK to close the form.

     - Save the VBA Add-In project by pressing the Save button, or do a File->Save.. If you don't save the project the add-in will not be protected.

Custom Code for Instrument Control

Statement Text Files can be used to create Custom Code Add-Ins for Instrument Control. Example spBasic Add-Ins and Statement Text Files are included in the following directories in the free fully-functional download,

'C:\Program Files\spBasic\AddIns\Agilent\34401ADmm\AddIn\' - for the Agilent 34401A DMM and most other DMM's
'C:\Program Files\spBasic\AddIns\Agilent\E3600PwrSupplies\AddIn\' - for the Agilent E36xx Series power supplies and others
'C:\Program Files\spBasic\AddIns\Instrument\AddIn\' - for general Instrument Control

You can use Statement Text Files to control any instrument with a computer interface using spBasic statements. See the Instrument Control page for more details.

 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