How to Write Visual Basic Programs in Excel : Simple Explanation

 

 

Description / Intro

 

These notes take the reader through an introduction to writing Visual Basic programs in Microsoft Excel. They are intended to get you started up to the point where you can make use of the on-screen help.

 

You need a basic familiarity with using Windows in general, and a little experience of using Excel.

 

The different versions of Windows and Office lead to differences in details, but these notes should be sufficient for all versions. They are actually written in Office XP.

 

Let’s Start

 

Suppose we want to be able to input 2 numbers, add them up, and display the answer. Of course we can do this with just one formula in Excel without VB - but the purpose of this is to show a very simple example of VB code.

 

 

 

 

To set up something like this, you need the Control Toolbox visible. In a new Excel sheet, from the main menu go View.Toolbars and check the Control Toolbox option (not Forms). This offers buttons to produce these controls. Click on the TextBox button (see below), then drag in the sheet to draw out a text box where you want it.  Make another text box, and button, and a label (note the difference between a text box and a label).

 

 

When you create these controls, VB chooses names for them itself, like ListBox1, ListBox2 and so on. When programming this is very confusing, since you have to remember which control is which. It is much easier if they have more meaningful names, which means altering the properties of each control. To do this -

• Make sure you are in design view ( the set square)
• Right click on the control
• Click on Properties

For example, changing the name of the button to AddButton: 

 

Experiment with changing the other properties. In particular change the caption to say Add.

 In a similar way change the names of the text boxes to Num1 and Num2, and change the label to Result.

Write your First VBA Program

In design mode, double-click on the AddButton, and the VB Integrated Development Environment ( IDE ) will open like this -

 

VB programs use the idea of event-handling. Small pieces of code - sub-routines - are written to be carried out when something happens - when an event occurs. Each object, such as a button, can respond to various events. For a button the most common event to program is the click - as shown here. It is also possible to program a double-click event for example, but this is unusual, not least because users will not expect that double-clicking a button will do something.

You can alter the appearance of the VB editor - go Tools .. Options.. Editor Format - to change the colors and so on.

VB writes the framework for the sub-routine for you -

 Private Sub AddButton_Click()

End Sub

You just have to write the middle bit. Type this in very carefully -

 Private Sub AddButton_Click()

' This button adds the numbers from

' the text boxes and displays the result
' in the label

Dim a, b, c As Double

a = CDbl(Num1.Text)

b = CDbl(Num2.Text)

c = a + b

result.Caption = CStr(c)

End Sub

Note that is CDbl with a letter L not a digit 1

To test it, click back on Excel, go out of design mode, type 2 numbers into the text boxes and click add -

If you have made a mistake, you will get and error message, and an invitation to Debug the code. Check it and try and fix it. Before you try it again, stop the debugger, or you code will be still paused:

 

 

How the code works

The first 3 lines -

 

' This button adds the numbers from

' the text boxes and displays the result

' in the label

 

are comments, ignored by the computer. Comments start with a single quote, ( ' ).

 The code uses 3 variables to hold values in the memory. These are declared -

 

Dim a, b, c As Double

 

 The 3 variables are called a b and c. Double means what data type they are - double indicates that they are numbers which can have decimal parts, and are not restricted to whole numbers.

 

Next

 

a = CDbl(Num1.Text)

b = CDbl(Num2.Text)

 

gets values out of the text boxes. Num1 is the name of the text box object, and Text is one of its properties, namely the text in it. But the data type of this is a string of characters, while a is a number. CDbl is a 'built-in function' which changes a string into a Double value.

 

c = a + b

 is the heart of the code, just adding a and b and assigning the sum to the variable called c.

 

 result.Caption = CStr(c)

 

is placing this value in the label called 'result'. This is done by assigning to the caption property ( text boxes have text, labels have captions ). But c is a number, and the caption is a string, so we use CStr to convert the number to a string.

 

Home work J

 Try adding, subtract, multiply and divide buttons and program them accordingly. Subtract is -, multiply is * and divide is /.

 

Data types

 

Double is a number up to 10³⁰⁸. As well as Double, the following data types are useful -

 

Single	Similar to Double but uses less memory, has less range and less accuracy.  Range up to 10 38
Integer	Whole number, up to 32 767
Long	Whole number, up to around 2 thousand million
String	String of characters like "Hello"
Boolean	True or False

 

Integers and longs are faster to process than singles or doubles.

 

How to use Debug.Print

When programming it is useful to be able to check on the values of variables. This can be done by, for example,
Debug.Print x
which will display the value of x in the 'Immediate Window' in the IDE. If you can't see this, go View..Immediate Window.
 

Spreadsheet Cells reference

Instead of using text boxes, values can be obtained from cells on a spreadsheet, and values can also be written into cells. For example some code might say

Dim x as Integer

x = Cells( 2, 3).value

This gives x the value in cell C2 - that is, row 2 and column 3. 

 

Conditional Statements

We often want the computer to take decisions on the basis of inputted data and take alternative actions on that basis. This is done using an 'if' or a conditional statement. For example suppose we have a sheet like:
 

So in B1 will go pay before tax, B2 will have a tax allowance and B4 is the tax due. This is 25% of the gross pay, unless the gross is less than the allowance, in which case the tax due is zero.

 

 

The following code does it:

 

 

 

 

Dim gross, allowance, tax As Single

gross = Cells(1, 2).Value

allowance = Cells(2, 2).Value

If gross < allowance Then

    tax = 0

Else

tax = gross * 0.1

End If

Cells(4, 2.value) = tax

It is possible to use < for less than, > for greater than, and = for equals. Use <> for not equal to. Conditions can be put together with AND OR and NOT, such as

If x < 4 AND y<>7 Then ….

Using Check Boxes

A check box control would usually be used with an 'if'. For example, a sheet might be like this -

There might be a 20% reduction for the child rate for a plane fare. If the checkbox was called Child, the code to work this out would be likely to include -

 If Child.Value = True Then

                ..

Else

                ..

End if

 

Home work J

 Program the above example as outlined.

 

Loops

We often want the computer to repeat a section of code many times. This is called iteration or looping. VB has several kinds of loops to do this. If you know how many times to repeat the code, use a For loop. As an example -

 

Dim i as Integer

For i = 1 to 20

                Debug.Print i

Next

 

outputs the numbers from 1 to 20.

 

The code between the For and the Next would be repeated 20 times. Th efirst time, i would be 1. The next time it would be 2. The last time it is 20.

 

A For loop goe sup in steps of 1, unless you say something like

For x = 0 to 9 Step 3

…

Next

which loops 4 times, with x being 0, 3, 6 and 9.

An example of the use of this -

 1 - 1/3 + 1/5 - 1/7 + 1/9 ……

converges to π/4

 This does it -

Dim i, Factor As Long

Dim Sum As Double 

Factor = 1

Sum = 1

For i = 3 To 1000000 Step 2

    Factor = (-1) * Factor

    Term = Factor / i

    Sum = Sum + Term

Next

Debug.Print 4 * Sum

This produces

3.14159065358969

which is pretty close.

Thanks for Reading. Happy Learning Friends :)
Do like this post if you like it. :)