Welcome to our VBA tutorial!

In first lesson we discuss how to set up environment for developing code in VBA. You will find out how to allow Excel to run macros and how to change VBA editor settings to make your work more comfortable and efficient.

The lesson is divided into the following sections:

• Changing macro security settings
• Opening the VBA editor
• Visual Basic editor
• Adding a new module to a VBA project
• Setting a password on a VBA project
• How to configure your Visual Basic editor?

If you want to create and run macros, first of all you have to change macro security settings.

By default, all macros in MS Office are disabled. This is because macros can make many things, also destructive, including removing data from your computer.

To change these settings in MS Excel 2003 you have to click the following buttons:

• Tools
• Macro
• Security

The window shown below will pop up:

As you can see there are four levels of macro security:

• Very High
  If your macro security level is set to very high only macros installed in trusted location will be allowed to run.
• High
  You can only run macros signed as trusted by this security level.
  Since you will create and run lots of macros during this course, it would be waste of time signing all of them - so this option and previous one are out of question.
• Medium
  When you open a file that contains any macro, the warning message box will appear on your screen.

  

  This is the most suitable option - every time you can decide by yourself if you want to run or block macros in the file you are about to open.
• Low
  When you select this security level, all the macros will be allowed to run without your permission or any warning.
  This option is not recommended, because you can run into a file with macros causing serious damages to your computer without even knowing it.

If you use Excel 2007, there are two ways to show and change macro security options.

The first way is to click the Office logo in the upper-left and then Excel Options button (both of them are bordered in red in the picture below).

The window like in the picture below will appear on your screen. First you should select Trust Center from the list on the left side and then click Trust Center Settings... in the lower right corner (marked in red).

The Trust Center window will pop up. Select Macro Settings from the list on the left side:

The window above can also be displayed by clicking the proper icon in the Developer tab on Excel ribbon. In order to do that first you have to show Developer tab on Excel 2007 ribbon and then go to this tab and click Macro security button (marked in red).

Available security levels are listed in the right side of the Trust Center window (marked in green):

• Disable all macros without notification
  

  If you select this option Excel will not allow any macro to run, even if it is digitally signed. Obviously, this option is out of question when you learn VBA, since you will create and run lots of macros.

• Disable all macros with notification
  

  Description of this option can be a little bit tricky, because it suggests that you will not be able to run any macro. In fact, if you select this option Excel will ask you whether to enable or disable the macros every time it detects any macro in the file being opened.

  It is the most suitable option, because you can decide by yourself whether to run macros or not.

  Depending on the type of file being opened, two kinds of messages can be displayed on the screen. When you open a file in *.xlsm format (i.e. Excel Macro-Enabled Workbook), the file is being opened normally, but in the part between Excel ribbon (toolbar) and formula bar the warning about macro presence appears:
  

  

  In this case all macros are disabled by default. If you want to use them, you have to click Options... button and select Enable this content in the window that pops up on the screen.
  

  

For other types of Excel files (Excel 2003 files, Excel Add-In files etc.), before a file containing macro is opened, the warning like below will pop up on your screen.

Disable all macros except digitally signed macros

In this case Excel will allow to run only macros with digital signature. Since you will create and run lots of macros during this course, it would be waste of time signing all of them. However, later in this tutorial you will see how to create a digital signature for your macros.

Enable all macros

When you set macro security to this level, Excel will run all the macros in every file without your permission or any warning.

This option is not recommended, because you can run into a file with macros causing serious damages to your computer without even knowing it.

You can open the VBA editor in a few way:

• By clicking Alt + F11 shortcut in Excel's main window
• By clicking a proper button on the menu bar (since there is no menu bar in Excel 2007, this way is available only in Excel 2003 and lower)
  In order to open the VBA editor straight from the menu bar, you have to click the following buttons:
  • Tools
  • Macro
  • VBA editor

  

• By clicking a proper icon on Excel's toolbar

  If you want to open the VBA editor this way, first you have to make Visual Basic toolbar visible. You can do it by clicking the following buttons on the menu bar:

  • View
  • Toolbars ...

  and ticking Visual Basic (like in the picture below).

  

Once you do that, the additional toolbar will appear:

The icon bordered in red in the picture below is the one you should use to open the VBA editor.

If you want to show VBA Tab on Excel 2007 ribbon, first you have to open Excel Options window. In order to do that click Office logo in upper-left and then Excel options button (both of them are marked in red in the picture below).

The Options window will appear on the screen. You have to select Popular from the list on its left side and check Show Developer tab in the Ribbon as active.

