|
Program Excel the easy way
- Use spBasic! Free Download
Screen Shot of an spBasic 'Hello World' Program for an Excel Worksheet
This program sets cell values and variables, performs a simple loop, and displays a Message Box saying 'Hello World'.
An Introduction to Spreadsheet Basic
What is 'spBasic'?
How do I Get Started with spBasic?
What spreadsheets can use spBasic?
What's the purpose of spBasic?
What is a 'program', and why would I want to
write one?
How do I use spBasic?
Why would I want to use spBasic, can't I just
use cell Formula's?
For the more complicated programs, can't I use
VBA?
Show me something more complex than the 'SimpleProgram'
Does the program have to be visible on
the worksheet?
How can I use spBasic to control Instrumentation?
Can I modify spBasic to control my unique Hardware
Equipment or my unique Systems?
I'm interested, what's the next step?
What is 'spBasic'?
SpBasic is a computer language designed to be used
in spreadsheets such as Microsoft's Excel. It is modeled after the easy
to use 'Basic' language which has been around for 40+ years and is familiar
to many people.
How do I Get Started with spBasic?
First read this page, then download the fully-functional Evaluation Copy of spBasic, then go to Getting Started on the Language page.
What spreadsheets can use spBasic?
At this point, spBasic can be used with the Windows
version of Microsoft's Excel.
What's the purpose of spBasic?
SpBasic makes it easy to write procedural programs
to create and display data in Excel. The data can be formatted and displayed
on worksheets, Charts, and Graphs using Excel's many resources. The primary
purpose of spBasic is to make it easy for the user to create and
display information, and also to collect, analyze, and display
data from various sources. It is also used to control hardware
and instrumentation through PC interfaces such as serial communication
or GPIB.
What is a 'program', and why would I want to write one?
A program is a series of statements which are performed in time
sequence to do a particular task. A spreadsheet is normally used in a
'static' situation, where you change the information in one cell and all
the cells are recalculated using the new information. A program is run
in a 'dynamic' environment, where each program statement is executed in
time sequence. It can be used to generate cell data automatically in a
program 'loop', and can also be used to insert measured data from various
hardware instrumentation. The data can be collected over time, inserted
into the spreadsheet, and analvzed and displayed on charts in 'real time'
as it is being collected.
Also, a program consists of multiple statements where cell values can
be calculated. Many times this is an advantage over cell 'formulas' where
the cell value calculation must fit on one line.
How do I use spBasic?
To use spBasic, you write a spBasic Program using the spBasic Edit Window.
The program is a series of spBasic 'statements'. For example, the program 'SimpleProgram' is shown below. The first line
defines the program name, 'SimpleProgram'. Then cell d1 is set to 1, and
cell d2 is set to 4*d1 + 8, which is 12. 'End' defines the end of the
program.
After running the program, the spreadsheet looks like,
Why would I want to use spBasic, can't I just use cell Formula's?
Yes, you could use cell formula's for the simple case shown above,
but when you get to long programs using program logic, loops, and functions,
you'll find it's much easier using spBasic program statements.
For the more complicated programs, can't I use VBA?
Yes, you can, and many people use VBA. The purpose of spBasic is
to simplify the process of writing data collection and manipulation programs.
It's simpler to use than VBA, but just as powerful, and you can use existing
VBA routines as part of the spBasic language if you wish. To effectively
use VBA, you need to understand the Excel Object model. SpBasic provides
a buffer between these inner workings of Excel and the user, so the user
doesn't have to spend a lot of time learning the VBA environment and the
VBA language details.
Show me something more complex than the 'SimpleProgram'.
This program uses a program loop to calculates gas mileage
for 5 dates with the results plotted on a chart (this example is included in the 'C:\Program Files\spBasic\Examples\' directory in the free download). Data is entered in the
Date, Miles, and Gallons columns (D, E, and F). Miles/Gallon
is computed in column G, and a running Average of miles/gallon
is computed in column H for all the data. Columns D, G and H are used
in the embedded Chart to show Mileage vs. Date.
The spBasic program is shown in the Edit Window. The program uses variables which are
defined in a spreadsheet column (Col A), whose values are shown in Col B.
The program is fairly simple, and uses a For - Next looping structure.
A variable 'i' is used in the loop, and is varied from 2 to numPoints
+ 1. 'numPoints' is calculated from the Excel 'Count' function, which
counts the number of cells in the column which contain number entries,
in this case 5 number entries are counted in col E. The variable 'i'
is incremented in the For statement until it is greater than 6
(is equal to 7), then the program jumps to the statement after the Next
statement, which is the End of the program.
A replacement function defined by double backets '<< >>' is
used to vary the cell addresses from 2 to 6. So the first time through
the loop, G2 = E2/F2, and the last time through the loop, G6 = E6/F6.
The total miles and total gallons are accumulated in lines
7 and 8, and line 9 computes a running average of the milage, which
is displayed in column H. A button labeled 'Run Mileage'
is included on the worksheet. Clicking on the button runs the program
and computes the data. The updated data is plotted automatically on the
Chart. If the input data is changed or added to, simply clicking on the
button updates the output data and the chart.
Does the program have to be visible on the worksheet?
No, you can hide the program and variable columns as shown below.
You can also put the program and variables on a separate worksheet, and
can also password protect the worksheet so columns can't be unhidden.
How can I use spBasic to control Instrumentation?
SpBasic uses Excel AddIns to extend the spBasic language to
include instrument control statements and functions. 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
The spBasic language can be expanded to control any instrumentation such as,
- Power Supplies
- Function Generators
- Oscilloscopes
- Digital Multimeters
- Power Meters
- RF Signal Generators
- Spectrum Analysers
- Switches
- Electronic Loads
Any instrument can be controlled by creating a text file which specifies the instrument commands.. See the Instrument Control
link.
Can I modify spBasic to control my unique Hardware Equipment or my unique
Systems?
Yes, you can write your own VBA Code for your own equipment
as long as it has a computer interface. If your equipment uses the standard
SCPI command language, you may not have to write any code. The ability
to send SCPI commands is included as part of the spBasic language. Also, all
the standard instrument communication protocols are supported. See the Instrument
Control link.
I'm interested, what's the next step?
First, get the free fully-functional evaluation copy under Downloads. Then go to the Language link and
get started with the 'Hello World' program. You'll find that spBasic is
easy to use and will be a worthwhile addition to your computer tools.
|
