Excel 2019 Power Programming with VBA E-Book

Michael Alexander

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1009

Veröffentlichungsjahr: 2019

Beschreibung

Maximize your Excel experience with VBA Excel 2019 Power Programming with VBA is fully updated to cover all the latest tools and tricks of Excel 2019. Encompassing an analysis of Excel application development and a complete introduction to Visual Basic for Applications (VBA), this comprehensive book presents all of the techniques you need to develop both large and small Excel applications. Over 800 pages of tips, tricks, and best practices shed light on key topics, such as the Excel interface, file formats, enhanced interactivity with other Office applications, and improved collaboration features. Understanding how to leverage VBA to improve your Excel programming skills can enhance the quality of deliverables that you produce--and can help you take your career to the next level. * Explore fully updated content that offers comprehensive coverage through over 900 pages of tips, tricks, and techniques * Leverage templates and worksheets that put your new knowledge in action, and reinforce the skills introduced in the text * Improve your capabilities regarding Excel programming with VBA, unlocking more of your potential in the office Excel 2019 Power Programming with VBA is a fundamental resource for intermediate to advanced users who want to polish their skills regarding spreadsheet applications using VBA.

Leseprobe

Table of Contents

Cover

Introduction

Topics Covered

What You Need to Know

What You Need to Have

Conventions Used in This Book

What the Icons Mean

How This Book Is Organized

How to Use This Book

What's on the Website

Part I: Introduction to Excel VBA

CHAPTER 1: Essentials of Spreadsheet Application Development

What Is a Spreadsheet Application?

Steps for Application Development

Determining User Needs

Planning an Application That Meets User Needs

Determining the Most Appropriate User Interface

Concerning Yourself with the End User

Other Development Issues

CHAPTER 2: Introducing Visual Basic for Applications

Getting a Head Start with the Macro Recorder

Working with the Visual Basic Editor

VBA Fundamentals

Deep Dive: Working with Range Objects

Essential Concepts to Remember

Don't Panic—You Are Not Alone

CHAPTER 3: VBA Programming Fundamentals

VBA Language Elements: An Overview

Comments

Variables, Data Types, and Constants

Assignment Statements

Arrays

Declaring arrays

Object Variables

User-Defined Data Types

Built-in Functions

Manipulating Objects and Collections

Controlling Code Execution

CHAPTER 4: Working with VBA Sub Procedures

About Procedures

Executing Sub Procedures

Passing Arguments to Procedures

Error-Handling Techniques

A Realistic Example That Uses Sub Procedures

Utility availability

Evaluating the project

CHAPTER 5: Creating Function Procedures

Sub Procedures vs. Function Procedures

Why Create Custom Functions?

An Introductory Function Example

Function Procedures

Function Arguments

Function Examples

Emulating Excel's SUM Function

Extended Date Functions

Debugging Functions

Dealing with the Insert Function Dialog Box

Using Add-Ins to Store Custom Functions

Using the Windows API

CHAPTER 6: Understanding Excel's Events

What You Should Know About Events

Getting Acquainted with Workbook-Level Events

Examining Worksheet Events

Monitoring with Application Events

CHAPTER 7: VBA Programming Examples and Techniques

Learning by Example

Working with Ranges

Working with Workbooks and Sheets

VBA Techniques

Some Useful Functions for Use in Your Code

Some Useful Worksheet Functions

Windows API Calls

Part II: Advanced VBA Techniques

CHAPTER 8: Working with Pivot Tables

An Introductory Pivot Table Example

Creating a More Complex Pivot Table

Creating Multiple Pivot Tables

Creating a Reverse Pivot Table

CHAPTER 9: Working with Charts

Getting the Inside Scoop on Charts

Creating an Embedded Chart

Creating a Chart on a Chart Sheet

Modifying Charts

Using VBA to Activate a Chart

Moving a Chart

Using VBA to Deactivate a Chart

Determining Whether a Chart Is Activated

Deleting from the ChartObjects or Charts Collection

Looping Through All Charts

Sizing and Aligning ChartObjects

Creating Lots of Charts

Exporting a Chart

Changing the Data Used in a Chart

Using VBA to Display Custom Data Labels on a Chart

Displaying a Chart in a UserForm

Understanding Chart Events

Discovering VBA Charting Tricks

Working with Sparkline Charts

CHAPTER 10: Interacting with Other Applications

Understanding Microsoft Office Automation

Automating Access from Excel

Automating Word from Excel

Automating PowerPoint from Excel

Automating Outlook from Excel

Starting Other Applications from Excel

