Excel VBA Programming For Dummies - Dick Kusleika

Скачать книгу

section of the Trust Center dialog box is in view and review the Help screen that appears.

      Employing VBA with Excel

      See how to access the important parts of the Visual Basic Editor.

      Discover VBA code modules (where you store your VBA code).

      Obtain an overview of the Excel object model.

      Get a crash course in using the Excel macro recorder.

      Working in the Visual Basic Editor

      IN THIS CHAPTER

      

Accessing the Visual Basic Editor

      

Discovering the Visual Basic Editor parts

      

Knowing what goes into a VBA module

      

Getting VBA code into a module

      

Customizing the VBA environment

      The Visual Basic Editor (VBE) is the main tool for programming in VBA. It’s where you view and edit recorded code and write code from scratch. In this chapter, you find out how to work with the VBE, and you get down to the nitty-gritty of writing some VBA code.

      The Visual Basic Editor (often referred to as the VBE) is a separate application where you write and edit your VBA macros. Beginning with Excel 2013, when you open multiple workbooks, each opens in a separate window. However, the VBE only ever has one window, and from this window you can write, edit, and view macros for any open workbook.

      

You can’t run the VBE separately; Excel must be running for the VBE to run.

      Activating the VBE

      The quickest way to activate the VBE is to press Alt+F11 from an Excel window. To return to Excel, press Alt+F11 again. Or you can simply click the Close button on the VBE’s title bar. When the VBE window closes, Excel becomes the active window.

      You can also activate the VBE by choosing Developer ⇒ Code ⇒ Visual Basic. If you don’t have a Developer tab at the top of your Excel window, refer to Chapter 2, which shows how to get that handy Developer tab to show up.

      Exploring VBE components

Snapshot of VBE is your customizable friend.

      FIGURE 3-1: The VBE is your customizable friend.

      Menu bar

      The VBE menu bar contains commands you can use to do things with the various components in the VBE. Many of the menu commands have shortcut keys associated with them.

      

The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands.

      Toolbar

      The Standard toolbar, which is directly below the menu bar by default (refer to Figure 3-1), is one of the four VBE toolbars. You can customize the toolbars, move them around, display other toolbars, and more. To do so, choose View ⇒ Toolbars, and customize as you see fit. Most people just leave them as they are.

      Project Explorer

      The Project Explorer displays a tree diagram that shows every workbook currently open in Excel (including add-ins and hidden workbooks). Double-click items to expand or contract them within the outline. See the upcoming “Working with the Project Explorer” section for more detail.

      If the Project Explorer is not visible, press Ctrl+R or choose View ⇒ Project Explorer. To hide the Project Explorer, click the Close button on its title bar. Or right-click anywhere in the Project Explorer and choose Hide from the shortcut menu.

      Code pane

      A Code pane is where you put your VBA code. Every object in a project has an associated Code pane. To view an object’s Code pane, double-click the object in the Project Explorer. For example, to view the Code pane for the Sheet1 object in Book1, double-click Sheet1 in the VBAProject for Book1. Unless you’ve added some VBA code, the Code pane is empty.

      You find out more about Code panes later in this chapter’s “Working with a Code Pane” section.

      Immediate window

      The Immediate window may or may not be visible. If it isn’t visible, press Ctrl+G or choose View ⇒ Immediate Window. To close the Immediate window, click the Close button on its title bar (or right-click anywhere in the Immediate window and choose Hide from the shortcut menu).

      GETTING HELP IN THE VBE

      Beginning with Excel 2013, all VBA help information is on the internet and is displayed in your web browser. In other words, you must be connected to the internet to access the Help system. You can, however, download your very own copy of the Help system from Microsoft’s site. Do a web search for download excel VBA documentation, and you’ll find it. Pick the link at microsoft.com and don’t worry if the version they have is a few versions older than yours — they don’t update it all that often.

      Chapter 13 covers the Immediate window in detail. It may just become your good friend!

      In the VBE, workbooks are called projects. Every workbook contains exactly one project,

Скачать книгу