Excel Tutorial 8 Developing an Excel Application

1
Excel Tutorial 8Developing an Excel Application
2
Objectives

• Create, edit, and delete defined names for cells
  and ranges
• Paste a list of defined names as documentation
• Use defined names in formulas
• Add defined names to existing formulas
• Create validation rules for data entry
• Protect the contents of worksheets and workbooks

3
Objectives

• Add, edit, and delete comments
• Learn about macro viruses and Excel security
  features
• Create a macro using the macro recorder
• Edit a macro using the Visual Basic Editor
• Assign a macro to a keyboard shortcut and a
  button
• Save a workbook in macro enabled format
• Minimize the Ribbon

4
Planning an Excel Application

• An Excel application is a spreadsheet written or
  tailored to meet the users specific needs

5
Naming Cells and Ranges

• A defined name (often called simply a name) is a
  word or string of characters associated with a
  single cell or a range
• Select the cell or range to which you want to
  assign a name
• Click in the Name box on the formula bar, type
  the name, and then press the Enter key (or in the
  Defined Names group on the Formulas tab, click
  the Define Name button, type a name in the Name
  box, and then click the OK button)
• Or
• Select the range with labels and blank cells in
  the top row or first column to which you want to
  assign a name
• In the Defined Names group on the Formulas tab,
  click the Create From Selection button
• Specify whether to create the ranges based on the
  top row, bottom row, left column, or right column
  in the list
• Click the OK button

6
Naming Cells and Ranges
7
Adding Defined Names to Existing Formulas

• In the Defined Names group on the Formulas tab,
  click the Define Name button arrow, and then
  click Apply Names (if the cell reference and
  defined name definition are in the same
  worksheet)
• In the Apply Names dialog box, select the names
  you want to apply, and then click the OK button
• Or
• Edit the formula by selecting the cell reference
  and typing the defined name (or clicking the
  appropriate cell)

8
Adding Defined Names to Existing Formulas
9
Validating Data Entry

• To ensure that correct data is entered and stored
  in a worksheet, you can use data validation to
  create a set of rules that determine what users
  can enter in a specific cell or range
• An input message appears when the cell becomes
  active and can be used to specify the type of
  data the user should enter in that cell
• An error alert message appears if a user tries to
  enter a value in the cell that does not meet the
  validation rule

10
Validating Data Entry

• In the Data Tools group on the Data tab, click
  the Data Validation button
• Click the Settings tab
• Click the Allow arrow, click the type of data
  allowed in the cell, and then enter the
  validation criteria for that data
• Click the Input Message tab, and then enter a
  title and text for the input message
• Click the Error Alert tab, and then, if
  necessary, click the Show error alert after
  invalid data is entered check box to insert a
  check mark
• Select an alert style, and then enter the title
  and text for the error alert message
• Click the OK button

11
Validating Data Entry
12
Protecting a Worksheet

• Select the cell or range you want to unlock.
• In the Font group on the Home tab, click the
  Dialog Box Launcher
• In the Format Cells dialog box, click the
  Protection tab, click the Locked check box to
  remove the check mark, and then click the OK
  button
• In the Changes group on the Review tab, click the
  Protect Sheet button
• Enter a password (optional)
• Select all of the actions you want to allow users
  to take when the worksheet is protected
• Click the OK button

13
Protecting a Workbook

• In the Changes group on the Review tab, click the
  Protect Workbook button
• Click the check boxes to indicate whether you
  want to protect the workbooks structure,
  windows, or both
• Enter a password (optional)
• Click the OK button

14
Adding Worksheet Comments

• A comment is a text box that is attached to a
  specific cell in a worksheet
• Click the cell to which you want to attach a
  comment
• Right-click the cell, and then click Insert
  Comment on the shortcut menu (or in the Comments
  group on the Review tab, click the New Comment
  button)
• Type the comment into the comment box

15
Adding Worksheet Comments
16
Working with Macros

• A macro is a series of stored commands that can
  be run whenever you need to perform the task
• The Developer tab will enable you to create and
  use macros
• If the Developer tab isnt available, you will
  need to enable it using the Excel Options

17
Protecting Against Macro Viruses

• Macro viruses are a type of virus that uses a
  program's own macro programming language to
  distribute the virus
• The macro security settings control what Excel
  will do about macros in a workbook when you open
  that workbook
• Disable all macros without notification
• Disable a macro with notification
• Disable all macros except digitally signed macros
• Enable all macros

18
Setting Macro Security in Excel

• In the Code group on the Developer tab, click the
  Macro Security button
• Click the option button for the security setting
  you want
• Click the OK button
• or
• Click the Office Button, and then click the Excel
  Options button
• Click the Trust Center category, and then click
  the Trust Center Settings button
• Click the Macro Settings category, and then
  select the option button for the security setting
  you want
• Click the OK button

19
Recording a Macro

• In the Code group on the Developer tab, click the
  Record Macro button
• Enter a name for the macro, and specify the
  location to store the macro
• Specify a shortcut key (optional)
• Enter a description of the macro (optional)
• Click the OK button to start the macro recorder
• Perform the tasks you want to automate
• Click the Stop Recording button

20
Running a Macro

• Press the shortcut key assigned to the macro
• or
• In the Code group on the Developer tab, click the
  Macros button
• Select the macro from the list of macros, and
  then click the Run button

21
Editing a Macro

• In the Code group on the Developer tab, click the
  Macros button, select the macro in the Macro name
  list, and then click the Edit button (or in the
  Code group on the Developer tab, click the Visual
  Basic button)
• Use the Visual Basic Editor to edit the macro
  code
• Click File on the menu bar, and then click Close
  and Return to Microsoft Excel

22
Editing a Macro
23
Creating a Macro Button

• In the Controls group on the Developer tab, click
  the Insert button
• In the Form Controls section, click the Button
  (Form Control) tool, click the worksheet where
  you want the macro button to be located, drag the
  pointer until the button is the size and shape
  you want, and then release the mouse button
• In the Assign Macro dialog box, select the macro
  you want to assign to the button, and then, with
  the button still selected, type a new label

24
Creating a Macro Button
25
Saving Workbooks with Macros

• On the Quick Access Toolbar, click the Save
  button
• Click No in the dialog box
• Save as an Excel Macro-Enabled Workbook

26
Opening a Workbook with Macros