CHAPTER 11: Working with External Data and Files

Working with External Data Connections

Power Query Basics

Using ADO and VBA to Pull External Data

Working with Text Files

Text File Manipulation Examples

Performing Common File Operations

Zipping and Unzipping Files

Part III: Working with UserForms

CHAPTER 12: Leveraging Custom Dialog Boxes

Alternatives to UserForms

Using an Input Box

Using the VBA MsgBox Function

Using the Excel GetOpenFilename Method

Using the Excel GetSaveAsFilename Method

Prompting for a Folder

Displaying Excel's Built-in Dialog Boxes

Displaying a Data Form

CHAPTER 13: Introducing UserForms

How Excel Handles Custom Dialog Boxes

Inserting a New UserForm

Adding Controls to a UserForm

Toolbox Controls

Adjusting UserForm Controls

Adjusting a Control's Properties

Displaying a UserForm

Closing a UserForm

Creating a UserForm: An Example

Referencing UserForm Controls

Customizing the Toolbox

Creating UserForm Templates

A UserForm Checklist

CHAPTER 14: Looking at UserForm Examples

Creating a UserForm “Menu”

Selecting Ranges from a UserForm

Creating a Splash Screen

Disabling a UserForm's Close Button

Changing a UserForm's Size

Zooming and Scrolling a Sheet from a UserForm

Exploring ListBox Techniques

Using the MultiPage Control in a UserForm

Using an External Control

Animating a Label

CHAPTER 15: Implementing Advanced UserForm Techniques

A Modeless Dialog Box

Displaying a Progress Indicator

Creating Wizards

Emulating the MsgBox Function

A UserForm with Movable Controls

A UserForm with No Title Bar

Simulating a Toolbar with a UserForm

Emulating a Task Pane with a UserForm

A Resizable UserForm

Handling Multiple UserForm Controls with One Event Handler

Selecting a Color in a UserForm

Displaying a Chart in a UserForm

Making a UserForm Semitransparent

A Puzzle on a UserForm

Video Poker on a UserForm

Part IV: Developing Excel Applications

CHAPTER 16: Creating and Using Add-Ins

What Is an Add-In?

Understanding Excel's Add-in Manager

Creating an Add-In

An Add-In Example

Comparing XLAM and XLSM Files

Manipulating Add-Ins with VBA

Optimizing the Performance of Add-Ins

Special Problems with Add-Ins

CHAPTER 17: Working with the Ribbon

Ribbon Basics

Customizing the Ribbon

Creating a Custom Ribbon

Using VBA with the Ribbon

Creating an Old-Style Toolbar

CHAPTER 18: Working with Shortcut Menus

CommandBar Overview

Referring to Controls in a CommandBar

Properties of CommandBar Controls

Displaying All Shortcut Menu Items

Using VBA to Customize Shortcut Menus

Resetting a Shortcut Menu

Shortcut Menus and Events

CHAPTER 19: Providing Help for Your Applications

Help for Your Excel Applications

Help Systems That Use Excel Components

Displaying Help in a Web Browser

Using the HTML Help System

CHAPTER 20: Leveraging Class Modules

What Is a Class Module?

Creating a NumLock Class

Coding Properties, Methods, and Events

Exposing a QueryTable Event

Creating a Class to Hold Classes

CHAPTER 21: Understanding Compatibility Issues

What Is Compatibility?

Types of Compatibility Problems

Avoid Using New Features

But Will It Work on a Mac?

Dealing with 64-Bit Excel

Creating an International Application

Multilanguage Applications

VBA Language Considerations

Using Local Properties

Identifying System Settings

Date and Time Settings

Part V: Appendix: VBA Statements and Functions Reference

APPENDIX: VBA Statements and Functions Reference

VBA Statements

Functions

Index

End User License Agreement

List of Tables

Chapter 1

TABLE 1.1 ActiveX Controls versus Form Controls

Chapter 2

TABLE 2.1 Some Useful Properties of the Application Object

Chapter 3

TABLE 3.1 VBA Built-in Data Types

TABLE 3.2 Variable Scope

TABLE 3.3 Operator Precedence

TABLE 3.4 VBA Logical Operators

Chapter 5

TABLE 5.1 Function Categories

Chapter 6

TABLE 6.1 Commonly Used Workbook Events

TABLE 6.2 Worksheet Events

TABLE 6.3 Commonly Used Events Recognized by the Application Object

TABLE 6.4 Key Codes for the OnKey Event

Chapter 9

TABLE 9.1 Events Recognized by the Chart Object

Chapter 11

TABLE 11.1 VBA File-Related Statements

