Michael Alexander - Excel 2019 Power Programming with VBA

We briefly discuss language issues in Chapter 21.

Although system speed and processing power has become less of an issue on modern PCs and devices, testing your application for performance and speed is still a recommended best practice. A procedure that executes almost instantaneously on your system may take several seconds to execute on another system. In the world of computers, several seconds may be unacceptable.

When you gain more experience with VBA, you'll discover that there are ways to get the job done and there are ways to get the job done fast . It's a good idea to get into the habit of coding for speed. Other chapters in this book can certainly help you out in this area.

As you probably know, users' video displays vary widely. Higher-resolution displays and even dual displays are becoming increasingly common. Just because you have a super-high-resolution monitor, you can't assume that everyone else does.

Video resolution can be a problem if your application relies on specific information being displayed on a single screen. For example, if you develop an input screen that fills the screen in 1280 × 1024 mode, users with a 1024 × 768 display won't be able to see the whole input screen without scrolling or zooming.

Also, it's important to realize that a restored (that is, not maximized or minimized) workbook is displayed at its previous window size and position. In the extreme case, it's possible that a window saved by using a high-resolution display may be completely off the screen when opened on a system running in a lower resolution.

Unfortunately, you can't automatically scale things so that they look the same regardless of the display resolution. In some cases, you can zoom the worksheet (using the Zoom control in the status bar), but doing so reliably may be difficult. Unless you're certain about the video resolution that the users of your application will use, you should probably design your application so that it works with the lowest common denominator—800 × 600 or 1024 × 768 mode.

As you will discover later in the book, you can determine the user's video resolution by using Windows API calls from VBA. In some cases, you may want to adjust things programmatically, depending on the user's video resolution.

Using Excel's macro recorder

Working with the Visual Basic Editor

Understanding the Excel Object Model

Diving into the Range object

Knowing where to turn for help

A macro is essentially Visual Basic for Applications (VBA) code that you can call to execute any number of actions. In Excel, macros can be written or recorded.

Excel programming terminology can be a bit confusing. A recorded macro is technically no different from a VBA procedure that you create manually. The terms macro and VBA procedure are often used interchangeably. Many Excel users call any VBA procedure a macro. However, when most people think of macros, they think of recorded macros.

Recording a macro is like programming a phone number into your smartphone. First you manually enter and save a number. Then when you want, you can redial the number with the touch of a button. Just like on a smartphone, you can record your actions in Excel while you perform them. While you record, Excel gets busy in the background, translating and storing your keystrokes and mouse clicks to VBA code. After a macro is recorded, you can play back those actions any time you want.

The absolute best way to become familiar with VBA, without question, is simply to turn on the macro recorder and record some of the actions that you perform in Excel. This approach is a quick way to learn the relevant VBA syntax for a task.

In this section, you'll explore macros and learn how you can use the macro recorder to start familiarizing yourself with VBA.

To start recording your first macro, you first need to find the macro recorder, which is on the Developer tab. Unfortunately, Excel comes out of the box with the Developer tab hidden—you may not see it on your version of Excel at first. To display this tab, follow these steps:

1 Choose File ➪ Excel Options.

2 In the Excel Options dialog box, select Customize Ribbon.

3 In the list box on the right, place a check mark next to Developer.

4 Click OK to return to Excel.

Now that you have the Developer tab showing in the Excel Ribbon, you can start up the macro recorder by selecting the Record Macro command found in the Code group on the Developer tab. This activates the Record Macro dialog box, as shown in Figure 2.1.

FIGURE 2.1 The Record Macro dialog box

Note that you can also get to the Macro Recorder by selecting View ➪ Macros ➪ Macros ➪ Record Macros. However, if you plan to work with VBA macros, you'll want to make sure that the Developer tab is visible in order to gain access to the full gamut of developer features.

Here are the four parts of the Record Macro dialog box:

Macro Name This should be self-explanatory. Excel gives a default name to your macro, such as Macro1, but you should give your macro a name more descriptive of what it actually does. For example, you might name a macro that formats a generic table as FormatTable.

Shortcut Key Every macro needs an event, or something to happen, for it to run. This event can be a button press, a workbook opening, or, in this case, a keystroke combination. When you assign a shortcut key to your macro, entering that combination of keys triggers your macro to run. This is an optional field.

Store Macro In This Workbook is the default option. Storing your macro in This Workbook simply means that the macro is stored along with the active Excel file. The next time you open that particular workbook, the macro is available to run. Similarly, if you send the workbook to another user, that user can run the macro as well (provided the macro security is properly set by your user—more on that later in this chapter).

Description This is an optional field, but it can come in handy if you have numerous macros in a workbook or if you need to give a user a more detailed description about what the macro does. The description is also useful for distinguishing one macro from another when you have multiple workbooks open or you have macros stored in the Personal Macro Workbook.

With the Record Macro dialog box open, follow these steps to create a simple macro that enters your name into a worksheet cell:

1 Enter a new single-word name for the macro to replace the default Macro1 name. A good name for this example is MyName.

2 Assign the shortcut key Ctrl+Shift+N to this macro by entering uppercase N in the edit box labeled Shortcut Key.

3 Click OK to close the Record Macro dialog box and begin recording your actions.

4 Type your name into the active cell and press Enter.

5 Choose Developer ➪ Code ➪ Stop Recording. Alternatively, you can click the Stop Recording icon in the status bar (the square icon on left side of the status bar).