Introduction to Microsoft Excel 2010 Macros
By Amal Prasad Naseem
Contents
Introduction to Macro Introduction to Visual Basic for Application Recording a Macro Looking at the code of Recorded Macro
Introduction
Microsoft Excel 2010 in itself a tool which helps users to work with worksheets & workbooks with ease. This tool is one of the ultimate timesaver, but there are numerous events when you have to do repetitive tasks which are of a very little value but consume your precious time; yet engage you in a less productive activity, which Excel may do automatically. Microsoft Excel 2010 macro is the feature which eliminates or at-least minimize these events which take your precious time in repetitive tasks, and you will be able to capitalize this time in more productive & strategic activities.
Introduction
Apart from automating repetitive tasks Microsoft Excel Macros may also be used to create user interfaces User interfaces are screens where user can give inputs with ease also it enables input validations
Introduction to Visual Basic for Application
What is Visual Basic? Visual Basic is a language which is inherited from a very popular language BASIC. BASIC stands for Beginners Allpurpose Symbolic Instruction Code. Visual Basic is called visual as you can do most of the program by click and go i.e. visually. Its an event driven & object based language. What is Event Driven Language? When we say event driven it means that you can write triggers which will initiate on specific events for example when user opens any document or saves any document What is Object Based Language? Object based language is language in which we can use objects & their properties i.e. we can use the Worksheet object and its property SheetName when some one clicks on a button
Introduction to Visual Basic for Application - cont
Visual Basic for Application Visual Basic for Application is called VBA as it uses Visual Basic language and is capable of using application specific objects i.e. if we talk about VBA for Excel it can use Cell Object, Range Object, Worksheet Object, Workbook Object etc.
Recording your first Macro
Microsoft Excel 2010 provides you a very helpful option which enables you to record a macro and also modify and run afterwards Microsoft Excel 2010 dont provide you the macro recording option by default for enabling macro recording at first you need to enable the Developer Tab, next slide defines step by step procedure to enable Developer Tab
Enabling the Developer Tab
Click on Office Button Click on Excel Options On Popular Tab check Show Developer tab in Ribbon
Enabling the Developer Tab
Once you are done with the process you will have the Developer Tab ready
Recording your first Macro
When you record macro Microsoft Excel 2010 provides you with 2 options either you can record a macro using Absolute References or Relative References If you go for Absolute References Excels behavior will be:
You record a macro in Cell A1 type Excel is Great format Bold Cell B1 Whenever you play / run this macro Excel will always type Excel is Great in Cell A1 and format Bold Cell B1 no matter wherever your cursor is
Recording your first Macro
You can refer to following tab that you can click on Use Relative References which will enable you to record a macro in relative cells, as discussed whenever a relative reference macro is executed it will run in-place
Recording your first Macro
1. Click Developer Tab 2. Click on Use Relative Reference 3. Click on Record Macro
1
3
2
Recording your first Macro
As soon as you press record macro button the following screen will appear In the Macro name box you have to type the name of macro In Shortcut key you can type short cut key which invoke the Macro Store macro (will discussed later) You can also provide description of the macro
Recording your first Macro
As soon as you press OK in the previous dialog box two things happens first record macro button changes to Stop Recording Secondly Excel starts looking for your action and will record the same Please follow steps on the following slide to complete recording
Playing your Macro
You can execute the macro you just recorded by either clicking on the Macro button on the Developer Tab or by pressing Alt F8 , you can see the list of Recorded macros in the dialog box you can press Run button to execute the macro as soon as you press run you can see that your recorded macro will execute
Recording your first Macro
1. Click on Cell A1 type Excel is Great 2. Select cells from A1:C1 click on Home Tab and click on Merge & Center 3. Press Bold & Italics 4. Now click on Developer Tab and click on Stop recording
Behind the scenes
To see what Excel did behind the scenes you can either click on Macros button on Developer Tab and select Edit or you can press Alt F11
What it looks like
This is what Excel did behind the scenes, it written the code for you. You can change the code if you want. You can either close the window or press Alt F11 again to go back to your Excel normal screen
Summary
Macros are used to automate tasks or you can use macros to develop user interfaces Excel Macro use a language called VBA (Visual Basic for Applications), which is both Event Driven and Object Based The easiest way to look at macro is by using the Excels record Macro Feature You can even edit Excels recorded macro by yourself
19
PIVOT TABLE
Pivot Table
Use the PivotTable feature to summarize data in a worksheet, then list and display that data in a table format
The interactive quality of a PivotTable allows you to freely rearrange, or pivot, parts of the table structure around the data Summarize data values within the table
21
Microsoft Office Excel 2007 - Illustrated
PivotTable-Example
Column fields
Row fields
Data summary field
22 Microsoft Office Excel 2007 - Illustrated
Creating a PivotTable Report
Click to add a field to the PivotTable
23 Microsoft Office Excel 2007 - Illustrated
Creating a PivotTable Report (cont.)
Column fields
Row fields
Data area
24 Microsoft Office Excel 2007 - Illustrated
Changing a PivotTables Summary Function and Design
A PivotTables summary function controls what type of calculation is applied to the table data
Unless you specify otherwise, Excel applies the SUM function to numeric data and the COUNT function to data fields containing text Easily change the SUM function to different summary function
25
Microsoft Office Excel 2007 - Illustrated
Changing a PivotTables Summary Function and Design (cont.)
PivotTable showing averages
26
Microsoft Office Excel 2007 - Illustrated
Creating a PivotChart Report
A PivotChart report is a chart that you create from data or from a PivotTable report
A PivotChart has fields that you move to explore new data relationships When you create a PivotChart directly from the data, Excel automatically creates a corresponding PivotTable report
27
Microsoft Office Excel 2007 - Illustrated
Creating a PivotChart Report (cont.)
28
PivotChart
Microsoft Office Excel 2007 - Illustrated
Using the GETPIVOTDATA Function
Ordinary cell references will not work when you want to reference a PivotTable cell in another worksheet
If you change the way data is displayed in a PivotTable, the data moves, rendering an ordinary cell reference incorrect To retrieve summary data from a PivotTable, you need to use the Excel GETPIVOTDATA function
29
Microsoft Office Excel 2007 - Illustrated
Using the GETPIVOTDATA Function (cont.)
GETPIVOTDATA Function
Result of the GETPIVOTDATA Function
30
Microsoft Office Excel 2007 - Illustrated
Summary (cont.)
Use the Refresh button to update PivotTables The structure of a PivotTable can be altered PivotChart reports can be created directly from PivotTables Use the GETPIVOTDATA function to extract information from a PivotTable
31
Microsoft Office Excel 2007 - Illustrated
