Excel VBA and Macros with MrExcel LiveLessons (Video Training)

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