How to make and run Visual Basic programs from MS Excel or Word programs
Comments(1)
Within Word or Excel there is a Visual Basic
Editor, together with tools, for writing Visual Basic programs and running
them. This brief note is aimed at showing how to get started and a little of
what is possible. It does not aspire to teach the ins and outs of how to
program in Visual Basic – there are plenty of books available on that.
The Visual Basic Editor is also present in MS
Access and Powerpoint.
Part A – getting started.
1. Start Excel.
2. On the task bar click Tools\Macro\Visual
Basic Editor. You will get the Visual Basic Editor containing a blank
worksheet.
3. On the task bar click on Insert\User Form.
You will get a form, called UserForm1, and a toolbox superimposed on the
worksheet. Drag the toolbox to one side.
4. Click on UserForm1 and drag the lower,
right hand corner to make the form larger.
5. In the toolbox click on the ‘ab’ icon (the
textbox icon) and then click on UserForm1 – a text box is dropped onto the
form, and its name is TextBox1. Move the textbox to the top left corner and
then drag the lower right corner to enlarge it.
6. In the toolbox click on the command button
icon (middle of the 2nd row) and then click on a free part of
UserForm1 – a command button is placed there, called CommandButton1.
7. Double click on the command button; you
will get a white form, or code window, with the following brace:
Private Sub CommandButton1_click()
End Sub
Between these two lines we write our program
code. As an example:
(a)
Private
Sub CommandButton1_click()
Line = Line + “Output is “
For j = 1 to 10
Line = Line + Str(j)
Next j
TextBox1.Text = Line
End
Sub
(b)
Click on the taskbar Run\Run. The program runs and you get UserForm1
with the textbox and command button.
(c) Click the command button: the program executes and displays in the
textbox the expected
Output
is 1 2 3 4 5 6 7 8 9 10
(d) click the ‘x’ on the top right corner of
UserForm1 to stop the program.
Part B – running the ‘Make Values’ program.
1. Double click on the CommandButton1 to
bring up the code window. Delete the program we have just written in, but leave
the braces there. Paste in, or type in, the program ‘Make Values’ from the
Crypto Drop Box.
2. On the Task Bar click View\Project
Explorer and in the box that appears click on UserForm1 to get our form back.
3. On the Task Bar click on View\Properties
to get a small box called ‘Properties_TextBox1’. In this box
-
click ‘MultiLine’ and choose true
-
click Font and a small button will appear on the font line. Click this button
to get a dialogue box, in which choose Courier New and size 10.
4. Now click Run\Run to run the program and
click CommandButton1.
5. The program will create indices for each
of the three words STUBTOES, FOURDOOR and DUOAORBB. Then from the perm 5089721463 the values of the three words will be
calculated. Finally the program displays all this data as follows:
word(0) = STUBTOES
word(1) = FOURDOOR
word(2) = DUOAORBB
derived alpha=STUBOEFRDA
index(0)= 0 1 2 3 1 4 5 0
index(1)= 6 4 2 7 8 4 4 7
index(2)= 8 2 4 9 4 7 3 3
if perm = 5 0 8 9 7 2 1 4 6 3
value(0) = 50890725
value(1) = 17846774
value(2) = 68737499
Part C – running the ‘Make Perms’ program.
Proceed as described above.
The program will display all the perms for 5
digits. Perms for a different number of digits (say 
can be displayed by
changing the 6th line in the program to
length = 8
To solve an addition cryptarithm this program
would be combined with the ‘Make Values’ program and the display section
adjusted to show the solution rather than all the perms.
Part D – conclusion.
I am indebted to photon for pointing out to
me that Word and Excel contain this hidden nugget of Visual basic.
This little introduction is to help you get
started. Computer solving programs, such as BION and I describe in our columns,
are just one type to work on. Equally rewarding are interactive programs that
take the labour out of pencil and paper work, and leave you to solve with your
own initiative rather than the computer’s muscle. photon is an arch-exponent of
this art.
Help is available from the Visual Basic Editor
taskbar, from the MS Visual Basic web site and from numerous manuals.
