Part 1 Recording Macros XIV
Lesson 1 Enable the Developer Tab 1
What You Will
Learn................................................................................1
Notes from the
Lesson............................................................................1
Differences in Excel
2003........................................................................1
Lesson 2 Recording Your First Macro 2
What You Will
Learn................................................................................2
Notes from the
Lesson............................................................................2
In Excel 2003
...........................................................................................2
Code from the
Lesson.............................................................................3
Lesson 3 Running a Macro 4
What You Will
Learn................................................................................4
Notes from the
Lesson............................................................................4
In Excel 2003
...........................................................................................5
Code from the
Lesson.............................................................................5
Lesson 4 Keys to Successful Recording 6
What You Will
Learn................................................................................6
Notes from the
Lesson............................................................................6
In Excel 2003
...........................................................................................6
Code from the
Lesson.............................................................................7
Lesson 5 Variable Number of Rows 8
What You Will
Learn................................................................................8
Related
Lessons.......................................................................................8
Notes from the
Lesson............................................................................8
Code from the
Lesson.............................................................................8
Lesson 6 Adding Totals 9
What You Will
Learn................................................................................9
Notes from the
Lesson............................................................................9
Code from the
Lesson.............................................................................9
Lesson 7 Opening a Workbook with Macros 12
What You Will
Learn..............................................................................12
Notes from the
Lesson..........................................................................12
In Excel 2003
.........................................................................................12
Part II Visual Basic Editor 13
Lesson 8 Introducing the VBA Editor 14
What You Will
Learn..............................................................................14
Notes from the
Lesson..........................................................................14
In Excel 2003
.........................................................................................14
Code from the
Lesson...........................................................................14
Lesson 9 VBA Help 15
What You Will
Learn..............................................................................15
Notes from the
Lesson..........................................................................15
Lesson 10 Object Browser 16
What You Will
Learn..............................................................................16
Notes from the
Lesson..........................................................................16
Lesson 11 Stepping Through Code 17
What You Will
Learn..............................................................................17
Related
Lessons.....................................................................................17
Notes from the
Lesson..........................................................................17
Code from the
Lesson...........................................................................18
Lesson 12 Immediate Window 19
What You Will
Learn..............................................................................19
Notes from the
Lesson..........................................................................19
Code from the
Lesson...........................................................................19
Lesson 13 The Watch Window 22
What You Will
Learn..............................................................................22
Notes from the
Lesson..........................................................................22
Code from the
Lesson...........................................................................22
Lesson 14 Breakpoints 24
What You Will
Learn..............................................................................24
Notes from the
Lesson..........................................................................24
Code from the
Lesson...........................................................................24
Part III VBA Syntax 25
Lesson 15 VBA Parts of Speech 26
What You Will
Learn..............................................................................26
Notes from the
Lesson..........................................................................26
Lesson 16 Using Regular Variables 27
What You Will
Learn..............................................................................27
Related Lesson
......................................................................................27
Notes from the
Lesson..........................................................................27
In Excel 2003
.........................................................................................27
Code from the
Lesson...........................................................................28
Lesson 17 Using Object Variables 29
What You Will
Learn..............................................................................29
Notes from the
Lesson..........................................................................29
Code from the
Lesson...........................................................................30
Lesson 18 Better Ways to Refer to Cells 31
What You Will
Learn..............................................................................31
Notes from the
Lesson..........................................................................31
Code from the
Lesson...........................................................................31
Lesson 19 To Declare or Not To Declare? 33
What You Will
Learn..............................................................................33
Notes from the
Lesson..........................................................................33
Code from the
Lesson...........................................................................34
Part IV Making Decisions 35
Lesson 20 If…Then 36
What You Will
Learn..............................................................................36
Notes from the
Lesson..........................................................................36
Code from the
Lesson...........................................................................36
Lesson 21 If…Then…Else 37
What You Will
Learn..............................................................................37
Notes from the
Lesson..........................................................................37
Code from the
Lesson...........................................................................37
Lesson 22 Select Case 38
What You Will
Learn..............................................................................38
Notes from the
Lesson..........................................................................38
Code from the
Lesson...........................................................................38
Part V Looping 40
Lesson 23 Finding the Final Row 41
What You Will
Learn..............................................................................41
Notes from the
Lesson..........................................................................41
Code from the
Lesson...........................................................................41
Lesson 24 Basic Looping 42
What You Will
Learn..............................................................................42
Notes from the
Lesson..........................................................................42
Code from the
Lesson...........................................................................43
Lesson 25 Loop Example 44
What You Will
Learn..............................................................................44
Notes from the
Lesson..........................................................................44
Code from the
Lesson...........................................................................45
Lesson 26 Deleting While Looping 46
What You Will
Learn..............................................................................46
Notes from the
Lesson..........................................................................46
Code from the
Lesson...........................................................................46
Lesson 27 Every Other Row Loops 47
What You Will
Learn..............................................................................47
Notes from the
Lesson..........................................................................47
Code from the
Lesson...........................................................................47
Lesson 28 Other Legacy Loops 49
What You Will
Learn..............................................................................49
Notes from the
Lesson..........................................................................49
Code from the
Lesson...........................................................................49
Lesson 29 For Each Loops 51
What You Will
Learn..............................................................................51
Notes from the
Lesson..........................................................................51
Code from the
Lesson...........................................................................52
Part VI Worksheets and Workbooks 53
Lesson 30 Creating Worksheets 54
What You Will
Learn..............................................................................54
Related Lesson
......................................................................................54
Notes from the
Lesson..........................................................................54
Code from the
Lesson...........................................................................55
Bonus Code
.................................................................................56
Lesson 31 Creating Workbooks 57
What You Will
Learn..............................................................................57
Notes from the
Lesson..........................................................................57
Code from the
Lesson...........................................................................58
Lesson 32 Listing Files in a Folder 60
What You Will
Learn..............................................................................60
Notes from the
Lesson..........................................................................60
Code from the
Lesson...........................................................................60
Bonus Code
.................................................................................63
Lesson 33 Combining Workbooks 65
What You Will
Learn..............................................................................65
Related Lesson
......................................................................................65
Notes from the
Lesson..........................................................................65
Code from the
Lesson...........................................................................66
Part VII Formulas 68
Lesson 34 A1-Style Formulas 69
What You Will
Learn..............................................................................69
Related Lesson
......................................................................................69
Notes from the
Lesson..........................................................................69
Code from the
Lesson...........................................................................70
Lesson 35 R1C1-Style Formulas 71
What You Will
Learn..............................................................................71
Notes from the
Lesson..........................................................................71
Code from the
Lesson...........................................................................72
Lesson 36 Using Excel Functions in Code 73
What You Will
Learn..............................................................................73
Notes from the
Lesson..........................................................................73
Code from the
Lesson...........................................................................74
Lesson 37 Adding Functions to Excel 85
What You Will
Learn..............................................................................85
Notes from the
Lesson..........................................................................85
Code from the
Lesson...........................................................................86
Part VIII Power Tools 87
Lesson 38 Advanced Filter 88
What You Will
Learn..............................................................................88
Notes from the
Lesson..........................................................................88
Code from the
Lesson...........................................................................90
Lesson 39 Filter Instead of Looping 92
What You Will
Learn..............................................................................92
Related Lesson
......................................................................................92
Notes from the
Lesson..........................................................................92
Code from the
Lesson...........................................................................93
Lesson 40 Pivot Tables 94
What You Will
Learn..............................................................................94
Notes from the
Lesson..........................................................................94
Code from the
Lesson...........................................................................95
Lesson 41 Charts 98
What You Will
Learn..............................................................................98
Notes from the
Lesson..........................................................................98
Code from the
Lesson...........................................................................99
Lesson 42 Event Handler Macros 100
What You Will
Learn............................................................................100
Notes from the
Lesson........................................................................100
Code from the
Lesson.........................................................................101
Part IX Interacting 103
Lesson 43 Interacting 104
What You Will
Learn............................................................................104
Notes from the
Lesson........................................................................104
Code from the
Lesson.........................................................................105
Lesson 44 Getting File Names 106
What You Will
Learn............................................................................106
Notes from the
Lesson........................................................................106
Code from the
Lesson.........................................................................106
Lesson 45 Userforms 108
What You Will
Learn............................................................................108
Related
Lessons...................................................................................108
Notes from the
Lesson........................................................................108
Code from the
Lesson.........................................................................111
Lesson 46 Error Handling 112
What You Will
Learn............................................................................112
Notes from the
Lesson........................................................................112
Code from the
Lesson.........................................................................113
Lesson 47 Suppressing Alerts 117
What You Will
Learn............................................................................117
Notes from the
Lesson........................................................................117
Code from the
Lesson.........................................................................117
Lesson 48 Faster Code 118
What You Will
Learn............................................................................118
Notes from the
Lesson........................................................................118
Code from the
Lesson.........................................................................118
Part X Best Practices 119
Lesson 49 Separating Code and Data 120
What You Will
Learn............................................................................120
Notes from the
Lesson........................................................................120
Code from the
Lesson.........................................................................120
Lesson 50 Cleaning Recorded Code 122
What You Will
Learn............................................................................122
Notes from the
Lesson........................................................................122
Code from the
Lesson.........................................................................123
Next Steps
TOC, 9780789739384, MF
Bill Jelen is the host of MrExcel.com and the author of 24 books on
Microsoft Excel, including
Special Edition Using Microsoft Office Excel 2007, Pivot Table Data
Crunching for Microsoft
Office Excel 2007, VBA and Macros for Microsoft Excel, Excel Gurus
Gone Wild, Excel for
Marketing Managers, and Guerilla Data Analysis Using Microsoft
Excel. He has made more than
60 guest appearances on TechTV with Leo Laporte and was voted guest
of the year on the
Computer America radio show. He has produced more than 900 episodes
of his daily video podcast
“Learn Excel from MrExcel.” Bill will entertain you while showing
you the powerful tricks in
Excel. Before founding MrExcel.com in 1998, Jelen spent 12 years in
the trenches as a financial
analyst for the accounting, finance, marketing, and operations
departments of a publicly held company.
Since then, his company has automated Excel reports for hundreds of
clients around the
world. The website answers more than 30,000 questions a year–for
free–for readers all over the
world. Jelen joins us from Akron, Ohio.
Ask a Question About this Product More... |