Excel 2003 Advanced

1

• In this section you will learn how to
• Share workbooks
• Track changes to a shared workbook
• Merge and revise a shared workbook
• Add VBA code to a macro
• Declare variables and prompt for input with VBA
• Iterate over a range, and use If/Then/Else
  statements with VBA
• Identify file properties
• Search for files
• Protect personal information in a file
• Create backup files
• Change Auto-Recovery settings
• Work with digital signatures
• Set macro security
• Protect your workbook with a password
• Use custom AutoFill lists
• Customize Excel
• Use Detect and Repair
• Use Text to Speech

2
Sharing A Workbook

• The first step in sharing a workbook in Excel
  2003 is to open the workbook, and choose
  Tools-gtShare Workbook from the menu bar.

3
Requesting Reviews

• Choose Tools-gtShare Workbook from the menu bar.
• Under the advanced tab, select the radio button
  to Keep change history.
• The next step is to choose File-gtSend To-gtMail
  Recipient (for review) from the menu bar. This
  will invoke the Outlook e-mail program.
• The words Please review the attached document
  will appear in the e-mail text area.

4
Reviewing A Workbook

• A reviewing toolbar should automatically appear.
• You can add comments to the file, or edit the
  data. When you are finished, click the Reply with
  changes button on the reviewing toolbar.

5
Tracking Changes

• To see the history of changes that have been made
  to the workbook, choose Tools-gtTrack
  Changes-gtHighlight Changes from the menu bar.
  This will display the highlight changes dialogue
  box.
• You can choose
• When
• Who
• Where

6
Merging And Revising A Shared Workbook

• The first step is to open the workbook, and then
  set it up for sharing by choosing Tools-gtShare
  Workbook from the menu bar. In the share workbook
  dialogue box, put a check mark under the Edit tab
  to allow workbook merging.
• Use the save as dialogue box to create a copy for
  each intended reviewer by saving the file as a
  different name (i.e. budgetMike, budgetNancy,
  budgetGreg, etc).
• Once you have gathered the reviewed files, you
  can open the original shared file in Excel and
  choose Tools-gtCompare and Merge Workbooks from
  the menu bar.

7
Adding Code To Your Macro

• First, to see the code for a macro, choose
  Tools-gtMacro-gtMacros from the menu bar. This
  action will display the Macro dialogue box.
• You can quickly record a simple macro by choosing
  Tools-gtMacros-gtRecord New Macro from the menu
  bar.
• Perform a sequence of Excel actions or keystrokes
  for your macro, and choose Tools-gtMacro-gtStop
  Recording to create the macro.
• Once you have recorded a macro, invoke the macro
  dialogue box.

8
Adding Comments To VBA Code

• To add a comment to a macro, open the visual
  basic window by choosing Tools-gtMacro-gtMacros. In
  the Macro dialogue box, select a macro and click
  the Edit button.
• Statements in green, beginning with an apostrophe
  () are comments.

9
Declaring Variables

• Variables can be thought of as place holders for
  numbers or text.
• When you declare a variable, you are telling the
  visual basic interpreter to set aside a space in
  memory for a number or a text string.
• The amount of space required for a variable is
  specified by its type.
• Some of the important variable types in VBA are
• String for strings of text data
• Double for large numbers with or without decimal
  places
• Integer for small to moderately sized numbers
  without decimal places (no fractional parts)
• Long for larger numbers without decimal places
• Date for holding date values
• Variant will hold any data type

10
Prompting For User Input

• There are times when you may require your macro
  to request numbers or text from someone using the
  worksheet. To do this, we can use an inputBox
  function.
• MyNumber inputBox ("Please enter a number",
  "Enter Number")
• This statement creates an inputBox with the title
  Enter Number, and the prompt Please enter a
  number. The user input from the box is assigned
  to the variable MyNumber.

11
Iteration Over A Range

• In programming terms, iteration involves the
  repetition of a series of programming
  instructions. Iterations are also commonly
  referred to as loops. If you want to perform an
  action on, or test each cell in a range, it is
  useful to be able to iterate over the range,
  (perform the same series of instructions for each
  cell in the range).

12
Using If Then Else Statements

• If Then statements can be included in your macros
  to allow certain actions to be taken based on the
  results of tests that you specify.
• If Then statements are called conditional
  statements because they allow you to control the
  program flow based on logical conditions that you
  establish.
• If condition evaluates to true Then statement

13
Identifying File Properties

• You can view the file properties to get useful
  information about the file without having to open
  the file itself.
• To view the properties of a file without opening
  it, right click on the file icon, and select
  properties from the menu.

14
Searching For Files

• To search for files from within Excel, click the
  File menu and click Search.

15
Protecting Personal Information

• To remove personal information from a workbook
  before distributing copies of it, choose Tools ?
  Options from the menu bar
• Select the Security tab and put a checkmark in
  the checkbox labelled Remove personal information
  from file properties on save.
• When you save the file, the author, company,
  manager, and last saved by information will be
  removed from the properties.

16
Creating Back Up Files

• Choose File ? Save As from the menu bar.
• In the save as dialog, select the General Options
  item from the Tools drop list.
• Put a checkmark in the Always create backup check
  box and then click the OK button.

17
Changing Auto-Recovery Settings

• Excels auto recovery feature repeatedly saves a
  copy of the workbook that you are currently using
  based on a specified time period.
• To change your Auto-recovery settings, choose
  Tools ? Options from the Excel menu bar, and
  click on the Save tab on the Options dialog box.

18
Digital Signatures

• To digitally sign an Excel workbook, open the
  workbook and choose Tools ? Options from the menu
  bar.
• Under the Security tab, click the Digital
  Signatures button.

19
Macro Security

• It is well known that VBA macros can be a source
  or carrier of computer viruses.
• f you choose Tools ? Options from the menu bar,
  and click the security tab, you will see a Macro
  Security button in the lower right of the Options
  dialog box.
• If you click on the Macro Security button, you
  will display a Security box detailing three
  levels of macro security.

20
Protecting Your Workbook with A Password

• To password protect a workbook, open the workbook
  and choose Tools ? Options from the menu bar.
• When the Options dialog appears, click the
  Security tab.
• Under the File Encryption for this workbook
  heading, you will see a text field labelled
  Password to Open.
• Type the password of your choice, and press
  enter.
• Excel will then open a confirm dialoguedialog box
  where you to type the password again to confirm
  it. When you click the OK button on the confirm
  dialog box, the workbook becomes password
  protected.

21
Customizing Excel by Changing Options

• You can make a lot of changes to Excel 2002 by
  making selections in the Options dialog box.

22
Using Custom Auto-fill Lists

• To create your own custom AutoFill lists choose
  Tools ? Options from the menu bar, and click on
  the custom lists tab in the options dialog box.

23
Using Detect and Repair

• You can use the Detect and Repair feature to
  automatically examine your office files, and
  replace any damaged or missing ones
• To use detect and repair from within Excel,
  choose Help ? Detect and Repair from the menu
  bar.