|
Getting Started with spBasic
SpBasic Programs - 'Hello World' for an Excel Worksheet
Programs and Variables
SpBasic programs are written and edited in the spBasic Edit Form Window. Two "Hello World" programs are shown above. This example and others are included in the 'C:\Program Files\spBasic\Examples\' directory in the free fully-functional download. The "SimpleHelloWorld" program displays a message box with "Hello World", then it sets the spreadsheet cell d1 to "Hello World!" and displays another message box with the contents of d1.
The second "Hello World" program uses variable names 'str1','str2','str3', and 'str4'.
SpBasic programs can use variable names which are listed in a variable
column. Variable names are just Excel 'Names' which are automatically
inserted by spBasic in the Excel name list. Variable names can be single
or multiple characters (e.g. i, x, aVar5, minVolts, etc), but can't be
1 or 2 characters followed by a number (e.g. x1, c6, aa5, bc678, etc.)
which are reserved for cell addresses. The column to the right of the
variable column is the variable value column which holds the variable
values. You can set the variable value manually by entering a value
into the cell, or compute the value automatically through a spBasic statement.
Each cell in Excel has a Format property that starts out with 'General'.
If you set a cell to a date, the cell format will be changed to a 'Date'.
If you want to change it back to a number, select the cell , right-click
on it, and select 'Format Cells'. Then select the desired format. Select
'General' to clear the format for the cell. You can set the format for
each spBasic variable name by selecting the variable value column and
setting the format as described above. You can set each individual cell
according to the variable display desired. Select 'General' to clear the
format for the cell. You can also set the format for an entire column
by selecting the column, right clicking on it, and choosing 'Format Cells'.
You can create an spBasic program by using the "Create, Edit, or Run spBasic Programs" selection on the spBasic menu as shown below.
This displays the spBasic Edit Form shown here,
We'll explain all the buttons later, but the "On Top" checkbox keeps the form On Top of the worksheet so you can work with it easily. To create a new program, press the "New Program" button (the first button on the left) or select the "File->New Program" menu selection.
The "New Program Form" will be displayed, type "HelloWorld" for the program name and press OK.
If you don't have a Variable Column defined, you get the message,
Each worksheet with an spBasic program must have a least one variable column defined in the worksheet, so answer "Yes" to this if you want the Variable Column to be placed in Col A, and the Variable Values to be Col B. If you don't want to use Cols A and B, you can add the variable column later using the "Tools->Create Variable Column" menu item. If you answer "Yes", another message box will be displayed,
Answer "OK" and the variable column will be created in Col A, with Col B used for variable values.
The "HelloWorld" blank program is also created in the spBasic Edit Form.
'Hello World' Program
Your first program is the classic 'Hello World". After creating the blank program and variable column as explained above, create the program by typing
the program statements shown below into the program edit window.
Also, on the worksheet variable column (Col A), enter the variable names 'str1','str2','str3', and 'str4' , and ' from Me' in the 'str4' variable value column (Col B).
You can use the comment characters "//" before a variable name to tell spBasic to ignore the variable line.
If you included "//str4" in the variable list as shown above, the second 'str4' variable will be ignored. Using the comment characters, you can switch back and forth between variable values in column B by simply commenting out one of the 'str4' variable lines.
A few words on navigating the cells in Excel.
- To select a cell, click on it once.
- When you type when a cell is selected, the cell contents are replaced with the typed values
- When you press 'Enter', the cell selection is moved to the next cell in the column
- To edit text already in a cell, double click on the cell
to get into the cell edit mode.
- To get out of the cell edit mode, click on another
cell
- You must exit the cell edit mode to run a spBasic
program
To insert extra cells into a program, right-click the cell at the insertion
point and select 'Insert'. To copy and paste, select the cells to be copied,
right-click 'Copy', then right-click at the insertion point and select
'Insert Copied Cells'.
Congratulations! You are now ready to run your first spBasic program!
To run the progam, just click on the R button (last button on the right) of the Edit form. You can also use the "Run/Debug -> Run Program" menu selection.
After
a short delay while spBasic loads, the program will run and 2 message
boxes will be displayed, one after another. The variable value column (Col B) will then look
like,
Note the two ways variables can be used. The variable str4
was set by you to " from Me" and was used as an input to the program, and str1, str2,
and str3 was set by the program. You can use variables both ways. Also,
note that the '&' operator concatenates two strings.
You can include a Run Button on the worksheet to
run 'HelloWorld'. Click on a cell on the worksheet where you want the button to be located, then select the "Tools ->Create Buttons -> Create Run Button" menu item.
To move the button, right-click on it, then left-click on the button border to move it.
Now simply click on the button to run the program.
The 'Debug' Display
When writing spBasic programs, you may want to step through the statements
one at a time in order to debug the program execution. To do this, you
use the Debug Controls. To display the debug controls, just click on the D button (second button from the right) of the Edit form. You can also use the "Run/Debug -> Debug Program" menu selection.
The debug display looks like,
Make sure the "Debug Mode" checkbox is checked, then press the "Run" button. The
program starts at the first statement and stops. Click the Step
button two times. The worksheet should now look like this,
Now you can step through the program one statement at a
time by pressing the Step button. Tracing through the program shows
you how the program operates and allow you to debug it if there is a problem.
For more information about program Debug, click here.
Program Statements
and Functions
SpBasic uses a variety of statements and functions. These are,
- SpBasic built-in Statements and Functions
- Excel Macros and Functions
- User defined Statements and Functions
If you are familiar with VBA, you can customize spBasic by adding your
own commands and functions. You can also run Macros that you define through
Excel's Macro generation techniques.
SpBasic built-in Statements and Functions
A list of spBasic built-in language elements can be
found in the spBasic Help Form. To display the Help Form, just click on the H button (third button from the right) of the Edit form. You can also use the "Help -> Show help Form " menu selection.
Here are several
views of the Help Form which show the spBasic language elements,
Once you display the Help Form , you can display Help
on any statement or function by pressing the Help function key F1, or by right-clicking on the statement and selecting
'Show Item Help'.
For more information about the Help Form, click here.
Excel Macros and Functions
You can run an Excel Macro in spBasic by using the spBasic
'Run' statement, e.g., 'Run myMacro' will execute a macro named 'myMacro'. Also, you can use any Excel
Function in any spBasic statement, e.g., 'x = SUM(d1:d100)'.
Below are some Excel Operators and a few of the common Excel
Math Functions that can be used in spBasic.
Excel Operators
'=' Assignment
'+' Addition
'-' Subtraction
'*' Multiplication
'/' Division
'%' Percentage
'^' Exponentiation
'&' Text Concatenate
Excel Logical Operators and Functions
'=' Equal To
'>' Greater Than
'<' Less Than
'>=' Greater Than Or Equal To
'<=' Less Than Or Equal To
'<>' Not Equal To
NOT(x) Logical Not
AND(x, y) Logical And
OR(x, y) Logical Or
Common Math and Trig Excel Functions
ABS(number) - returns the absolute value of a number
COS(angle) - returns the cosine for the angle (angle is in radians)
DEGREES(angle) - converts an angle in radians to Degrees
EXP(number) - Returns e (the natural logarithm) raised to the power of
the number (e^number)
INT(number) - Rounds a number down to the nearest integer
LN(number) - Returns the natural logarithm of a number
LOG(number,base) - Returns the logarithm of a number to the base you specify
LOG10(number) - Returns the base 10 logarithm of a number
MAX(number1,number2,...) - Returns the largest value in a set of values
or cell ranges
MIN(number1,number2,...) - Returns the smallest value in a set of values
or cell ranges
MOD(number,divisor) - Returns the remainder after number is divided by
divisor
PI( ) - Returns the number 3.14159265358979
RADIANS(angle) - Converts the angle in degrees to radians
ROUND(number,num_digits) - Rounds a number to a specified number of digits
SIN(number) - Returns the sine of the given angle
SUM(number1,number2, ...) - Adds all the numbers in a range of numbers
or cells
SUMSQ(number1,number2, ...) - Returns the sum of the squares of the arguments
TAN(number), ATAN(number) - Returns the tangent or arctangent of a number
These are just a few of the many functions in Excel you can use in your
spBasic statements. Other examples are,
msgbox "The maximum value in the cell ranges is "
& MAX(a1:a200, d1:d10)
c3 = AVERAGE(a1:a200)
c4 = SQRT(DEVSQ(D1:D10))
c5 = SLOPE(D1:D10,e1:e10)
c6 = STDEV(d1:d10)
To get a complete list of Excel Functions available to you, select an
empty cell in a worksheet and click '=' on the formula bar. On the far
left of the formula bar click the dropdown box arrow and select 'More
Functions...' on the bottom. To get function descriptions in Excel Help,
open Help -> Table of Contents and select 'Creating Formulas and Auditing Workbooks -> Worksheet
Function Reference' or 'Working with Data -> Function Reference'.
User defined Statements and Functions
You can define custom statements and functions to be
included in the spBasic statement list. Below is an example of some statements
and functions added to spBasic to control a popular electronic instrument,
the Agilent 34401A Digital Multimeter (DMM). You can add statements to
control any instrument or hardware item through VBA, as long as
it has a computer interface. This particular Agilent DMM has over 200
statements that are available to control every aspect of the instrument
operation. You can also add custom functions to do scientific and financial
functions, or any other computational operation through the large
number of 'AddIns' available for Excel.
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
To see a complete list of statements for the Agilent 34401A and a generic DMM class, and for more information about Instrument Control, go to the Instrument Control page.
- Next Step - Go to the Menus page to get a description of the spBasic menus on the Edit Form. Then visit the Debug Display, Help Form, Variable List Form, and Excel Charts pages for more detailed information on how to use spBasic. The Developers Page shows how to use spBasic with VBA (Visual Basic for Applications), and the Custom Code page shows you how to add your own custom statements to the spBasic language. See Example Programs for more examples. These and other examples are included in the free fully-functional download in the 'C:\Program Files\spBasic\Examples\' directory.
|
