Maximizing the Power of Excel

[Pages:34]Maximizing the Power of

Excel?

With Macros and Modules

? SkillPath a division of the Graceland College Center for Professional Development and Lifelong Learning, Inc. All rights reserved, including the right to reproduce this material or any part thereof in any manner.

MAXIMIZING THE POWER OF EXCEL? WITH MACROS AND MODULES

Introduction

If you've done a lot of work in Excel?, you've probably heard about macros, modules or VBA. Learning how to use them will help you do your work much faster!

Macros, modules and VBA are the ingredients used to create custom Excel? commands and shortcuts.

Maximizing the Power of Excel? With Macros and Modules will show you how to automate most of the work you perform manually and save you tons of time on documents and reports you use regularly.

You could learn by trial and error, but that can be a slow, arduous process. In this session, you'll learn steps for creating, using and programming macros.

Course Overview

Often, we inherit macro-enabled workbooks that contain "secret codes" that cause the workbook to perform commands outside the realm of built-in Excel? commands.

In this session, you'll learn the essentials of working with macros and modules--building blocks of custom Excel? features for you and co-workers.

When macros work well, you're happy. When they stop working, you're perplexed and frustrated. In this lesson, you'll also earn how to troubleshoot and debug those pesky macros.

This workbook contains many images of user interface elements that you'll work with while performing tasks in Excel? on a Windows computer. Depending on your screen resolution or program window width, the Excel? ribbon on your screen might look different from that shown in this workbook. (If you turn on Touch mode, the ribbon

displays significantly fewer commands than in Mouse mode.)

As a result, SkillSteps that involve the ribbon might require a bit of adaptation.

If differences between your display settings and ours cause a button to appear differently on your screen than it does in this workbook's illustration, you can easily adapt the steps to locate the command.

Learning Objectives

? Macros ... explained in plain English ? Understand modules and their unique role

in programming ? How to create loops to repeat commands

multiple times ? Learn when to use macros and when to use VBA

(Visual Basic? for Applications)

Notes to Readers

Throughout this workbook you'll see study aids that will help you master Microsoft? Excel?. ? SkillSteps: The fundamental steps to get to

a feature ? SkillTips: Special guidelines for becoming a

power user ? Strategies: Techniques for mastering

Microsoft? Excel?

V536_101618

2

PARTICIPANT NOTEBOOK

Macros ... Explained in Plain English

A macro is a pre-written or recorded sequence of commands that can be replayed or triggered to issue those commands as needed. While Excel? comes with dozens of built-in commands that are triggered by buttons on a ribbon, there may be times when the command you need is too specific to be part of the program's standard collection. For that reason, you need to know the Excel? Visual Basic? for Applications programming environment so that you can: ? Understand and repair macros that are built on VBA code ? Build your own VBA commands for Excel? from scratch

A Quick Demonstration: What Macros Can Do

Macros provide custom commands that do not exist in Excel?. For example, if you wanted to provide co-workers with custom buttons to filter lists--you could do that with macros attached to those graphic objects.

True, a user could filter lists on their own--but your macro makes it easier.

3

V536_101618

MAXIMIZING THE POWER OF EXCEL? WITH MACROS AND MODULES

In addition, you could develop a more advanced macro that enables the user to change the case of text on a worksheet--something that exists in Word ... but not Excel?.

Or, you could provide the user with a custom function (SpellNumber) that can convert numbers to text.

All these commands are custom. The exist because you've built them in the Visual Basic layer of a workbook.

SkillSteps: To view the Visual Basic Editor in Excel?: Alt + F11. The Visual Basic layer or your file is a separate companion environment to your Excel? workbook. It has its own window and toolbar.

SkillTip: You can also view the Visual Basic Editor from the Developer tab on the ribbon.

The Developer tab provides many powerful tools. Excel? provides the tab ... but doesn't display it by default.

V536_101618

4

PARTICIPANT NOTEBOOK

The Developer Tab

To begin to learn to work with VBA, display the Developer tab on the Excel? ribbon.

SkillSteps: To display the Developer tab: File > Options > Customize Ribbon > Check the Developer box > OK The Developer tab comprises four groups of buttons: ? Code ? Add-ins ? Controls ? XML SkillSteps: To display the Visual Basic Editor: Developer > Code > Visual Basic

The Visual Basic Editor

The Visual Basic Editor is your "headquarters" for working with macro codes and properties.

5

V536_101618

MAXIMIZING THE POWER OF EXCEL? WITH MACROS AND MODULES

The Visual Basic Editor contains four standard areas:

The Command Area The Navigator Window

The Properties Window The Code Area

This is where you'll find the toolbar and buttons that provide shortcuts to running commands nested in the toolbar.

This is where you'll find the names of the currently open projects and discover whether any of them have modules and forms. You can toggle the view between List view and Folder view.

Everything has properties and you can view an object's properties by examining the Properties window while the object is selected.

The Code area displays any code associated with the object--whether it's a module, a sheet, a user form or a workbook.

SkillSteps: To view any of these windows that are not displayed: View > Select the window you wish to display

SkillTip: Each of the windows in the Visual Basic Editor is dockable or floatable. You can move each window by dragging its title bar.

V536_101618

6

PARTICIPANT NOTEBOOK

The Project Explorer

The Project Explorer is located on the left-hand side of the Microsoft? Visual Basic? window. It contains all the projects that are currently open. A project is an Excel? workbook, the Project Explorer displays each of the file's objects--the workbook itself and any worksheets, modules and forms the workbook contains.

SkillTip: To display the Project Explorer, press Ctrl + R The three buttons in atop the Project Explorer pane control what you view.

? The first button, View Code, lets you view the code of the object you have selected in the Project Explorer

? The second button, View Object, displays the object associated with the selected item (like a user form--rather than its code window)

? The third button, Toggle Folders, allows you to display your project's UserForms and modules without a folder hierarchy

SkillTip: Macros (VBA code) that are stored in a workbook are stored in modules or in worksheets listed in the Project Explorer.

7

V536_101618

MAXIMIZING THE POWER OF EXCEL? WITH MACROS AND MODULES

The Properties Window

Below the Project Explorer is the Properties window. If you do not see the Properties window, click the View menu, and select Properties window (or press F4). The property window displays the properties of any item selected in the Project Explorer.

For example, a worksheet can have one of three properties for "Visible." ? xlSheetVisible ? xlSheetHidden ? xlSheetVeryHidden

V536_101618

8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download