TABLE 11.2 File Attribute Constants for the Dir Function

Chapter 12

TABLE 12.1 Codes to Determine the Data Type Returned by Excel's InputBox Method...

TABLE 12.2 Constants Used for Buttons in the MsgBox Function

TABLE 12.3 Constants Used for MsgBox Return Value

Chapter 13

TABLE 13.1 SpinButton Events

Chapter 21

TABLE 21.1 Excel Country Codes

TABLE 21.2 Properties That Have Local Versions

TABLE 21.3 Constants for the International Property

Appendix

TABLE A.1 Summary of VBA Statements

TABLE A.2 Summary of VBA Functions

List of Illustrations

Chapter 1

FIGURE 1.1 A customized shortcut menu

FIGURE 1.2 A dialog box created with Excel's UserForm feature

FIGURE 1.3 You can add UserForm controls to worksheets and link them to cells...

FIGURE 1.4 Using the Ribbon to add controls to a worksheet

FIGURE 1.5 Using the Protect Sheet dialog box to specify what users can and c...

Chapter 2

FIGURE 2.1 The Record Macro dialog box

FIGURE 2.2 Your pretotaled worksheet containing two tables

FIGURE 2.3 Your post-totaled worksheet

FIGURE 2.4 The Excel Macro dialog box

FIGURE 2.5 Recording a macro with relative references

FIGURE 2.6 The Trusted Locations tab allows you to add directories that are c...

FIGURE 2.7 You can find the form controls on the Developer tab.

FIGURE 2.8 Assign a macro to the newly added button.

FIGURE 2.9 Adding a macro to the Quick Access toolbar

FIGURE 2.10 The VBE with significant elements identified

FIGURE 2.11 This Project window lists two projects. They are expanded to show...

FIGURE 2.12 Code modules are visible in the Project window in a folder called...

FIGURE 2.13 The Editor tab in the Options dialog box

FIGURE 2.14 Change the VBE's looks with the Editor Format tab.

FIGURE 2.15 The General tab of the Options dialog box

FIGURE 2.16 The Docking tab of the Options dialog box

FIGURE 2.17 The Object Browser is a great reference source.

Chapter 3

FIGURE 3.1 VBA's way of telling you that your procedure contains an undeclare...

FIGURE 3.2 VBA displays a list of constants that you can assign to a property...

FIGURE 3.3 Displaying a list of VBA functions in VBE

Chapter 4

FIGURE 4.1 The Macro dialog box

FIGURE 4.2 The Macro Options dialog box lets you assign a Ctrl key shortcut a...

FIGURE 4.3 The References dialog box lets you establish a reference to anothe...

FIGURE 4.4 Assigning a macro to a button

FIGURE 4.5 Executing a procedure by entering its name in the Immediate window...

FIGURE 4.6 VBA error messages aren't always user friendly.

FIGURE 4.7 You can create a message box to display the error code and descrip...

FIGURE 4.8 The SpecialCells method generates this error if no cells are found...

FIGURE 4.9 Using the VBE Immediate window to test a statement

FIGURE 4.10 An empty procedure in a module located in the Personal Macro Work...

FIGURE 4.11 Using a temporary procedure to test the BubbleSort code

FIGURE 4.12 This message box tells the user that the sheets cannot be sorted....

FIGURE 4.13 This message box appears before the sheets are sorted.

FIGURE 4.14 Adding a new command to the Ribbon

Chapter 5

FIGURE 5.1 Using a custom function in a worksheet formula

FIGURE 5.2 Using a custom function in a VBA procedure

FIGURE 5.3 Using a custom VBA function for conditional formatting

FIGURE 5.4 Calling a Function procedure from the Immediate window

FIGURE 5.5 Using a function to display the result of a calculation

FIGURE 5.6 Different ways of passing an array or a single value to a workshee...

FIGURE 5.7 Comparing SUM with MYSUM

FIGURE 5.8 The Extended Date functions used in formulas

FIGURE 5.9 Use the Immediate window to display results while a function is ru...

FIGURE 5.10 The Insert Function and Function Arguments dialog boxes for a cus...

FIGURE 5.11 Using Windows API functions to determine which keys were pressed ...

Chapter 6

FIGURE 6.1 The components for each VBA project are listed in the Project wind...

FIGURE 6.2 The best way to create an event procedure is to let the VBE do it ...

FIGURE 6.3 This message box was triggered by a SheetActivate event.

FIGURE 6.4 Clicking No cancels the print operation by changing the Cancel arg...

FIGURE 6.5 When this message appears, Workbook_BeforeClose has already done i...

