Saturday, 22 April 2017

Excel VBA Introduction

What is VBA?

VBA is stand for “Visual Basic for Application” and it is a programming language. This is very easy and powerful language and it comes pre-installed with Excel. We use this VBA skill to automate Excel job or task to reduce the time.


VBA is also packaged with other Microsoft application like Word, Outlook and Access

What is a Macro?

Macros are small or long programme design in Excel VBA. In further tutorial we will discuss about macro in a detailed way.

Whenever we create a macro in Excel VBA, it will be started like:-

Sub Topic_we_want_to_create_a_macro
-
-  (in this place we will write some vba coding
-     and we will talk in details about it later posts)
-
End Sub

Here SUB (Short for Subroutine) is collection of lines of VBA coding and that coding is the main part of VBA macro.

But when we open Excel in our computer, the VBA option is not displaying there.


In above given image there is no Developer tab, Developer tab is the option from we can make the VBA option visible.

Follow the below given instruction and get a VBA Developer Tab is visible to the ribbon


In Excel 2007, open Excel and top of the Excel there Office Button (No. 1) click on it and a new window will open and at the bottom click on "Excel Option" (No.2), the following window will appear


First click on No.3 "Popular Tab" and make sure No.4 should be ticked and if it is not-ticked the VBA Developer tab will be disappear from Excel ribbon, so make sure that "Show Developer Tab in the Ribbon" is ticked and simply click on "OK" button. 

And once you done it, the Excel image will look like below image:- 


The "Developer Tab" is now available in the Excel Ribbon and once you click on "Developer Tab"
the another VBA option are also visible there,  like "Visual Basic", "Macro", "Record Macro", "Macro Security" etc are also available in the Excel Ribbon,

What is Macro Security?

Click on the "Macro Security" button as circled in the above image 

First we have to check for "Macro Security Setting" in the following given image:-



Make sure your Macro security setting should be exactly as mentioned in the above image.

What is a macro security setting? we will discuss this in a different blog because this is a detailed study.

How to save a Excel macro file?

Now one more thing need to discuss that whenever we will save a macro contains file, we need to   notice that click on "Save" button and from Option "Save as type" as circled, choose  "Excel Macro-Enabled Workbook", if we will not follow this, the macro is not going to save in our file.






Now working on "Visual Basic" simply click on "Developer Tab" and then click on "Visual Basic Tab" or simply type "Alt + F11"








Once you hit on "Visual Basic Tab" or type "ALT + F11" you will get the following 
image :-



No.1 is the "Project Explorer" all the "Module" will be stored in this project explorer window.

What is Module in Excel VBA?

Module are in which we write our macro programming code and Module are created from Project window as follows:-





For creating a Module Right Click on "VBAProject" and click"Insert" and click "Module".

Following image will appear




There are another important windows, showing in below image:-


1.  Project window

Project window contains all Module

2. Properties window

Properties window can change the properties of the module, change the name of Module or etc.

3. Module window

We write all the VBA coding in the Module window only

4. Immediate window

Immediate window is very useful and we will discuss about this window in future blogs.

Create My First Macro

First of all go to the Module window and simply type Sub and the name of Macro "My_First_Macro"
and a simple coding " MsgBox "Hello World" 




And run the macro by pressing the Red Circle Button in above image and following outcome will appear





VBA coding create a Msgbox containing a message "Hello World"

So this the First VBA post by "Developer Tab" to create a simple macro. This tutorial shows that there is nothing in complicate in VBA, it is just a simple language but as we will go deeply in VBA we will find some little bit difficulty but if we will not miss any topic then it will benot be so difficult as we think.


Goodbye for now

Narendra

No comments:

Post a Comment

Featured post

Pivot Tables