The Developer Tab is presented below. The button to run VBA editor lies on its left side (bordered in red on the picture).

Before we discuss the details of the VBA editor, you should adjust it to this course. The most important thing is to make all the toolbars used in this course visible.

You can do it by going to

• View
• Toolbars

and ticking toolbars Edit and Standard as active.

After you activate both the toolbars mentioned above, VBA editor should look as in the picture below. All the essential parts of VBA editor are bordered in different colours.

The green-bordered panel is called Properties.
If it is not visible in your editor, you can show it by clicking the icon bordered in green on the picture above or F4 on your keyboard.

This panel allows you to read and change properties of different VBA objects, e.g. user forms, controls, classes and modules.

Properties section is exploited especially when designing graphic user interfaces and we will discuss it in details in the part dedicated to this subject.

The other important part of the VBA editor is Project panel, bordered in red on the picture.
If you cannot see Project panel in your editor, click icon marked in red or press Ctrl + R combination on your keyboard.

In this part of editor all the components of a currently opened VBA project are shown.

Every VBA project can contain the following parts:

• Microsoft Excel Objects
  All the components you can normally find in Excel, like worksheets or charts. It may sometimes happen that there are more sheets listed in VBA editor than in normal Excel view - the reason is that some sheets can be set as hidden so you cannot see them in normal Excel view, while in VBA editor all the sheets are visible regardless of their visibility.

• Forms
  Graphic user interfaces - they will be described in details later in this tutorial.

• Modules
  Modules are the most common objects to store VBA code. In our first lessons all the code will be put into modules, so later in this lesson you can find instruction on how to insert a new module.

• Class Modules
  Objects used to create your own classes. After you finish this course, you will put most of your code into class modules. However, this is too early at this point to even try to explain what classes are and how to use them, so we will back to this subject later in this tutorial.

If you cannot see the objects mentioned above in a Project part of your VBA editor, don't worry. This printscreen was made after adding these components to the project. By default, a newly created file does not contain any user forms, modules or class modules, so only Microsoft Excel Object section is visible.

This is not the right time to describe these components and differences between them in details. For the moment, let us assume that all the code is stored into modules. In the future, when your knowledge of VBA will be greater, we will introduce and discuss the rest of components.

There are a couple more helpful panels in VBA editor, that you can use when creating macros. However, at this stage they would confuse you more than help you. You would probably find them completely unnecessary and forget about them. That is why we introduce them later in this course.

However, there is one more component we have to mention now - gray field you can see in the middle of your VBA editor. That is the place you can see, edit and insert all your VBA code. It is inactive at this moment because we have not inserted any module yet. Adding a new module to a VBA project is a subject of the next chapter.

All VBA modules are stored in normal Excel files, so if you want to create a new module first you have to open an existing or create a new Excel file. When you open a new file, there is only Microsoft Excel Objects section in Project panel, containing as many worksheets as this file (probably 3).

Now you can add a new VBA module by clicking

• Insert
• Module

on VBA editor menu bar.

Once you do that, the new module's edit window will appear in the middle of the screen and the name of this module (usually Module1) will be added to the list of modules in the Project panel.

If you want to rename this module, place the cursor into (Name) field in Properties section (marked in red) and just type the new name.

The module edit window in the middle of the screen can be maximize or minimize by double-clicking on its header (bordered in purple) or by clicking 'Maximize' or 'Minimize' icon on the right side of the header bar.

Sometimes it is reasonable to protect your VBA code from being viewed and especially edited by other users.
To this end, you can set a password protecting VBA code from unauthorized users.

In order to do that, you have to click right mouse button in Project panel on the name of the project you want to protect and select VBAProject properties from a popup menu.

Window like in the picture below will appear:

Go to the Protection tab, check Lock project for viewing as active and enter your password twice for confirmation. When you click OK and save the file, all VBA content in this file will be hidden from users until they enter the correct password.

VBA editor has a few default settings that are quite irritating. You should modify them to make your work as comfortable as possible.

First you have to open VBA editor's Options window by clicking

• Tools
• Options ...

on its menu bar.

In the window that appeared go to Editor tab and set all the controls exactly as it is shown in the picture below.

The most important thing is to uncheck Auto Syntax Check field as inactive.
If this option is active, every time you make a syntax error (e.g. typographical error) VBA editor displays a very annoying error message in the middle of the screen and emits a noisy sound. When Auto Syntax Check option is unchecked, VBA editor still warns you about syntax errors, but it is much more user-friendly now - erroneous code is just displayed in red font.

If you don't like fonts or colors in which your code is displayed, you can also modify them in VBA Editor Options window by going to Editor Format tab.