web, and macros - computer science and engineering · web, and macros robert grauer, keith mulbery,...

Copyright © 2008 Pearson Prentice Hall. All rights reserved. 1 Copyright © 2008 Pearson Prentice Hall. All rights reserved. 1 Copyright © 2008 Pearson Prentice Hall. All rights

reserved. 1 1 Copyright © 2008 Prentice-Hall. All rights reserved.

Web, and Macros

Robert Grauer, Keith Mulbery, Judy Scheeren

Copyright © 2008 Pearson Prentice Hall. All rights reserved. 2

Create a Web Page

Use the Save As option to save an Excel

workbook as a Web page

Use hyperlinks and graphics to make it more

effective

Preview it in the browser to see how it will

look on the Internet

Hyperlinks are links in an application or Web

page


Create a Web Page

Hyperlinks are links in an application or Web

page

Uses of hyperlinks:

To display another Web page

To display a different application file, such as a

Word file

To display another worksheet or different location

on a large worksheet in Excel

Can modify and/or delete hyperlinks


Create a Web Page

Insert pictures or photographs to highlight or

embellish a spreadsheet

To insert a picture in a worksheet:

Click Picture in the Illustrations group on the Insert

tab

Select the location of the picture and the picture to

be inserted in the Insert Picture dialog box

Click Insert


Inserting a Picture


Create a Web Page

SmartArt, a new feature in Excel 2007, is a

graphic organizer diagram that visually

presents information to communicate a

message

Use caution; the graphics are relatively

generic and will not always enhance the

information value of a workbook

The Reference Table shows the SmartArt

categories and descriptions


Create a Web Page


Create a Web Page

Format a worksheet for the Web

Simplicity should be the watchword for a Web page

Watch color and font combinations

Use graphics sparingly; too many graphics will be cause for long download times

.GIF files will download quicker than .JPEG files

Use the Save As option to save the document as a Web page


Create a Web Query

A Web query enables Excel to go to a particular Web site to obtain the Web based information

Web queries let you extract data from tables on a Web page

If the Web address changes, you must change the Web query and URL to ensure you have the most accurate information and citation

The information downloaded is based on the structure of the Web page

If you have to log in to the Web site, the query generally will not work


Create a Web Query


Macros and VBA

A macro is a set of instructions that tells

Excel which commands to execute

Visual Basic a programming language used

to create macros

You can also create macros by recording

your opertions.

You do not have to be a programmer to write

macros

To delete a Macro

Developer Macros Delete.



Create a Macro with the Macro

Recorder

Excel has two other file formats that store

macros:

XLSM files, which are macro enabled workbooks,

are stored using XML

XLSB files, which are workbooks stored in binary

format, are often used for very large

spreadsheets


Create a Macro with the Macro Recorder

The Macro Recorder has some issues:

Everything you do once you begin recording a

macro becomes part of the macro

Take your time and be sure the action is correct

Try to ensure your macros are broad enough to

apply to a variety of situations

The Visual Basic Editor (VBE) is used to

create, edit, execute, and debug Excel

macros


Create a Macro with the Macro Recorder - – Two macros expressed

as Visual Basic procedures.



Recorder To create a macro:

1. Click Record Macro in the Macros down arrow in the Macros group of the View tab to open the Record Macro dialog box

2. Type a name for the macro in the Macro name text box

3. Create a keyboard shortcut, if desired, for your macro in the Shortcut key box

4. Select a location to store the macro from the Store macro in drop-down arrow

5. Click OK to start recording the Macro



Recorder


Create Macro Buttons

Buttons are useful to start macros

May prefer to have a button on the Quick Access

Toolbar

Requires that the Developer tab is added to

the Ribbon first

To place the Developer tab on the ribbon, click the

Microsoft Office button and click Excel Options

Choose the Popular section and check Show

Developer tab on the Ribbon


Work with Macro Security

The proliferation of Excel macro viruses has made it a dangerous operation to open spreadsheets that contain viruses

To counter this threat, when you open an Excel workbook that contains macros, Excel automatically disables the macros and displays the Security Warning Macros have been disabled message

Click Options to open the Microsoft Office Security Options dialog box


Work with Macro Security


Understand the basics of VBA

Starting with Office 95, the recorded keystrokes of a macro were translated into Visual Basic commands

Can execute Visual Basic programs from within Excel

Microsoft Office uses a subset of Visual Basic known as Visual Basic for Applications (VBA)

You are not expected to be a programmer, but you do need to know the capabilities of VBA and its relationship with Excel

web, and macros - computer science and engineering · web, and macros robert grauer, keith mulbery,...

Documents