FIGURE 6.6 A message displayed by the Workbook_BeforeClose event procedure

FIGURE 6.7 This message box describes the problem when the user makes an inva...

FIGURE 6.8 The Worksheet_Change procedure ensures that data validation isn't ...

FIGURE 6.9 Moving the cell cursor shades the active cell's row and column.

FIGURE 6.10 This workbook uses a class module to monitor all Application-leve...

FIGURE 6.11 This message box was programmed to display at a particular time o...

FIGURE 6.12 Pressing Shift+F10 displays this message.

Chapter 7

FIGURE 7.1 The number of rows in the data range changes every week.

FIGURE 7.2 This workbook uses a custom shortcut menu to demonstrate how to se...

FIGURE 7.3 The InputBox function gets a value from the user to be inserted in...

FIGURE 7.4 Validate a user's entry with the VBA InputBox function.

FIGURE 7.5 A macro for inserting data into the next empty row in a worksheet...

FIGURE 7.6 Use an input box to pause a macro.

FIGURE 7.7 A VBA procedure analyzes the currently selected range.

FIGURE 7.8 Using the intersection of the used range and the selected ranged r...

FIGURE 7.9 The goal is to duplicate rows based on the value in column B.

FIGURE 7.10 New rows were added, according to the value in column B.

FIGURE 7.11 Using a function to determine the type of data in a cell

FIGURE 7.12 Displaying the time to write to a range and read from a range, us...

FIGURE 7.13 Using Excel's InputBox method to prompt for a cell location

FIGURE 7.14 All rows and columns are hidden, except for a range (G7:L19).

FIGURE 7.15 Hyperlinks to each worksheet, created by a macro

FIGURE 7.16 A message box displaying the date and time

FIGURE 7.17 Using a function to display time differences in a friendly manner...

FIGURE 7.18 Listing font names in the actual fonts

FIGURE 7.19 Comparing the time required to perform sorts of various array siz...

FIGURE 7.20 Examples of the SPELLDOLLARS function

FIGURE 7.21 Determining the path and name of the application associated with ...

Chapter 8

FIGURE 8.1 This table is a good candidate for a pivot table.

FIGURE 8.2 A pivot table created from the data in Figure 8.1

FIGURE 8.3 The data in this workbook will be summarized in a pivot table.

FIGURE 8.4 A pivot table created from the budget data

FIGURE 8.5 The Pivot Table Fields task pane

FIGURE 8.6 Several pivot tables created by a VBA procedure

FIGURE 8.7 The summary table on the left will be converted to the table on th...

FIGURE 8.8 This dialog box asks the user for the ranges.

Chapter 9

FIGURE 9.1 These charts use different formatting.

FIGURE 9.2 A simple macro applied consistent formatting to the four charts.

FIGURE 9.3 Each row of data will be used to create a chart.

FIGURE 9.4 A sampling of the 50 charts created by the macro

FIGURE 9.5 This chart always displays the data from the row of the active cel...

FIGURE 9.6 Data labels from an arbitrary range show the percent change for ea...

FIGURE 9.7 Data labels created from a range of data are not compatible with v...

FIGURE 9.8 An XY chart that would benefit by having data labels

FIGURE 9.9 This XY chart has data labels, thanks to a VBA procedure.

FIGURE 9.10 A chart within a UserForm

FIGURE 9.11 Selecting an event in the code module for a Chart object

FIGURE 9.12 This chart serves as a clickable image map.

FIGURE 9.13 After converting a chart to a picture, you can manipulate it by u...

FIGURE 9.14 A text box displays information about the data point under the mo...

FIGURE 9.15 Range B7:C9 contains data point information that's displayed in t...

FIGURE 9.16 An example of a scrollable chart

FIGURE 9.17 Sparkline examples

FIGURE 9.18 The result of running the

SparklineReport

procedure

Chapter 10

FIGURE 10.1 Add a reference to the object library for the application that yo...

Chapter 11

FIGURE 11.1 Starting a Power Query web query

FIGURE 11.2 Enter the target URL containing the data you need.

FIGURE 11.3 Select the correct data source and then click the Edit button.

FIGURE 11.4 The Power Query Editor window allows you to shape, clean, and tra...

FIGURE 11.5 Select the columns that you want to keep and then select Remove Ot...

