Excel VBA 24-Hour Trainer. Tom Urtis

Чтение книги онлайн.

СКАЧАТЬ you might imagine, the list of advantages to using VBA could fill the capacity of your average flash drive. The point is, you are sure to have tasks in your everyday dealings with Excel that can be accomplished more quickly and efficiently with VBA, and this book shows you how.

      Liabilities of VBA

      Although VBA is a tremendously useful and versatile tool, it is not a 100 percent perfect programming language – but then, no programming language anywhere can truthfully claim infallibility. The pros of VBA far outweigh its cons, but learning and using VBA does come with a few objective caveats that you should be aware of:

      • With each version release of Excel, Microsoft may add new VBA commands or stop supporting existing VBA commands, sometimes without advance warning. Surprises do happen, as was especially the case when Office 2007 was released with all its added features. Such is life in the world of Excel VBA. You will probably learn of coding errors from people who have upgraded to a newer version and are using the workbook you created in an earlier version.

      • VBA does not run uniformly in all computer operating environments. Sometimes, no matter how extensively you test your code and how flawlessly the macros run on your computer as you develop a project, there will be users of your workbook who will eventually report an error in your code. It won't be your fault or VBA's fault, it's just the idiosyncrasies of how programming languages such as VBA mix with various operating systems, Office versions, and network configurations. Debugging your code is the subject of Lesson 20.

      • Programming languages, 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. СКАЧАТЬ