Excel 2013 VBA and Macros Book

Introduction ........................................................ 1

Getting Results with VBA ...................................................................1

What Is in This Book? .................................................................1

Reduce the Learning Curve .............................................1

Excel VBA Power .................................................................2

Techie Stuff Needed to Produce Applications ................................2

Does This Book Teach Excel? ............................................................2

The Future of VBA and Windows Versions of Excel ..............................4

Versions of Excel ......................................................................................4

Special Elements and Typographical Conventions .........................................4

Code Files .........................................................................................................5

Next Steps..........................................................................5

1 Unleash the Power of Excel with VBA ......................................... 7

The Power of Excel .....................................................................7

Barriers to Entry .......................................................................7

The Macro Recorder Doesn’t Work! ...................................................7

Visual Basic Is Not Like BASIC ..............................................................8

Good News: Climbing the Learning Curve Is Easy ....................................8

Great News: Excel with VBA Is Worth the Effort ..................................8

Knowing Your Tools: The Developer Tab .............................................................9

Understanding Which File Types Allow Macros .........................................10

Macro Security ...........................................................................................11

Adding a Trusted Location ........................................................................12

Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations ........................13

Using Disable All Macros with Notification ...............................................13

Overview of Recording, Storing, and Running a Macro ......................................14

Filling Out the Record Macro Dialog ..................................................................14

Running a Macro..............................................................................................16

Creating a Macro Button on the Ribbon .........................................................16

Creating a Macro Button on the Quick Access Toolbar ............................17

Assigning a Macro to a Form Control, Text Box, or Shape .......................................18

Understanding the VB Editor ...............................................19

VB Editor Settings ........................................................................20

The Project Explorer ...........................................................20

The Properties Window .......................................................................21

Understanding Shortcomings of the Macro Recorder ..................................21

Examining Code in the Programming Window .....................................23

Running the Macro on Another Day Produces Undesired Results ...................25

Possible Solution: Use Relative References When Recording ..............................26

Never Use the AutoSum or Quick Analysis While Recording a Macro .....................30

Three Tips When Using the Macro Recorder ..............................................31

Next Steps..................................................................................31

2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar? ........................33

I Can’t Understand This Code ................................................................33

Understanding the Parts of VBA “Speech” .............................34

VBA Is Not Really Hard .......................................................37

VBA Help Files: Using F1 to Find Anything ....................................37

Using Help Topics ........................................................................38

Examining Recorded Macro Code: Using the VB Editor and Help .............................39

Optional Parameters ..............................................40

Defined Constants ..........................................................41

Properties Can Return Objects ...................................45

Using Debugging Tools to Figure Out Recorded Code .........................46

Stepping Through Code .................................................................46

More Debugging Options: Breakpoints .......................................49

Backing Up or Moving Forward in Code .................................49

Not Stepping Through Each Line of Code.....................................50

Querying Anything While Stepping Through Code ..........................50

Using a Watch to Set a Breakpoint .........................................53

Using a Watch on an Object ...........................54

Object Browser: The Ultimate Reference ....................................55

Seven Tips for Cleaning Up Recorded Code ................................56

Tip 1: Don’t Select Anything .............................56

Tip 2: Cells(2,5) Is More Convenient Than Range(“E2”) ..........................57

Tip 3: Use More Reliable Ways to Find the Last Row .......................................58

Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas ......................59

Tip 5: R1C1 Formulas That Make Your Life Easier .............................59

Tip 6: Learn to Copy and Paste in a Single Statement................................59

Tip 7: Use With...End With to Perform Multiple Actions .................60

Next Steps............................................................................................63

3 Referring to Ranges ......................................................65

The Range Object ........................................................................65

Syntax to Specify a Range ................................................................66

Named Ranges ........................................................................................66

Shortcut for Referencing Ranges .................................................................66

Referencing Ranges in Other Sheets ..................................................67

Referencing a Range Relative to Another Range ............................67

Use the Cells Property to Select a Range .................................................68

Use the Offset Property to Refer to a Range ..............................................69

Use the Resize Property to Change the Size of a Range ........................71

Use the Columns and Rows Properties to Specify a Range ......................72

Use the Union Method to Join Multiple Ranges ................................................72

Use the Intersect Method to Create a New Range from Overlapping Ranges..........................72

Use the ISEMPTY Function to Check Whether a Cell Is Empty ............................................73

Use the CurrentRegion Property to Select a Data Range .....................................73

Use the Areas Collection to Return a Noncontiguous Range .........................................76

Referencing Tables ............................................................................77

Next Steps..................................................................................................77

4 Looping and Flow Control ................................................................79

For...Next Loops ............................................................................................79

