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 :
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 :
Excel 2010 :
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
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 :
V. Line Continuation Character
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 :
- Consolidate data from multiple sheets in a single sheet
- Converting multiple excel workbooks into PDF files
- Sending an email to multiple recipients from Outlook
- Highlight row and column of selected cells
- 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.
- Click on File
- Click Options from the list
- Click on Customize Ribbon
- In the Customize the Ribbon section you will notice a listing of the Main tabs. Check Developer option that appears in right side area
- 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
Shortcut Key : Alt + F11
1. Project Window
This window contains the following items :
- Microsoft Objects - It contains a code area for your workbook (ThisWorkbook) and your worksheets
- Forms - It stores any user form that you create
- 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 :
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:
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.
Rules applied to name a variable in VBA
Declare a string variable
Declare a numeric variable
Declare a custom number formatted variable
Declaring multiple variables
IV. Comment TextI. Module (where code is written)
Module is an area where we write VBA code.
Instructions :
- Open Excel Workbook
- Press ALT + F11 to open visual basic editor (VBE)
- 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
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.
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.
- The length of a variable name must be less than 255 characters
- No spacing is allowed within the variable name
- It must not begin with a number
- Period (.) is not permitted
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
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).
Next Post : Record Your First MacroSub Macro2()Range("A1").Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Amazing content if you wish to start from the scratch!!
ReplyDeleteHas been really helpful..great effort!!
Very well done! :)
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.
ReplyDeleteYou are amazing!
Thank you for your lovely words and appreciation!
DeleteThank y for sharing
ReplyDeleteIl's really halpful
The clearest explanation of basic VBA I've found
ReplyDeleteThank you for your appreciation!
Deletegood for all content
ReplyDeleteGlad you found it useful.
DeleteMy best website
ReplyDeleteThank you for providing best content. I'm learning it now.
ReplyDeleteCan you please send me a soft copy to my email address?
ReplyDeleteHow does someone test their understanding of what they learn from your website without installing any softwares etc?
ReplyDeleteDear Sir,
ReplyDeleteI 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
Great Information Keep going
ReplyDeletehttps://excelpathblog.com/
VERY GOOD WORK AND EXPLANATION.. THANK YOU
ReplyDeleteClear and Useful..
ReplyDelete