Using Macros & VBA Code

See how VBA coding can transform spreadsheets into applications. It's easier than you might think.

Don't worry you won't need to fully understand the language to write some really useful scripts. We will show you just how easy it is to create & run VBA code in your excel spreadsheets just using some of the basics. You can take it further from there if you wish. Lets just begin by expaining what a couple of the key features are.

What Is The VBA Editor And How To Open & Use It

The VBA editor, or VB Editor, or VBE, is an interface for creating scripts using the programming language "Visual Basic For Applications" or VBA (or often just VB) for short.

The VBA editor looks like this:-

Add Module Code

In the example above the workbook that is open is called Splash.xlsm and the VBA Editor is displaying the code windowfor a module nammed "Module1" and displaying the code within it.

To start coding in Excel you will need to first open the "Developer Tab". You can do this by either using the keyboard shortcut Alt + F11 or going to the Developer Tab on the main menu and selecting the visual basic button.

Add Module Code

If the Developer Tab is not there, then you can add it by going to File > Options > Customize Ribbon and ensuring that "Developer" box has a checkmark in it, then click Ok.

Add Module Code

What is a Module?

You will see in the image of the VBA editor above that in the left hand pane (the project view pane), there is a folder caled modules. This folder is where all the modules for a particular workbook are held. Think of a module as a container for code in your workbook. When a Macro is written or recorded it will be stored in a module.

How to Insert A Module Into Your Spreadsheet

Modules are a container for various pieces or snippets of code called sub-routines or functions. These routines can be asked to run or "called" from the click of a button or from other pieces of code elsewhere in the workbook. There will be more in-depth information about modules elsewhere on this site.

  1. Insert a Standard Module
    1. Right-click anywhere in the Project Explorer Pane
    2. Hover over Insert
    3. Click Module

      Insert Module

  2. A module, in this case "module1" will be created as below and it's as yet empty code window will automatically open for you to add code to.

    Add Module Code

What is a Macro?

Put simply a macro is a set of commands or instructions often called Subprocedures or Functions, they are stored in a Workbook in the form of VBA code, either in Modules, behind each Worksheet, or even Userforms. You can think of it as a small program to perform a predefined sequence of actions. Once created, macros can be re-used anytime. Running a macro executes the commands it contains, and code in macros can also be instructed to run or "called" from other pieces of code.

You may have seen that you can record macro's, but when you do this it is stored in the VBA editor anyway. We always tend to write our macros directly into the VBE editor as this gives you more flexibility and control.

How to Insert A userForm Into Your Spreadsheet

These simple steps explain how to add a UserForm to your spreadsheet, but you will also need to add content such as labels & images, and add whatever coding is required to tell your UserForm how to behave and what to do. There are a million different things that you can get a UserForm to do for you, it all depends on how creative you want to be. We will look at some possible applications for a UserForm in various other parts of this site.

  1. Open the VBA Editor (Alt+F11)
  2. Insert a UserForm
    1. Right-click anywhere in the Project Explorer Pane
    2. Hover over Insert
    3. Click Userform

      Insert UserForm

Your UserForm will now appear and be avaiable for you to create the design you require.

Insert UserForm

How to add code to a UserForm

Where to add the coding required to tell a UserForm how to behave. In this example the UserForm is called UserForm1.

  1. Open the VBA Editor again. (Alt+F11)
  2. Open the code window for the UserForm.
    1. In the Project Explorer Pane Right-click the Userform you want to add code to, in this instance "UserForm1"
    2. Select View Code


The UserForm code window below will open for you to add code to. You can now write or paste the code into this window that will tell the UserForm what to do & how to behave.

Add SplashForm Code

How to add a command button to a UserForm

Clicking a button that is placed anywhere in Excel is an event, and all events can have code specified for them.

So by adding a command button to a Userform we can provide the user with a quickly accessible way to execute some code of our choice, pretty much whatever we want.

  1. Open the VBA Editor (Alt+F11)
  2. Double click the UserForm you want to add a button to so that it opens in design mode.
  3. If it is not already visible, open the toolbox shown below by clicking View/Toolbox in the VBA editor.

  4. Click on the command button (labelled "ab") and holding down the left mouse button draw the button onto the userform roughly the size and position you want. You can change this later quite easily.

  5. In the properties window you can change the name, caption, height, width etc of the button to whatever you need it to be. You can also simple drag it with the mouse to where or what size you want it.
  6. You can now right click the button and the code window will open for you to add code to. You can now paste the code that will tell the button what action to perform when it is clicked.

Contact us we love conversations. let's talk!

Smiling Two Girls