FIGURE 11.6 You can click the Column Actions icon to select actions (such as R...

FIGURE 11.7 The Power Query Editor can be used to apply transformation actions...

FIGURE 11.8 The Import Data dialog box gives you more control over how the re...

FIGURE 11.9 Your final query pulled from the Internet: transformed, put into ...

FIGURE 11.10 Query steps can be viewed and managed in the Applied Steps secti...

FIGURE 11.11 The Advanced Editor window

FIGURE 11.12 Designate a cell that will trap the criteria selection.

FIGURE 11.13 Select the latest version of the Microsoft ActiveX Data Objects ...

Chapter 12

FIGURE 12.1 The VBA InputBox function at work

FIGURE 12.2 Using the VBA InputBox function with a long prompt

FIGURE 12.3 Using the InputBox method to specify a range

FIGURE 12.4 Excel's InputBox method performs validation automatically

FIGURE 12.5 Another example of validating an entry in Excel's InputBox

FIGURE 12.6 The button argument of the MsgBox function determines which buttons...

FIGURE 12.7 Displaying lengthy text in a message box

FIGURE 12.8 This message box displays text with tabs and line breaks.

FIGURE 12.9 The GetOpenFilename method displays a dialog box used to specify a ...

FIGURE 12.10 This dialog box was displayed with a VBA statement.

FIGURE 12.11 Using the Customize Ribbon panel to identify a command name

FIGURE 12.12 Some users prefer to use Excel's built-in data form for data-entry...

Chapter 13

FIGURE 13.1 The Properties window for an empty UserForm

FIGURE 13.2 Use the Toolbox to add controls to a UserForm.

FIGURE 13.3 This UserForm displays all of the controls.

FIGURE 13.4 Use the Format ➪ Align command to change the alignment of controls....

FIGURE 13.5 The OptionButton controls, aligned and evenly spaced

FIGURE 13.6 The Properties window for an OptionButton control

FIGURE 13.7 Use the Tab Order dialog box to specify the tab order of the contro...

FIGURE 13.8 This dialog box asks the user to enter a name and a gender.

FIGURE 13.9 The CommandButton1_Click procedure is executed when the button on t...

FIGURE 13.10 The CommandButton's Click event procedure displays the...

FIGURE 13.11 The event list for a CheckBox control

FIGURE 13.12 This SpinButton is paired with a TextBox.

FIGURE 13.13 The Toolbox, with a new page of controls

FIGURE 13.14 The Additional Controls dialog box lets you add other ActiveX cont...

Chapter 14

FIGURE 14.1 This dialog box uses command buttons as a menu.

FIGURE 14.2 This dialog box uses a list box as a menu.

FIGURE 14.3 The RefEdit control allows the user to select a range.

FIGURE 14.4 This splash screen is displayed briefly when the workbook is opened...

FIGURE 14.5 A dialog box before and after displaying options

FIGURE 14.6 Here, scroll bars allow zooming and scrolling of the worksheet.

FIGURE 14.7 Setting the RowSource property at design time

FIGURE 14.8 A

Collection

object is used to fill a list box with the unique item...

FIGURE 14.9 This message box displays a list of items selected in a list box.

FIGURE 14.10 The contents of this list box depend on the option button selected...

FIGURE 14.11 Building a list from another list

FIGURE 14.12 The buttons allow the user to move items up or down in the ListBox...

FIGURE 14.13 This ListBox displays a three-column list with column headers.

FIGURE 14.14 A two-column ListBox filled with data stored in an array

FIGURE 14.15 This list box makes selecting rows in a worksheet easy.

FIGURE 14.16 This dialog box lets the user activate a sheet.

FIGURE 14.17 Use a text box to filter a list box.

FIGURE 14.18 MultiPage groups your controls on pages, making them accessible fr...

FIGURE 14.19 The Windows Media Player control in a UserForm

FIGURE 14.20 The Windows Media Player control

FIGURE 14.21 Generating a random number

FIGURE 14.22 A random number has been chosen.

Chapter 15

FIGURE 15.1 This modeless dialog box remains visible while the user continues...

FIGURE 15.2 This modeless UserForm displays various pieces of information abo...

FIGURE 15.3 A UserForm displays the progress of a macro.

FIGURE 15.4 This UserForm will serve as a progress indicator.

FIGURE 15.5 The user specifies the number of rows and columns for the random ...

FIGURE 15.6 Page2 of the MultiPage control will display the progress indicato...

FIGURE 15.7 The progress indicator will be hidden by reducing the height of t...

FIGURE 15.8 The progress indicator in action

FIGURE 15.9 The steps are listed in a ListBox control.

FIGURE 15.10 Files are added to the list to show progress.

FIGURE 15.11 This four-step wizard uses a MultiPage control.

FIGURE 15.12 Clicking the Cancel button displays a confirmation message box....

FIGURE 15.13 The result of the MsgBox emulation function

FIGURE 15.14 The UserForm for the MyMsgBox function

FIGURE 15.15 You can drag and rearrange the three Image controls by using the...

FIGURE 15.16 This UserForm lacks a title bar.

FIGURE 15.17 Another UserForm without a title bar

FIGURE 15.18 A UserForm set up to function as a toolbar

FIGURE 15.19 The UserForm that simulates a toolbar

FIGURE 15.20 A UserForm designed to look like a task pane

FIGURE 15.21 This UserForm is resizable.

FIGURE 15.22 The UserForm after it was increased

FIGURE 15.23 The VBA code converts Label control movements into new Width and...

FIGURE 15.24 Multiple command buttons with a single event-handler procedure

FIGURE 15.25 The ButtonGroup_Click procedure describes the button that was cl...

FIGURE 15.26 This dialog box lets the user select a color by specifying the r...

FIGURE 15.27 The user's scroll bar values are stored in the Windows Registry ...

FIGURE 15.28 A UserForm can display "live" charts.

FIGURE 15.29 A semitransparent UserForm

FIGURE 15.30 Creating a light-box effect in Excel

FIGURE 15.31 A sliding tile puzzle in a UserForm

FIGURE 15.32 A feature-packed video poker game

Chapter 16

FIGURE 16.1 Excel warns you if an add-in uses a nonstandard file extension.

FIGURE 16.2 These settings affect whether add-ins can be used.

FIGURE 16.3 The Add-ins dialog box

FIGURE 16.4 The Export Charts workbook will make a useful add-in.

FIGURE 16.5 The Add-ins dialog box with the new add-in selected

FIGURE 16.6 Making an add-in not an add-in

FIGURE 16.7 One way to remove a member of the AddIns collection

FIGURE 16.8 A table that lists information about all members of the

AddIns

co...

FIGURE 16.9 When attempting to open the add-in incorrectly, the user sees thi...

Chapter 17

FIGURE 17.1 The Page Layout tab contains many different control types.

FIGURE 17.2 The Customize Ribbon tab allows you to add macros to the Ribbon....

FIGURE 17.3 The Rename dialog lets you choose an icon for your Ribbon button....

FIGURE 17.4 The custom Ribbon button executes the HelloWorld macro.

FIGURE 17.5 You can add a macro to the Quick Access toolbar.

FIGURE 17.6 The new QAT button executes your macro.

FIGURE 17.7 Excel can't find the macro associated with the Ribbon button.

FIGURE 17.8 XML to create two buttons in a custom group

FIGURE 17.9 The editor generates VBA code to use in your workbook.

FIGURE 17.10 Modify the callback procedures in the VBE.

FIGURE 17.11 Two new buttons added to the Data tab

FIGURE 17.12 This check box control is always in sync with the page break dis...

FIGURE 17.13 A new Ribbon tab with five groups of controls

FIGURE 17.14 A Ribbon group with two labels.

FIGURE 17.15 An editBox control in a custom Ribbon group

FIGURE 17.16 Three controls in a custom Ribbon group.

FIGURE 17.17 This group contains built-in controls.

FIGURE 17.18 This Ribbon group contains two galleries.

FIGURE 17.19 A gallery that displays month names, plus a button

FIGURE 17.20 A gallery of images

FIGURE 17.21 The dynamicMenu control lets you create a menu that varies depen...

FIGURE 17.22 Using the Customize Ribbon tab of the Excel Options dialog box t...

FIGURE 17.23 An old-style toolbar, located in the Custom Toolbars group of th...

Chapter 18

FIGURE 18.1 A simple macro generates a list of all shortcut menus.

FIGURE 18.2 Displaying the

Caption

property for controls

FIGURE 18.3 Listing the items in all shortcut menus

FIGURE 18.4 The Cell shortcut menu with a custom menu item

FIGURE 18.5 This shortcut menu has a submenu with three submenu items.

FIGURE 18.6 A new shortcut menu appears only when the user right-clicks a cel...

Chapter 19

FIGURE 19.1 Using cell comments to display help

FIGURE 19.2 Using a shape object with text to display help for the user

FIGURE 19.3 An easy method is to put user help in a separate worksheet.

FIGURE 19.4 Clicking one of the arrows on the SpinButton changes the text dis...

FIGURE 19.5 Inserting a Label control inside a Frame control adds scrolling t...

FIGURE 19.6 Using a drop-down list control to select a help topic

FIGURE 19.7 Displaying help in a web browser

FIGURE 19.8 Displaying an MHTML file in a web browser

FIGURE 19.9 An example of HTML Help

FIGURE 19.10 Using HTML Help Workshop to create a help file

FIGURE 19.11 Specify a context ID for a custom function.

Chapter 20

FIGURE 20.1 An empty class module named CNumLock

FIGURE 20.2 A message box shows the change in status of the Num Lock key.

FIGURE 20.3 A web query for financial information

FIGURE 20.4 The code pane lists available events.

FIGURE 20.5 After a web query is refreshed, the last update time is recor...

FIGURE 20.6 Excel tables hold the information for the objects.

FIGURE 20.7 The commission calculation is output to the Immediate Window.

Chapter 21

FIGURE 21.1 Compatibility Checker

FIGURE 21.2 A summary report from Microsoft Office Code Compatibility Inspect...

FIGURE 21.3 The wizard demo in English, Spanish, and German

Guide

Cover

Table of Contents

Begin Reading

Pages

iii

iv

v

vii

ix

xi

xxxiii

xxxiv

xxxv

xxxvi

xxxvii

xxxviii

xxxix

1

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

287

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

713

715

716

717

718

719

720

721

722

723

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

Excel® 2019 Power Programming with VBA

Michael Alexander

Dick Kusleika

Previously by John Walkenbach

Part IIntroduction to Excel VBA

IN THIS PART

Chapter 1

Essentials of Spreadsheet Application Development

Chapter 2

Introducing Visual Basic for Applications

Chapter 3

VBA Programming Fundamentals

Chapter 4

Working with VBA Sub Procedures

Chapter 5

Creating Function Procedures

Chapter 6

Understanding Excel's Events

Chapter 7

VBA Programming Examples and Techniques

CHAPTER 1Essentials of Spreadsheet Application Development

IN THIS CHAPTER

Discovering the basic steps involved in spreadsheet application development

Determining end users' needs

Planning applications to meet users' needs

Developing and testing your applications

Documenting your development efforts and writing user documentation

What Is a Spreadsheet Application?

For the purposes of this book, a spreadsheet application is a spreadsheet file (or group of related files) that is designed so that someone other than the developer can perform specific tasks without extensive training. According to this definition, most of the spreadsheet files that you've developed probably don't qualify as spreadsheet applications. You may have dozens or hundreds of spreadsheet files on your hard drive, but it's a safe bet that most of them aren't designed for others to use.

A good spreadsheet application does the following:

Enables the end user to perform a task that he or she probably would not be able to do otherwise.

Provides the appropriate solution to the problem. (A spreadsheet environment isn't always the optimal approach.)

Accomplishes what it is supposed to do. This prerequisite may be obvious, but it's not at all uncommon for applications to fail this test.

Produces accurate results and is free of bugs.

Uses appropriate and efficient methods and algorithms to accomplish its job.

Traps errors before the user is forced to deal with them.

Does not allow the user to delete or modify important components accidentally (or intentionally).

Has a clear and consistent user interface so that the user always knows how to proceed.

Has well-documented formulas, macros, and user interface elements that allow for subsequent changes, if necessary.

Is designed so that it can be modified in simple ways without making major changes. A basic fact is that a user's needs change over time.

Has an easily accessible help system that provides useful information on at least the major procedures.

Is designed to be portable and to run on any system that has the proper software (in this case, a copy of a supported version of Excel).

It should come as no surprise that it is possible to create spreadsheet applications for many different usage levels, ranging from a simple fill-in-the-blank template to an extremely complex application that uses a custom interface and may not even look like a spreadsheet.

Steps for Application Development

There is no simple, surefire recipe for developing an effective spreadsheet application. Everyone has his or her own style for creating such applications. In addition, every project is different and therefore requires its own approach. Finally, the demands and technical expertise of the people with whom (or for whom) you work also play a role in how the development process proceeds.

Spreadsheet developers typically perform the following activities:

Determine the needs of the user(s)

Plan an application that meets these needs

Determine the most appropriate user interface

Create the spreadsheet, formulas, macros, and user interface

Test and debug the application

Attempt to make the application bulletproof

Make the application aesthetically appealing and intuitive

Document the development effort

Develop user documentation and Help systems

Distribute the application to the user

Update the application when necessary

Not all of these steps are required for each application, and the order in which these activities are performed varies from project to project. We describe each of these activities in the pages that follow. For most of these items, we cover the technical details in subsequent chapters.

Determining User Needs

When you undertake a new Excel project, one of your first steps is to identify exactly what the end users require. Failure to assess the end users' needs thoroughly early on often results in additional work later when you have to adjust the application so that it does what it was supposed to do in the first place.

In some cases, you'll be intimately familiar with the end users—you may even be an end user yourself. In other cases (for example, if you're a consultant developing a project for a new client), you may know little or nothing about the users or their situations.

How do you determine the needs of the user? If you've been asked to develop a spreadsheet application, it's a good idea to meet with the end users and ask specific questions. Better yet, get everything in writing, create flow diagrams, pay attention to minor details, and do anything else to ensure that the product you deliver is the product that is needed.

Here are some guidelines that may help make this phase easier:

Don't assume that you know what the user needs. Second-guessing at this stage almost always causes problems later.

If possible, talk directly to the end users of the application, not just their supervisor or manager.

Learn what, if anything, is currently being done to meet the users' needs. You might be able to save some work by simply adapting an existing application. At the very least, looking at current solutions will familiarize you with the operation.

Identify the resources available at the users' site. For example, try to determine whether you must work around any hardware or software limitations.

If possible, determine the specific hardware systems that will be used. If your application will be used on slow systems, you need to take that into account.

Identify which versions of Excel are in use. Keep in mind that users can have versions of Excel running on macOS, mobile platforms, and Windows. These have to be taken into account when planning an automated Excel solution. Although Microsoft does everything in its power to urge users to upgrade to the latest version of the software, the majority of Excel users don't.

Understand the skill levels of the end users. This information will help you design the application appropriately.

Determine how long the application will be used and whether any changes are anticipated during the lifetime of the project. Knowing this information may influence the amount of effort that you put into the project and help you plan for changes.

Finally, don't be surprised if the project specifications change before you complete the application. This occurrence is common, and you're in a better position if you expect changes rather than being surprised by them. Just make sure that your contract (if you have one) addresses the issue of changing specifications.

Planning an Application That Meets User Needs

After you determine the end users' needs, it's tempting to jump right in and start fiddling around in Excel. Take it from those who suffer from this problem: try to restrain yourself. Builders don't construct a house without a set of blueprints, and you shouldn't build a spreadsheet application without some type of plan. The formality of your plan depends on the scope of the project and your general style of working, but you should spend at least some time thinking about what you're going to do and coming up with a plan of action.

Before rolling up your sleeves and settling down at your keyboard, you'll benefit by taking some time to consider the various ways that you can approach the problem. This planning period is where a thorough knowledge of Excel pays off. Avoiding blind alleys rather than stumbling into them is always a good idea.

If you ask a dozen Excel experts to design an application based on precise specifications, chances are that you'll get a dozen different implementations of the project that meet those specifications. Of those solutions, some will be better than the others because Excel often provides several options to accomplish a task. If you know Excel inside and out, you'll have a good idea of the potential methods at your disposal, and you can choose the one most appropriate for the project at hand. Often, a bit of creative thinking yields an unusual approach that's vastly superior to other methods.

Consider some general options at the beginning stage of this planning period, such as the following:

File structure

Think about whether you want to use one workbook with multiple sheets, several single-sheet workbooks, or a template file.

Data structure

You should always consider how your data will be structured and also determine whether you will be using external database files, data sources stored on the cloud, or storing everything in worksheets.

Add-in or workbook file

In some cases, an add-in may be the best choice for your final product, or perhaps you might use an add-in with a standard workbook.

Version of Excel

Will your Excel application be used with Excel 2019 only, or will your application also need to run on earlier versions of Excel? What about versions of Excel running on other platforms, such as macOS or mobile devices? These considerations are important because each new version of Excel adds features that aren't available in previous versions.

Error handling

Error handling is a major issue with applications. You need to determine how your application will detect and deal with errors. For example, if your application performs pivot table operations on the active sheet, you need to be able to handle a case in which a pivot table does not exist on the sheet that is active.

Use of special features

If your application needs to summarize a lot of data, you may want to consider using Excel's pivot table feature, or you may want to use Excel's data validation feature as a check for valid data entry.

Performance issues

The time to start thinking about increasing the speed and efficiency of your application is in the development stage, not when the application is complete and users are complaining.

Level of security

As you may know, Excel provides several protection options to restrict access to particular elements of a workbook. For example, you can lock cells so that formulas cannot be changed, and you can assign a password to prevent unauthorized users from viewing or accessing specific files. Determining up front exactly what you need to protect—and what level of protection is necessary—will make your job easier.

NOTE

Be aware that Excel's protection features aren't 100 percent effective—far from it. If you desire complete and absolute security for your application, Excel probably isn't the best platform.