Using Variables in the For Statement ..............................................................82

Variations on the For...Next Loop .................................................................82

Exiting a Loop Early After a Condition Is Met ....................................................83

Nesting One Loop Inside Another Loop .........................................................84

Do Loops .....................................................................................................85

Using the While or Until Clause in Do Loops ........................................87

While...Wend Loops .............................................................................88

The VBA Loop: For Each ............................................................................89

Object Variables .........................................................................................89

Flow Control: Using If...Then...Else and Select Case ...........................................92

Basic Flow Control: If...Then...Else ................................................92

Conditions ..............................................................................................92

If...Then...End If ....................................................................................93

Either/Or Decisions: If...Then...Else...End If .............................................93

Using If...ElseIf...End If for Multiple Conditions .....................................93

Using Select Case...End Select for Multiple Conditions ...............................94

Complex Expressions in Case Statements ........................................................95

Nesting If Statements .....................................................................95

Next Steps..............................................................................................................97

5 R1C1-Style Formulas ................................................................99

Referring to Cells: A1 Versus R1C1 References ...........................99

Toggling to R1C1-Style References ................................................100

The Miracle of Excel Formulas ...............................................................101

Enter a Formula Once and Copy 1,000 Times ....................................101

The Secret: It’s Not That Amazing ............................................................102

Explanation of R1C1 Reference Style ..............................................................103

Using R1C1 with Relative References....................................................104

Using R1C1 with Absolute References ......................................................104

Using R1C1 with Mixed References...............................................................105

Referring to Entire Columns or Rows with R1C1 Style .....................................................105

Replacing Many A1 Formulas with a Single R1C1 Formula ...................................106

Remembering Column Numbers Associated with Column Letters ................................107

Array Formulas Require R1C1 Formulas .................................................................108

Next Steps...................................................................................................................109

6 Create and Manipulate Names in VBA ....................................................111

Excel Names .....................................................................................111

Global Versus Local Names .............................................................111

Adding Names ...................................................................................112

Deleting Names ................................................................................113

Adding Comments .......................................................................114

Types of Names .................................................................114

Formulas ...........................................................................114

Strings .........................................................................................115

Numbers ........................................................................................116

Tables ................................................................................................117

Using Arrays in Names .........................................................................117

Reserved Names .....................................................................................118

Hiding Names ................................................................................................119

Checking for the Existence of a Name ...............................................................119

Next Steps............................................................................................121

7 Event Programming ..............................................................123

Levels of Events ...................................................................123

Using Events ..........................................................................124

Event Parameters ...................................................................124

Enabling Events .................................................................125

Workbook Events ..............................................................................125

Workbook Level Sheet and Chart Events ...................................................129

Worksheet Events ...................................................................132

Chart Sheet Events .................................................................................137

Embedded Charts ...........................................................................137

Application-Level Events ................................................................................140

Next Steps........................................................................................................................148

8 Arrays ....................................................................................149

Declare an Array ................................................................149

Declare a Multidimensional Array ................................150

Fill an Array......................................................................151

Retrieve Data from an Array .............................................................152

Use Arrays to Speed Up Code ........................................................153

Use Dynamic Arrays ..................................................................................155

Passing an Array .................................................................................156

Next Steps................................................................................................................................157

9 Creating Classes, Records, and Collections ......................................................159

Inserting a Class Module ...............................................................................159

Trapping Application and Embedded Chart Events ..........................................159

Application Events ...........................................................................................160

Embedded Chart Events ..................................................................................161

Creating a Custom Object .....................................................................163

Using a Custom Object .............................................................................163

Using Property Let and Property Get to Control How Users Utilize Custom Objects .......................................165

Using Collections to Hold Multiple Records ...............................................................................167

Creating a Collection in a Standard Module ..................................................167

Creating a Collection in a Class Module .........................................................168

Using User-Defined Types to Create Custom Properties .............................................172

Next Steps.......................................................................................................................174

10 Userforms: An Introduction ...........................................................175

User Interaction Methods ..........................................................................175

Input Boxes..............................................................................................175

Message Boxes .......................................................................................176

Creating a Userform ......................................................................... 176

Calling and Hiding a Userform ................................................177

Programming the Userform ................................................................178

Userform Events .............................................................................178

Programming Controls .....................................................................................180

Using Basic Form Controls...................................................................................181

Using Labels, Text Boxes, and Command Buttons .....................................................181

Deciding Whether to Use List Boxes or Combo Boxes in Forms ...............................183

Adding Option Buttons to a Userform ...........................................................186

Adding Graphics to a Userform ....................................................................187

Using a Spin Button on a Userform ...........................................188

Using the MultiPage Control to Combine Forms .........................................190

