Notes on VB in Excel / PDF

Notes on VB in Excel / PDF

Introduction

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 note should be sufficient for all versions. They are actually written in Office XP.

Getting Started

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.

We will make something like the sheet shown on the right. This makes use of some controls. It has two text boxes into which the user can enter the numbers, a button to click to do the addition, and a label in which the result will appear.

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:..... 

Notes on VB in Excel / PDF