Excel Coding

03 Writing and Running Code 

Small set up

Although we could go ahead and write some VBA code straight away, there is one little setup item we should do beforehand. This only need to be done once unless you want to change it again and is found under the “Tools” menu and “Options…”.


Ensure the “Require Variable Declaration” option is ticked. Although this not necessarily essential for VBA, it is still strongly recommended you do this and the reason for this will be explained later.


So we want to write some VBA code in Excel, but where exactly do we put it? Well we have 2 options. The first is to “attach” the code to something in Excel (such as a worksheet) and the second is to create a blank “canvass” which we write code into. We will be using a blank canvass to start with and in VBA these are called “modules”.

Modules (the blank canvass)

In order to write code we need something to write it in and this item is called a “module”. At the top of the Visual Basic editor screen under the “Insert” menu option is the “Module” option. Select this and the “project view” will change to show the newly added module.

 

We probably want to change the default name from “Module1” to something a bit more useful so if we click on the module highlight it, the Properties window will update with the module’s properties (of which there is only one option – “(Name)”)

    

You can directly change the name in the propertiess box by click on the name and typing in a new one. This will also change the name you see in the Project Explorer.

  


There are some limitations as to how you name your module (eg it cannot start with a number) but generally you should call it something sensible that represents what the code will do.

Other recommendations include:

  • Start the name with “mod” or something similar as this can help you identify modules later when you use them in code. Other items can have a similar naming convention (for example worksheets can start with “ws”, charts with “cht”, etc)
  • Spaces are not normally used in naming items in code, with the underscore being used instead if you really want some sort of separator (eg use “some_name” instead of “some name”)

In the main window you should see a large white space. The top of the area should have “Option Explicit” in it if you ticked the “Require Variable Declaration” option from the setup section above. It will be blank otherwise.

If it is not visible, then you can type it in yourself (although it is not necessary for this example).

Writing Code

When writing VBA code, all code is contained in a “sub-routine”. A sub-routine is simply code which has been given a name which allows you to run that code using the name you have given it. Running the code using its name is called “calling the subroutine”.

We define the name for the subroutine using the “Sub” command and we also need to indicate that the sub routine code has finished, for which we use “End Sub”. So, if we wanted to write some code in our module, we would write something like:

Sub <name>

<Code>

End Sub

Where <name> is the name you want to call the sub-routine and <My Code> is all the code you will write to do something in Excel.

So as a real example we could do this:


Notice that Excel has coloured in some words and has placed a line above the “Sub” row, making it easier to read and identify parts of the code you have written.

Running the code

If we want to run the code then there are several ways we can do this.

In the immediate box we can just type in the name of the sub and press return.

   

Or can click in the code in the sub to put the text cursor in the code and press the run button at the top of the screen.

   

Or we can call the sub routine from another sub routine. In the following example we have written another sub routine called “anotherSubRoutine” which uses the command “Call” to run the first sub routine we wrote. We can run “anotherSubRoutine” the same way as the above 2 methods.


      

NB in the code above we use the command “Call” to run the other sub routine, however in Excel VBA you don’t actually need to do that and it can just be run using the name of the sub routine. However it can be good practice to use the command “Call” to make reading the code easier.

The final way to call code is what gives Excel the flexibility and functionality which has made it the one of the most popular programs in the world. Excel allows code to be “attached” to “events”. An “event” any simply something that happens. This could be the user clicking on the mouse, pressing a button, typing something into a cell, etc.

In this example we will attached the code to a button which will run it when pressed.

Go back to the main Excel screen and make sure the “Developer” menu tab is available. If not, please read “Coding in Excel - 02 Setup” to enable it.

On this tab press the “insert” option to display the controls available. You will notice there are two sections – “Form” and “Active X”. We will be using the form controls and it is recommended that in general that these ones are used instead of the Active X ones (Active X controls are incompatible in the Mac version of Excel and can have problems with older versions of Excel/Windows).


So to make this work we will create a button and “link” the sub routine we wrote to the “Click” event of the button. When we do this, a sub routine will be generated automatically which runs the sub routine we wrote.

To do this, from the Form Controls, select the “Button” control and draw it onto the worksheet. When drawn, a box will appear. From the lower section, select the name of the sub routine you wrote (which will populate the top section) and press “OK” to confirm

   

Now when you press the button, the code you wrote will run.

If you need to change which sub routine runs, simply right click on the button and choose “assign macro” to select another sub routine.


The contact form requires that you configure reCAPTCHA in the site configuration.

The contact form requires that you select an email template.