Verifying Field Entry ....................................................................................................192

Illegal Window Closing ............................................................................192

Getting a Filename ..............................................................................................193

Next Steps..........................................................................................................................195

11 Data Mining with Advanced Filter .....................................................197

Replacing a Loop with AutoFilter ............................................................................197

Using New AutoFilter Techniques ............................................................200

Selecting Visible Cells Only ..........................................................203

Advanced Filter Is Easier in VBA Than in Excel ......................................................204

Using the Excel Interface to Build an Advanced Filter ....................................205

Using Advanced Filter to Extract a Unique List of Values ..............................................206

Extracting a Unique List of Values with the User Interface ........................206

Extracting a Unique List of Values with VBA Code ..................................................207

Getting Unique Combinations of Two or More Fields ..............................................211

Using Advanced Filter with Criteria Ranges .................................................................212

Joining Multiple Criteria with a Logical OR ................................................................213

Joining Two Criteria with a Logical AND ..............................................214

Other Slightly Complex Criteria Ranges .....................................214

The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula ...214

Using Filter in Place in Advanced Filter ............................................221

Catching No Records When Using Filter in Place..................222

Showing All Records After Filter in Place .............................. 222

The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only ...............222

Copying All Columns .............................................................223

Copying a Subset of Columns and Reordering ............................224

Excel in Practice: Turning Off a Few Drop-Downs in the AutoFilter .......................229

Next Steps............................................................................................................230

12 Using VBA to Create Pivot Tables...................................231

Introducing Pivot Tables .............................................................231

Understanding Versions ...................................................................231

Building a Pivot Table in Excel VBA ..........................................................232

Defining the Pivot Cache ..........................................................................232

Creating and Configuring the Pivot Table ...............................................233

Adding Fields to the Data Area ....................................................................234

Learning Why You Cannot Move or Change Part of a Pivot Report ...............................237

Determining the Size of a Finished Pivot Table to Convert the Pivot Table to Values .................238

Using Advanced Pivot Table Features .............................................240

Using Multiple Value Fields ........................................................240

Grouping Daily Dates to Months, Quarters, or Years ......................241

Changing the Calculation to Show Percentages ..........................243

Eliminating Blank Cells in the Values Area...................................246

Controlling the Sort Order with AutoSort .........................................246

Replicating the Report for Every Product ...................................246

Filtering a Dataset ...........................................249

Manually Filtering Two or More Items in a Pivot Field ....................249

Using the Conceptual Filters .......................................250

Using the Search Filter ........................................................254

Setting Up Slicers to Filter a Pivot Table .......................................................257

Setting Up a Timeline to Filter an Excel 2013 Pivot Table ...............................259

Using the Data Model in Excel 2013 ................................................262

Adding Both Tables to the Data Model .........................................262

Creating a Relationship Between the Two Tables ...........................263

Defining the PivotCache and Building the Pivot Table ...............................263

Adding Model Fields to the Pivot Table ..................................................264

Adding Numeric Fields to the Values Area ............................264

Putting It All Together ................................................265

Using Other Pivot Table Features ................................267

Calculated Data Fields...........................................267

Calculated Items .............................................................268

Using ShowDetail to Filter a Recordset ...............................268

Changing the Layout from the Design Tab ..............................268

Settings for the Report Layout ................................. 269

Suppressing Subtotals for Multiple Row Fields .................... 269

Next Steps......................................................................... 270

13 Excel Power ......................................................271

File Operations ..........................................................271

List Files in a Directory ................................................271

Import CSV ................................................................273

Read Entire TXT to Memory and Parse .....................................274

Combining and Separating Workbooks ..................................................275

Separate Worksheets into Workbooks .........................................275

Combine Workbooks .........................................................................276

Filter and Copy Data to Separate Worksheets ....................................277

Export Data to Word ...................................................................278

Working with Cell Comments ........................................................279

List Comments .....................................................................279

Resize Comments ...........................................................................281

Place a Chart in a Comment .......................................................................282

Utilities to Wow Your Clients ............................................................283

Using Conditional Formatting to Highlight Selected Cell ...................................283

Highlight Selected Cell Without Using Conditional Formatting ...............................285

Custom Transpose Data ...............................286

Select/Deselect Noncontiguous Cells ..................288

Techniques for VBA Pros ..........................290

Excel State Class Module .....................290

Pivot Table Drill-Down ...............................292

Custom Sort Order ...................................................293

Cell Progress Indicator ...........................................294

Protected Password Box ..................................295

Change Case ..........................................................297

Selecting with SpecialCells ................................................298

ActiveX Right-Click Menu ...........................................299

Cool Applications ....................................................................................300

Historical Stock/Fund Quotes ..........