MS Excel

1
MS Excel Macros using VBA

• By - Amit Dhingra

2
Agenda

• Macro Security
• Recording a Macro
• Running a Macro
• Introduction to VBA
• Writing and debugging code in VBA
• Looping using VBA

3
Macro Security

• 4 types
• Low
• Medium
• High
• Very High
• Option to choose which macro to run available
  under the Medium security level

4
Recording a Macro
5
Running a Macro

• Press ALT F8 for the list of Macros
• Press the Shortcut directly for the Macro

6
Exercise
A B C
S.No Name Marks
1 Amit 56
2 Kamal 67
3 Namrata 68
4 Deepti 59
5 Yogesh 78
Record a macro that gives the following output

1. Cell D1Average Marks
2. Cell E1Total Students
3. Cell F1Count of Students who have scored above
   65
4. Cell D2 to D5 should have either ABOVE or
   BELOW based upon the value in cells C2 to C5 as
   compared to the average in D1.

7
Some more Macros

• Working with multiple sheets
• Working with multiple files

8
Exercise

• Create a Macro that gives
• The first 5 characters of Asset Location
  (Column F) in Facility (Column G)
• Fetches the City (Column H) based upon Facility
  (Column G) from the Facility Code sheet.
• e.g.

9
Introduction to VBA

• Right Click on sheet name - gt view code
• Press ALTF11
• Write or Edit the code for macro in the modules
  under project explorer

10
Writing Code in VBA
11
Conditional Statements

• If. Then..End if
• Select Case.. End Select

12
Exercise

• Display the Full Name as
• Last Name , First Name - If Last name is not
  blank
• First Name - If Last name is blank

For the following data SNo FirstName LastName F
ull Name 1 Amit Gupta ? 2 Deepak ? 3 Aja
y Sharma ? 4 Payal Mehta ? 5 Deepak
?
13
Looping

• WhileWend
• Do.Loop While
• ForNext

14
Exercise

• Display the sum of all the data of Column A in
  the cell F1.
• Do not use the Sum Formula. Use the While / Do
  or For loop.
• The number of cells which have the data in Column
  A is not fixed.

15
ThanksContact Details Amit
Dhingra SH-103, A-91, Sector-2,
Noida 0120-3064000 ext. 3648 Nortel Ext.
3648