Excel VBA 24-Hour Trainer. Tom Urtis

including VBA, are not warmly received by all workplace IT departments. Many companies have set internal policies that forbid employees from downloading malicious software onto workplace computers. This is an understandable concern, but the corporate safety nets are sometimes cast far and wide to include Excel workbooks with VBA code. The tug of war in companies between the security interests of IT and the work efficiency needs of management can determine whether the VBA code you install will actually be allowed for use in some company venues.

      • Finally, VBA is a large program. It has thousands of keywords and the language library is only getting larger. Actually, I see this as a good thing, because the more VBA you learn, the more productivity and control you will have with Excel. Just as with any language, be it spoken or programming, there is a level of rolling-up-your-shirtsleeves commitment that'll be needed to learn VBA. Even the longest journey starts with a first step, and this book gets you on your way.

      NOTE VBA has a bright, stable future. An occasional rumor makes the rounds on the Internet, claiming the imminent demise of VBA. Do not believe it. VBA is here to stay, and Microsoft has publicly said so, time and again. The facts are, in 2007, Microsoft closed its VBA licensing program to new customers, and VBA was not supported in the 2008 version of Office for the Mac, though VBA has been supported by Mac versions after that. Microsoft has consistently made very clear its plan for supporting VBA in future versions of Excel for Windows.

      Try It

      With the introductory nature of this first lesson, there's nothing specific to try with VBA. What you can do is to get a jump on the rest of the lessons in this book by making a list of some of your most frequent everyday manual Excel tasks, especially the dreaded, time-consuming ones you wish would go away. Tasks such as those will become good candidates for you to apply the VBA macros and automated solutions skills that the following lessons will teach you.

      REFERENCE There is no video to accompany this lesson.

Lesson 2

      Getting Started with Macros

      In Lesson 1, you read that VBA is the programming language of Microsoft Excel and that a macro is a sequence of VBA commands to run a task automatically instead of manually. In this lesson, you find out how to create a simple macro, what its code looks like, and a few options for how you can run the macro.

      Composing Your First Macro

      This lesson leads you through the process of composing a macro to sort and format a range of data. But even before the first line of programming code is written, you need to set up shop by giving yourself easy access to the VBA-related tools you'll be using. The following housekeeping items usually need to be done only once, and it's worth taking the time to do them now if you haven't already done so.

      Accessing the VBA Environment

      At the time of this writing, Excel is at a unique stage in its ongoing evolution because four of its versions are being used with significant popularity in the Microsoft Office suite of applications. Version 2003 (also known as version 11) was the final Excel version with the traditional menu bar interface of File, Edit, View, and so on. Then came version 2007 (also known as version 12), blazing the trail for Office's new Ribbon interface. Three years later, version 2010 (also known as version 14) was the next release from Redmond. Most recently, version 2013 (also known as version 15) has taken its place among the community of Excel versions that are being used around the world.

      As with other tasks you typically do in Excel, the actions you take to create, view, edit, or run VBA code usually start by clicking the on-screen icon relating to that task. Exactly what those VBA-related icons look like, and what you need to do to make them easily accessible to you, depends on the particular version of Excel you are working with.

      WHY IS THERE NO VERSION 13?

      You probably noticed that the version numbers went from 12 in 2007 to 14 in 2010, making the number 13 conspicuously absent as a version number. This was not an accident; Microsoft purposely skipped the number 13. You'll often notice in elevators of high-rise office buildings and hotels that the floor buttons go from 12 to 14, without a floor number 13. Microsoft recognizes that its Office applications are used globally, and in some cultures, 13 is thought to be an unlucky number. It made good business sense to avoid issues of possible reluctance from consumers upgrading to “Office 13,” or blame for inevitable version bugs by people who believe that 13 is an unlucky number.

      To save yourself time and extra mouse clicks, start by making sure that the VBA-related icons you'll be using most frequently are already displayed whenever you open Excel. The following steps are shown for each of today's four most popular versions.

Version 2003 continues to be used by a measurable percentage of individuals and employers worldwide. For versions of Excel up to and including 2003, from your worksheet menu, click ViewToolbarsVisual Basic, as shown in Figure 2.1. This displays the Visual Basic toolbar, as shown in Figure 2.2, which you can dock just as you do with your other toolbars.

Figure 2.1

Figure 2.2

      For versions of Excel after 2003 (that is, starting with Excel 2007), the Ribbon user interface has replaced the menu interface, resulting in a different look to the VBA-related icons and a different set of steps required to see them.

      In versions 2007, 2010, and 2013, these VBA icons are located on the Developer tab. By default, the Developer tab is not automatically displayed along with the other Ribbon tabs. You need to complete a set of one-time steps to show the Developer tab and to keep it visible whenever you open Excel. Although the steps to do this are easy, they are different for each version.

      In Excel 2007, do the following:

      1. Click the round Office button near the top-left corner of your screen.

2. Click the Excel Options button located at the bottom of that menu, as shown in Figure 2.3.

3. In the Excel Options dialog box, click the Popular item at the upper left, and select the Show Developer tab in the Ribbon option, as shown in Figure 2.4.

Figure 2.3

Figure 2.4

      In Excel versions 2010 and 2013, showing the Developer tab is a bit different. A new Ribbon tab named File has supplanted the Office button. Use the following steps to make the Developer tab visible:

1. Click the File tab and then click the Options button, as shown in Figure 2.5. The Options dialog box opens.

2. Click the Customize Ribbon item at the left, which displays two vertical lists, as shown in Figure 2.6. Notice that the list on the right has a drop-down menu above it called Customize the Ribbon.

      3. Select the Main Tabs item from the Customize the Ribbon drop-down.

4. In the list of Main Tabs, select Developer and click OK. You will see the Developer tab in your Ribbon, as shown in Figure 2.7.

Figure 2.5

Figure 2.6