VBA Basics / My First Steps to Programming (Lesson 1)

Objective

Understand what is Visual Basic, how it is embedded in Office applications, what it can do and basic idea of using it.

Prerequisites

The reader/user should have reasonable knowledge of computing in general and Microsoft Excel in particular.

Solution Concept

Conceptual explanation of VBA, how to use it. What are Macros? How to Record and Running/Play back Macros.

Let’s do it

1.1.      VBA or VB

1.1.1. Visual Basic (VB) is a programming Language based upon historical BASIC language. BASIC language was developed with a vision of ‘Ease of Use’.

1.1.2. Visual Basic for Applications (VBA) is an extension of VB that is added in Microsoft’s Office Applications like Word, Excel, Power Point, Outlook etc. Thus placing the user in the driving seat to do whatever she wants to do with the Application.

1.2.      What is a Macro

1.2.1. Macro is a set of commands, written in VBA. At the will of the user or an Event such commands are executed.

1.3.      Getting Ready to use VBA / Macros

1.3.1. First of all let’s set Microsoft Excel Ready for VBA usage.

Important!

This may result in decreasing your computer’s security levels. You can read about Macro security on internet, though I will be talking about it in future posts/lessons.

However, as a basic concept, you need to understand through VBA codes / Macros you can manipulate computer system like folder structure. So a code can be developed to act as a VIRUS and damage your folder and files.

Therefore, security levels have been added to safeguard the user, which for time being (in our learning process) we will ease out to use VBA.

1.3.2. Enabling VBA:

Standard Menu Bar

Steps: (See Video)

File >> Options >> Customize Ribbon (From right side enable “Developer”)

 

Menu Bar with DEVELOPER / VBA option

 

1.3.3. Checking if VBA is properly Installed:

If the Visual Basic and Macros Icons in Developer menu are colored this means VBA is properly install in your computer:

 

However if you see Grey Icons, then you need to change Microsoft’s Excel setup

 

1.3.3.1.    Excel’s Setup (if Icons are greyed out – else skip this step)

In Control Panel >> Program & Features >> Microsoft Office [Change]

Then

Choose “Run All from My Computer” this will install VBA

 

1.3.4. Playing with Macro Security

  

This next step is setting Macro Security Level in Developer Tab:

 

You can choose 2^nd or 4^th Option

 

 

That is either “Disable all macros with notification” or “Enable all macros ….”. Though I would prefer second option which keeps security levels at reasonable status.

Now you are set to Use VBA / Macros.

1.4.      Macro Example (See Video)

1.4.1. A Macro is a set of commands. When we Record a macro, what we do is recorded in VBA language by Excel. Subsequently, when we RUN a macro all recorded steps are executed.

Hence we replicate what we have done once, many a times.

1.4.2. Macro Recording

  

Start by pressing ‘Record Macro’ Icon in Developer Menu:

 

Once RECORDING starts all your steps will be recorded till the time you press ‘Stop Recording’ Icon.

Following window will appear, just press OK. I will discuss options of this window in subsequent post/lesson.

 

Now do some steps like type following in Cells A1 to A3:

 

The next very important step is ‘Stop Recording’ in Developer Menu; else Excel will continue to Record your working.

 

Once recording stop, clear your changes/working done while macro was being recorded; which in current example will mean Deleting entries in Cells A1 to A3.

1.4.3. Macro Running

To Run a Marco, press Macros Icon in Developer Tab.

 

From list of macros choose the recorded macro and press RUN

This will do the job.

1.4.4. Q & A, at this stage I usually get lots of questions like:

Can I run macro at a different sheet?

Can I run it on different cells?

What is the benefit of Macros?

My answer is, what you can imagine; can be done through VBA/Macros. But it will need more learning & practice. Remember anything you do repeatedly like daily, weekly, monthly basis can be automated through Macros though workbook, sheet, cell range can be different.

All you need to ask yourself is: What is the logic I apply when I do it? That’s the logic you need to translate in Excel Functions and VBA codes.

Sample Workbook

Prerequisites/See also (Links not activated yet)