Lesson 1 : Getting Started with Excel VBA

Deepanshu Bhalla 16 Comments
This tutorial is designed for beginners who want to get started with Excel VBA. It gives you an overview of excel macros and VBA programming.

What are excel macros?

A macro is used to automate a task that we perform repeatedly or on a daily basis. The macros that we built in excel are called Excel Macros.

Examples :

  1. Consolidate data from multiple sheets in a single sheet
  2. Converting multiple excel workbooks into PDF files
  3. Sending an email to multiple recipients from Outlook
  4. Highlight row and column of selected cells
  5. Create PowerPoint Presentation using Excel

What is VBA?

The acronym VBA stands for Visual Basic for Applications. It is an integration of the Visual Basic with Microsoft Office applications (MS Excel, MS PowerPoint, MS Access, MS Word and MS Outlook). By running VBA within the Microsoft Office applications, you can automate repetitive tasks.


Step by Step Instructions-

Step 1 : Enable Developer Tab

Excel 2007 : 
Open Excel Options from the main menu, go to Popular and check Show Developer tab in the Ribbon and click OK.


Excel 2010 :
  1. Click on File 
  2. Click Options from the list 
  3. Click on Customize Ribbon
  4. In the Customize the Ribbon section you will notice a listing of the Main tabs. Check Developer option that appears in right side area
  5. Click OK

Once you have completed all of the above steps, a new tab will be added :



Step 2 : Getting Started - The Look of the Visual Basic Editor

To write any VBA code, you need a programming editor i.e. Visual Basic Editor in Excel. You can open it by pressing the shortcut key combination Alt F11. (Go to Developer tab >> Visual Basic)


Shortcut Key : Alt + F11
1. Project Window
This window contains the following items :
  1. Microsoft Objects - It contains a code area for your workbook (ThisWorkbook) and your worksheets
  2. Forms - It stores any user form that you create
  3. Modules - It stores macros and user defined functions that you create.

2. Properties Window
The Properties Window allows you to modify properties of object, form and module.

3. Code Window
It is the area where you write and edit visual basic code.



Step 3 : Fundamentals of VBA Programming

I. Module (where code is written)

Module is an area where we write VBA code.

Instructions :
  1. Open Excel Workbook
  2. Press ALT + F11 to open visual basic editor (VBE)
  3. To insert a module, go to Insert > Module




To view a module, just double click on its icon in the Project Explorer window in the VBA Editor


II. Sub Procedures - Tell excel how to perform a specific task

Rule : All Sub procedures must begin with the word Sub followed by a Macro Name and a pair of empty parenthesis () and end with End Sub.

The example is shown below:
Sub Macro1()
End Sub

What is Sub?
To create a sub procedure, start with the Sub keyword.

What is Macro1?
It is a macro name. You can give your macro any name you want.

What is End Sub?
To end a sub procedure, use the End Sub keyword.


III. Variable Data Types

In a standard VBA code, we need to declare a variable with its data type.

The following is a list of different data types and their storage information.



Dim is used to declare a variable name.

Rules applied to name a variable in VBA
  1. The length of a variable name must be less than 255 characters
  2. No spacing is allowed within the variable name
  3. It must not begin with a number
  4. Period (.) is not permitted

Declare a string variable
Sub temp()
Dim Name as String
End Sub

Declare a numeric variable
Sub temp()
Dim quantity as double
End Sub

Declare a custom number formatted variable
Sub temp()
Dim price as currency
End Sub

Declaring multiple variables
Sub temp()
Dim score as single, quantity as double, age as integer
End Sub


IV. Comment Text

We generally comment our code to explain the purpose of program . To comment a code we must precede it with a single ' (apostrophe). Excel will ignore any text preceded with a '. The example is shown below :

Sub Macro1()
'In cell A2, multiply cell value in cell A1 by 2
Range("A1").Value = 15
Range("A2").Formula = "=A1*2"
End Sub

V.   Line Continuation Character

If a statement is too long to fit on a line, it can be continued with   _ ( space then an Underscore).
Sub Macro2()
Range("A1").Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Next Post : Record Your First Macro
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

16 Responses to "Lesson 1 : Getting Started with Excel VBA"
  1. Amazing content if you wish to start from the scratch!!
    Has been really helpful..great effort!!
    Very well done! :)

    ReplyDelete
  2. Deepanshu Bhalla, I absolutely love your website. I needed to write a macro at work today and wanted to find some basic information online to get started. I visited your website and found exactly what I was looking for and explained so well.

    You are amazing!

    ReplyDelete
    Replies
    1. Thank you for your lovely words and appreciation!

      Delete
  3. Thank y for sharing
    Il's really halpful

    ReplyDelete
  4. The clearest explanation of basic VBA I've found

    ReplyDelete
  5. Thank you for providing best content. I'm learning it now.

    ReplyDelete
  6. Can you please send me a soft copy to my email address?

    ReplyDelete
  7. How does someone test their understanding of what they learn from your website without installing any softwares etc?

    ReplyDelete
  8. Dear Sir,
    I need code for creating a macro. This macro will collect some data from excel sheet and post to a website repeatedly i.e will do automation work.
    Please sir, send the code to sjibl2016@gmail.com

    ReplyDelete
  9. Great Information Keep going
    https://excelpathblog.com/

    ReplyDelete
  10. VERY GOOD WORK AND EXPLANATION.. THANK YOU

    ReplyDelete
Next → ← Prev