Vba For Excell by rockcartwright

VIEWS: 705 PAGES: 32

									UCL
EDUCATION & INFORMATION SUPPORT DIVISION
INFORMATION SYSTEMS




                                           Excel 2003

                                           Advanced Excel

                                           Setting up and
                                           automating Excel




Document No. IS-098 v2
Contents
Customising Excel .........................................................................................................................1
 Customising your menus and toolbars                                                                                                       1
 Excel’s customising options                                                                                                               4
AutoCorrect ................................................................................................................................... 7
Styles .............................................................................................................................................. 8
  Apply an existing style                                                                                                                             8
  Define a new style                                                                                                                                  8
AutoFormat.................................................................................................................................... 9
Introduction to templates ............................................................................................................. 10
  Standard templates                                                                                                                   10
  Custom templates                                                                                                                     11
  Auto templates                                                                                                                       12
  Opening and editing templates                                                                                                        12
Workbook properties .................................................................................................................... 13
 Automatically updated properties                                                                                                       13
 Preset properties                                                                                                                      13
 Custom properties                                                                                                                      14
 Setting document properties                                                                                                            14
 Searching for documents by category or keywords                                                                                        15
 Viewing document properties                                                                                                            15
Macros .......................................................................................................................................... 16
 The personal macro workbook                                                                                                                     16
 Recording a macro                                                                                                                               17
 Using a macro                                                                                                                                   18
 Deleting a macro                                                                                                                                18
 Assigning a macro to a toolbar button or menu                                                                                                   19
 Adding a macro to a button on a worksheet                                                                                                       19
 Macro security                                                                                                                                  20
 Macro projects                                                                                                                                  20
 Getting help with creating and working with macros                                                                                              21
Visual Basic for Applications (VBA) ............................................................................................22
  Visual Basics Editor                                                                                                          22
  The VBA toolbar                                                                                                               22
  Compile project                                                                                                               23
  Step Into                                                                                                                     23
  Get help for Visual Basic for Applications in Excel                                                                           23
Drawing tools ...............................................................................................................................25
 Drawing basic shapes                                                                                                                        25
 Stacking objects                                                                                                                            26
 Grouping and ungrouping objects                                                                                                             26
Learning more ..............................................................................................................................27




Document No. IS-098 v2                                                                                                                  17/08/2006
Introduction
This workbook has been prepared to help you use Excel more effectively. It aims to help you make
Excel work the way you work by customising Excel features and automating repetitive tasks. You will
learn to create templates and macros, edit simple macros, and view worksheet properties.
It assumed that you have a good working knowledge of Excel. In particular, you should be able to do the
following:
    Format cells and worksheets
    Change set up and print settings
    Use absolute and relative cell referencing
    Name cells and ranges

This guide can be used as a reference or tutorial document. To assist your learning, a series of practical
tasks are available in a separate document. You can download the training files used in this workbook
from the IS training website at: www.ucl.ac.uk/is/training/exercises.htm




UCL Information Systems                                                                           17/08/2006
Customising Excel
Customising your menus and toolbars
Toolbars
Excel 2002 and 2003 display fewer toolbar buttons by default than previous versions of Excel. You can
display the Standard and the Formatting toolbars on one row, or on two rows.
To display these toolbars on two rows (recommended):
1. Click on the Toolbar Options arrow.
                                                                                               Toolbar
2. Click on Show Buttons on Two Rows.                                                          Options
                                                                                               arrow
Alternatively you can do this in the Customize dialog box as follows:
1. From the Tools menu, select Customize, and then click the
    Options tab.
    or
    From the View menu, point to Toolbars, then Customize, and then click the Options tab.
    or
    Right-click on any menu or toolbar and select Customize
    from the shortcut menu. Then click the Options tab.
2. Ensure the Standard and Formatting toolbars on two rows
   check box is ticked.
3. Click Close.
Helpful hint:
The Standard and Formatting toolbars on two rows check box
affects only Microsoft Excel. The check box is not available if either
the Standard or Formatting toolbar is hidden, or if both toolbars are
floating.


Adding buttons to toolbars
To add a button, menu or command to your toolbar, you must first make sure it is visible on the screen.
If not go to View | Toolbars and select the toolbar you would like displayed by clicking in the
appropriate box.
1. Open the Customize dialog box using any of the
   methods described above.
2. Click the Commands tab.
3. In the Categories box, select a category for the command
   you want the button to perform.
4. Drag the command you want from the Commands box to
   the displayed toolbar.
5. Click on Close.

Removing buttons from toolbars
To remove the button, open the Customize dialog box.
Then drag the button off the toolbar onto your worksheet.

UCL Information Systems                                1                               Customising Excel
Create a custom toolbar
1. Open the Customize dialog box using any of the
   methods described above.
2. Select the Toolbars tab.
3. Click the New button.




4. In the Toolbar name box, type the name you want and click OK.
5. A new toolbar will appear.
6. Click on the Commands tab and add the buttons required to
   your new toolbar (as explained above), or add built-in menus (as
   described below).
7. You can change the images on the buttons of your toolbar if you wish. See Changing a toolbar
   button image below.


Adding a built-in menu
You can add any of the Excel menus to a toolbar as follows:
1. Open the Customize dialog box using any of the methods described above.
2. Click the Commands tab.
3.   In the Categories box, select Built-in Menus.
4.   Drag the command you want from the Commands box to the displayed toolbar.
5.   Drag the menu back if you wish to remove it.
6.   Click on Close.

Changing the image on a toolbar button
You can change the image on any toolbar button. You can copy an image from another button, create an
image in a graphics application and then paste it onto the button, or alter an
existing button image.

Copy an image from one toolbar button to another
You can copy an image from a toolbar button to another button only if the other
button is also on a toolbar (the same toolbar or a different one).
1. Open the Customize dialog box using any of the methods described above.
2. Click the toolbar button whose image you want to copy, and then right-click
   (click the right mouse button).
3. On the shortcut menu, click Copy Button Image.
4. Point to the button you want to paste the copied image onto, and then right-
   click.
5. On the shortcut menu, click Paste Button Image.

Customising Excel                                 2                           UCL Information Systems
Changing a toolbar button image
You can change an image on a toolbar button to another button.
1. Open the Customize dialog box using any of the methods
   described above.
2. Click the toolbar button whose image you want to change, and
   then right-click.
3. On the shortcut menu, point to Change Button Image.
4. From the sub-menu, select the button to which you want to
   change it.




Editing a toolbar button image
1. Open the Customize dialog box using any of the methods
   described above.
2. If the button you want to edit is not on a toolbar, add it to a
   new or existing toolbar.
3. Point to the button image on the toolbar, and then right-
   click.
4. On the shortcut menu, click Edit Button Image.
5. Click on the colour you want to use, then click on the pixels
   you want to change in the image box on the left. A preview of
   the button is displayed below the colour pad on the right.
6. Click OK when finished, and Close to exit the Customize
   dialog box.


Menus
Adding a customised menu
Click on the Toolbar Options arrow.
1. Open the Customize dialog box using any of the methods described above.
2. Click the Commands tab.
3. In the Categories box, select New Menu.
4. Drag New Menu from the Commands box to the menu bar or toolbar.
5. With the Customize dialog box still open, right-click the new menu on the toolbar, type a name in the
   Name box on the shortcut menu, and then press Enter.
6. To add commands to the menu, click and drag the required commands into the box that appears
   beneath the menu name when you hover the mouse pointer over it.
7. When you have added all the commands or built-in menus you require, click Close in the Customize
   dialog box.




UCL Information Systems                             3                                   Customising Excel
Excel’s customising options
There are many ways you can customise Excel to make it work in the way that suits you. To display the
Options dialog box:
1. From the Tools menu, select Options.
2. Select the relevant tab to display the type of options you want to customize.
It is a good idea to familiarise yourself with the various options available to you.
General tab
Some of the features you may want to change include:
Switch Function tooltips on or off.
Recently used file list — you can display up to 9 work-
books on your File menu so that you can open recently
used files without having to use the Open dialog box.
Set the number of worksheets in a new workbook
(maximum 255).
You can also set the default font and file locations.

View tab
There are many options you can switch on or off that
affect what is displayed in your worksheets.
Show
   Use the Formula bar and Status bar check boxes to
    switch them on and off from the screen display.
   If you are using Comments, ensure the Comment
    indicator only box is checked so that red indicators
    appear in the cells containing cell notes.
   If you wish each document to be shown as a separate
    window on the Windows Taskbar, check the
    Windows in Taskbar box.
Window options
   To display page breaks that have been automatically
    set by Excel, select the Page Breaks check box and then click OK.
   You can change the colour of the Gridlines, or remove them all together, by using the Window
    options at the bottom of the dialog box.
   By default, row and columns headers are displayed in Excel worksheets. To switch then off, deselect
    the Row & column headers check box, and then click OK.
   Whenever an outline is created in an Excel worksheet, symbols are automatically displayed to enable
    you to collapse and expand the various levels of the outline. To switch off the outline symbols,
    deselect the Outline Symbols check box and then click OK.
   If you would prefer cells with calculations that amount to zero to be left blank, rather than displaying
    a 0, deselect the Zero values option.
   Horizontal and vertical scroll bars are displayed by default in Excel. To switch them off, deselect
    the relevant check box and then click OK.
   Sheet tabs are normally displayed at the bottom of the worksheet. They can be switched off by
    deselecting the Sheet tabs check box. If your workbooks only contain one worksheet, you do not
    need to display Sheet tabs. When the Sheet tabs are not displayed, the horizontal scroll bar will run
    right across the bottom of the worksheet.
Customising Excel                                   4                             UCL Information Systems
Edit tab
The Edit tab offers further default editing options. For example:
  Edit directly in cell — when checked, allows you
   to double-click a cell to edit it.
  Allow cell drag and drop – when checked, allows
   items to be moved and copied with the mouse.
  Move selection after enter – Uncheck to stop
   Excel moving the active cell when you press
   Enter. Use the Direction list to specify an
   alternative direction to move the cell when Enter
   is pressed.
  Enable AutoComplete for cell values – Excel
   offers to complete entries made in a column based
   on data already in the column if this option is
   checked.
  Extend list formats and formulae – automatically formats new items added to the end of a list to
   match the format of the rest of the list. Formulae that are repeated in every row are also copied. To
   be extended, formats and formulae must appear in at least three of the five last rows preceding the
   new row.
  Enable automatic percent entry – select to multiply all numbers less than 1 that you enter in cells
   formatted in the percentage format. Clear this checkbox to multiply by 100 all numbers equal to or
   greater than 1.
  You can also opt not to have Paste and Insert Option buttons displayed.


Calculations tab
The way Excel calculates formulae can be controlled. This is done by selecting Options from the Tools
menu and checking the required elements on the Calculation tab.

Calculations
Automatic calculation can be switched off so that you
can manually calculate the entire worksheet when you
are ready. When a large amount of editing is to take
place within a worksheet, automatic calculation can
slow the process down.
When Manual calculation is selected, use F9, the Calc
Now function key, to update all calculations.
Alternatively, you can switch the Calculation setting on
the Calculation tab back to Automatic.
To turn off calculation before saving the worksheet:
1. From the Tools menu, click Options, and then
   click the Calculation tab.
2. Under Calculation, click Manual, and clear the Recalculate before save check box.
3. When you are ready to calculate your worksheets, press F9.
Helpful hint:
If a worksheet contains a formula that is linked to an uncalculated worksheet and you update that link, Excel
displays a message stating that the source worksheet is not completely calculated. To update the link with the
current value stored on the source worksheet, even though the value might not be correct, click OK. To cancel
updating the link and use the previous value obtained from the source worksheet, click Cancel.

UCL Information Systems                              5                                      Customising Excel
Speed up calculation of a worksheet containing a data table
To control when tables are calculated, click the Automatic except tables option. When you're ready to
calculate the table, click Calc Now (F9) on the Calculation tab.
To calculate only the Active worksheet, update only the chart on the worksheet, open charts linked to the
worksheet, and click the Calc Sheet button.

Iteration
Control the number of times a circular reference is calculated as follows:
In the Maximum iterations box, type the maximum number of iterations you want Excel to perform.
The more iterations you specify, the longer your worksheet takes to calculate.
In the Maximum change box, type the maximum change between iterations. The smaller the number
you specify, the longer your worksheet takes to calculate and the more accurate your answer is.

Workbook options
To automatically update links to other applications when performing calculations:
1. From the Edit menu, click Links.
2. Ensure the Automatic check box is selected next to Update at the bottom of the dialog box.
3. Click OK.
4. On the Tools menu, click Options, and then click the Calculation tab.
5. Select the Update remote references check box.

Precision as displayed
To set the precision with which numbers are calculated:
1. You can choose permanently to change constant values stored on the worksheet to their displayed
   values. If you later choose to calculate with full precision, the original full-precision values cannot be
   restored.
2. From the Tools menu, click Options, and then click the Calculation tab.
3. Select the Precision as displayed check box.

Chart tab
Plotting with empty cells
1. If the chart is embedded on the worksheet,
   double-click the chart. If the chart is on a
   separate sheet, click the chart sheet tab.
2. On the Tools menu, click Options, and
   then click the Chart tab.
3. In the Plot empty cells as section, choose
   how you want empty cells plotted:
   Not plotted (empty cells ignored)
   Zero (empty cells treated as 0 in value)
   Interpolated (empty cells estimated from
    surrounding know values)

Other tabs
There are many other Excel features that can be customised on the Custom Lists, Color, Error Checking,
Save, Spelling, and Security tabs. Many of these are covered in detail on other courses. It is a good idea to
familiarise yourself with these options.

Customising Excel                                    6                              UCL Information Systems
AutoCorrect
As in Microsoft Word, the AutoCorrect feature can be used in Excel to correct common keying errors.
AutoCorrect can be set to capitalise the first letter of a new sentence, capitalise the names of days,
correct two initial capitals, correct accidental usage of the Caps Lock key and correct common
misspellings automatically. By default AutoCorrect is set so that these errors are corrected automatically
when the Spacebar or Enter is pressed after entering the text.
You can also use AutoCorrect to automate the entry of commonly used words or phases.
Creating an AutoCorrect entry
AutoCorrect can be customised to correct your own misspellings and/or to replace abbreviations with
full text e.g. to replace #UCL with University College London.
1.   On the Tools menu, click
     AutoCorrect options. The         Click the
     AutoCorrect dialog box           appropriate
     appears.                         check boxes
                                      to select/
2.   Click on the AutoCorrect         clear options.                                                   Type the
     tab.                                                                                              replacement text
3.   In the Replace box type                                                                           in the With box.
                                      Type the
     the AutoCorrect entry            AutoCorrect
     (typically this is a             entry in the                                                    Frequently
     frequently misspelled            Replace box,                                                    misspelled words
                                      i.e. common
     word).                           misspelling,
                                                                                                      and their
                                                                                                      replacements are
4.   In the With box type the         abbreviation                                                    listed here.
     replacement text. Click          etc.
     Add and then OK.

Helpful hint:
Use something unique for each AutoCorrect entry, avoiding real words or abbreviations. For example, if you
put "name" in the Replace box, whenever you type this common word, Word would replace it with whatever
you put in the With box. This is why #UCL is used in the example above, if UCL were used all instances of
UCL would automatically turn into University College London.

Adding an AutoCorrect entry during a spell check                                                       Text box
AutoCorrect entries can also be added automatically during the spell-checking process.
1. Click the Spelling button on the toolbar.
2. When a word you often misspell or mistype is identified, select the correct spelling.
3. To add the misspelled word and its correct spelling to the list of words and phrases that are corrected
   automatically, click the AutoCorrect button in the Spelling dialog box.                       Text box

Cancelling an AutoCorrect entry
AutoCorrect is configured to correct errors when the Spacebar is pressed. To cancel a correction when
entering text, use the Undo button from the toolbar, or press Ctrl+Z.
Helpful hint:
You will have to repeat the Undo procedure twice if you actually intend to undo your most recent edit; the first
undo only cancels AutoCorrect.

Deleting an AutoCorrect entry
1. On the Tools menu, click AutoCorrect.
2. In the AutoCorrect list, click on the entry you want to remove, click Delete and then OK.

UCL Information Systems                                7                                            AutoCorrect
Styles
You can define a combination of formats, known as a Style, and apply it to other cells. In a new
workbook, only Excel’s built-in styles are available. You can define new styles by applying formats to a
cell, or by selecting an example cell that already has the required formatting.

Apply an existing style
1. Select the cells you want to format.
2. On the Format menu, select Style.
3. In the Style box, select the name of the style you want to
   use.
4. Click OK.



Define a new style
1. Select any cell(s) to which you want to apply your new style.
2. On the Format menu, select Style.
3. In the Style name box, type a name for the style, and click Modify.
4. Select the formats you want on any of the tabs in the dialog box.
5. To confirm your selections, click OK.
6. Clear the check box for any of the six format types that you don't want to include in the style
   definition.
7. To define and apply the style to any selected cells, click OK.
8. To define the style without applying it, click Add, and then click Close.


Define a style by example
1. Select any cell(s) that contains the style (formatting, etc.) that you wish to use.
2. From the Format menu, select Style.
3. In the Style Name box, type a name for the new style and click OK.


Copy styles from another workbook
1. Open the workbook you are copying styles from and the workbook
   you are copying to.
2. In the workbook you are copying to, select Style from the Format
   menu, and click Merge.
3. Select the name of the workbook that contains the styles you want to
   copy, and click OK.

Helpful hint:
If the active workbook contains any styles with the same name as styles you are copying, Excel asks if you
want to merge the styles that have the same names. If you want to replace the styles, click Yes. If you want to
keep the existing styles, click No. You receive this warning only once, regardless of the number of conflicting
style names.
Styles                                                8                               UCL Information Systems
AutoFormat
You can use AutoFormat to apply predefined formatting to your data. When you select AutoFormat,
Excel offers you a choice from a set of built-in formats you can apply to a selected range of cells.
To apply an AutoFormat to a range of cells:
1. Select the range you want to format.
2. From the Format menu, select
   AutoFormat.
3. In the AutoFormat box, select the layout
   you want. (An example of each layout is
   displayed.)
4. When you are happy with your choice,
   click OK.
When you click on the Options button, the
AutoFormat dialog box expands to allow you
to select the feature(s) of the selected format
you wish to use.
To use only parts of an AutoFormat:
1. In the Formats to apply section, clear the
   check boxes for the formats that you don't
   want to use. If you clear a check box for a
   format, that format will remain unchanged from the format last applied.
2. Check the example area to see if you are happy with the result, and click OK.




UCL Information Systems                           9                                         AutoFormat
Introduction to templates
All Excel workbooks and worksheets are based on templates. A template is a file used as a form to create
other workbooks, sheets and charts. New workbooks created from the template contain the same
layout, data, formulae, formats and styles as those of the template. Templates can contain standardised
text such as page headers and row and column headings, formulae, custom number formats, predefined
styles, page and print settings, as well as macros and customised menus and toolbars.
The default template for workbooks is called book.xlt and is used to create a blank workbook. It is
stored in the XLStart folder.
The default template for worksheets is called sheet.xlt and is used whenever you add a new sheet to a
workbook. It is also saved in the XLStart folder.
The standard Excel default workbook template normally contains three worksheets. Each worksheet has
256 columns and 65,536 rows. Every column has a standard width, and every row has a standard height.
The default font is usually set to Arial 10 point.

Standard templates
Excel comes supplied with a selection of templates designed to help in the production of common
business and home financial tasks. These templates can be modified for personal use.
Using a standard template
1. From the File menu, select New to display the New Workbook task pane.
    Excel 2002
    Under the New from Template section, select General Template.
    Excel 2003
    Under the Template section, select On my computer.
2. The Templates dialog box will be displayed.
3. You will find the default Workbook template on the General tab along with any other templates you
   may have created.
4. Click on the Spreadsheet Solutions tab to display more Excel templates.
5. You can create further tabs to store your own templates if you wish. See Saving a template on page 11.
6. Double-click on the icon for the template on which you wish to base the new workbook.




Introduction to templates                          10                            UCL Information Systems
Custom templates
You can create your own workbook and worksheet templates as detailed below.

Creating a workbook template
Open or create the workbook to be used as the basis for the template.
1. From the File menu, select Save As.
2. Select Template from the Save as type list. An .xlt extension will be added to the template name.




3. Type the template name in the File name text box.
4. The folder where the file will be stored will automatically change to Templates. By storing all .xlt
   files in the same folder Excel recognises and keeps track of templates.
5. Click Save to save the template.

Create a new tab
1. If you want to create a new tab within your Template dialog box in which to keep your templates,
   click the Create New Folder button.
2. Key in a name for your folder and click OK. This folder will be placed inside the Template folder
   and will appear as a tab within the Template dialog box.

Creating a worksheet template
1. In a new or existing workbook, delete all the sheets except the one to be used as the template.
2. Apply any text, formatting, formulae or settings that you require for your template.
3. From the File menu, select Save As.
4. Select Template from the Save as type list. An .xlt extension will be added to the template name.
5. The folder where the file will be stored will automatically change to Templates. By storing all .xlt
   files in the same folder Excel recognises and keeps track of templates.
6. Type the template name in the File name box.
7. Click Save to save the template.


UCL Information Systems                            11                             Introduction to templates
Base a new workbook on a template
1. From the File menu select New to display the New Workbook task pane.
2. Select either General Template under the New from Template section (Excel 2002), or On my
   computer under the Template section (Excel 2003), to open the Templates dialog box.
3. Click on the relevant tab to display the template you want.
4. Click on the template you require and then click OK.
5. A new copy of the workbook will be opened on screen. The new workbook will have the same
   name as the template, followed by the next sequential number. Note: This is a workbook – not a
   workbook template.


Adding a worksheet based on a worksheet template
1. With your workbook open on your screen, click the right mouse button over a sheet tab and choose
   Insert.
2. Double-click the icon for the worksheet template on which you want to base the new worksheet.
3. Your new worksheet will be inserted to the left of the worksheet on which you right-clicked.


Auto templates
An auto template is a workbook saved as a template in the XLStart folder or alternate startup folder
using the specific filename Book.xlt, sheet.xlt, dialog.xlt or macro.xlt. Auto templates, if they exist, will
act as the basis for all new items you create in the Excel environment.
The Book.xlt template becomes the default workbook. The Sheet.xlt template becomes the default
worksheet.


Opening and editing templates
Templates are files just like workbooks. If you need to change a template in any way, simply open, edit
and save it in the normal way.
1. From the File menu, select Open.
2. Change the Look In location to the Templates folder.
3. Double-click the name of the template you want to open.
4. Make changes in the open template.
5. From the File menu, select Save.




Introduction to templates                            12                              UCL Information Systems
Workbook properties
Workbook properties are details about a file that help identify it, for example, a descriptive title, the
author name, the subject, and keywords that identify topics or other important information in the file.
You can use workbook properties to display information about a file or to help organize your files so
that you can find them easily later. You can also search for documents based on document properties.

Automatically updated properties
Automatically updated properties include statistics that are
maintained for you by Office applications, such as file size and
the dates files are created and last modified. For example, you
can search for all files created after December 31 2005, or for all
files last modified yesterday.
To view these properties, select Properties from the File menu
and select the General and/or Statistics tab.




Preset properties
Preset properties already exist (such as author, title, and
subject), but you must enter a text value. For example, in Excel
you can use the Keywords property to add the keyword
"courses" to your files and then search for all files with that
keyword.




UCL Information Systems                             13                                  Workbook properties
Custom properties
Custom properties are properties you define yourself:
       You can assign a text, time, or numeric value to custom
        properties, and you can also assign them the values yes
        or no.
       You can choose from a list of suggested names or
        define your own.
       You can optionally link custom document properties to
        specific items in your file, such as a bookmark. For
        example, in a contract form, you can create a custom
        file property that is linked to a form field that contains the
        contract's expiration date. Then you can search for all
        contract files with expiration dates earlier than the date you
        specify.




Setting document properties
You can set document properties for the active file you're working on. If you want to be reminded to set
document properties for every workbook you create, you can have Excel automatically display the
Properties dialog box when you save files for the first time.
To set or change file properties:
1. On the File menu, click Properties, and then click the Summary tab.
2. Type any information you want in the file properties boxes.
3. Save your file.
The file properties are not actually associated with the file until you save the file.


Display the properties dialog box when you first save a file
To have Word automatically display the Properties dialog box when you save files for the first time you
need to turn on the feature in the Options dialog box.
1. On the Tools menu, click Options, and then click the General tab.
2. In the Settings area of the dialog box, select the Prompt for workbook properties checkbox.
3. Click OK.
In future you will be prompted to set properties for all new workbooks when you save them for the first
time.




Workbook properties                                    14                                UCL Information Systems
Searching for documents by category or keywords
Once you have added categories or keywords to your workbook properties, you can
use these to locate, or list all the documents within a particular category, or containing a
particular keyword as follows:
1. On the File menu, select Search (or File Search if using Excel
    2003).
2. Click Advanced Search at the bottom of the Basic Search
    taskbar.
3. Select either Category or Keywords from
    the drop-down list in the Property box.
4. Set the Condition to includes or is
    (exactly).
5. Key in the word(s) you are searching for in
    the Value box and click the Add button.
6. You can add additional criteria as required, selecting And or Or
    and then Add for each word you want to search for.
7. Click Search when you are ready to start the search.

Viewing document properties
If a workbook is open, you can view its properties by selecting Properties on the File menu and selecting
the relevant tab.
Viewing document properties from the Open dialog box
1. On the Standard toolbar, click Open.
2. In the Open dialog box, select the document for which you want to view properties.




3. On the Views menu, do any of the following:
     To preview the contents of the file before opening it, click Preview.
     To view such properties as the size of the file and the date the file was last modified, click Details.
     To view all document properties, click Properties.
Viewing document properties in Windows Explorer
You can also view the properties of any document by right-clicking on the workbook in Windows
Explorer or in the Open, Save, or Search dialog boxes and selecting Properties.

UCL Information Systems                              15                                  Workbook properties
Macros
A Macro is a recording of a sequence of commands which can be played back when required. Macros
can be created to automate any task that can be done in the normal way using cell and menu selections.
They are useful for automating routine tasks, simplifying complex worksheets, creating custom menus,
dialog boxes and buttons and running other applications.
One of the easiest ways to create a macro in Excel is to use the macro recorder. This records the actions
that you perform through the keyboard and mouse. It creates a program from these steps that you can
run any time you would like to repeat the actions. It creates a VBA script and can be viewed and edited
in the VBA editor (see page 22). Experienced programmers can write their own VBA script directly in
the VBA editor.
Macros are identified by a unique name and can be stored in the current workbook, a new workbook, or
your personal macro workbook so that they are available to use in other workbooks.
Helpful hints:
   Before you record or write a macro, plan the steps and commands you want the macro to perform. Always
    practice the keystrokes or mouse movements you want to record before recording your macro. Once the
    recording starts, everything you do will be recorded, including any mistakes.
   If you make a mistake when you record the macro, corrections you make will also be recorded. You can
    edit the macro later and remove any unnecessary steps you may have recorded.
   Never accept any default settings that are displayed in dialog boxes. Always key in the setting you need.
    Remember, when you run the macro, the settings which are automatically offered may not be the same.
    So, never assume anything, always key in what you want.
   Try to anticipate any messages Excel might display that might halt your macro or be confusing when the
    macro is run.
   If you want to use the macro you are recording in other workbooks, make sure that the macro doesn't
    depend on the current workbook's contents.
   If you use a particular macro often, assign it to a toolbar button, a menu or shortcut keys. That way, you can
    run the macro directly without having to open the Macros dialog box.


The personal macro workbook
When you record a macro that you want to have available to other
workbooks, you need to store it in your personal macro
workbook. This workbook is normally hidden. You can unhide it
by selecting Unhide from the Windows menu and then selecting
Personal.XLS in the unhide dialog box.
If you don’t see the Personal.xls file in the Unhide dialog box,
or the Unhide feature is unavailable, you have not yet created a
personal macro workbook. This is automatically created the first
time you create a macro and opt to store it to the Personal Macro
Workbook.
Once created, the Personal.xls file is placed in the XLStart folder along with the sheet.xlt, and book.xlt
templates. Excel opens these files automatically each time you open Excel.




Macros                                               16                               UCL Information Systems
Recording a macro
When you create a macro, you need to assign it a unique name and nominate where the macro is to be
stored (either in the current workbook, a new workbook, or in your personal macro workbook so that it
is available to all documents). You can also assign a shortcut key combination that can be used to play
the macro, or assign it to a button on a toolbar.
Start with the relevant workbook open and the cursor placed exactly where it will be each time you run
the macro.
1. On the Tools menu, point to Macro, and then select Record New Macro. The Record Macro dialog
   box will be displayed.
2. Key in a name for your macro in the Macro name box.
    Helpful hints:
       The first character of the macro name must be a letter. Other
        characters can be letters, numbers, or underscore characters.
       Spaces are not allowed in a macro name; an underscore
        character works well as a word separator.
       You can use uppercase and lowercase characters.
       Do not use a macro name that is also a cell reference or you
        may get an error message that the macro name is not valid.
3. Optional — Add macro details in the Description box. (The
   description can be very important, especially when you haven’t used your macro for a while.)
4. Optional — If you want to run the macro by pressing a keyboard shortcut key, enter a letter in the
   Shortcut key box.
    Helpful hints:
       You can use Ctrl+ letter (for lowercase letters) or Ctrl+Shift+ letter (for uppercase letters), where letter is
        any letter key on the keyboard.
       The shortcut key letter you use cannot be a number or special character such as @ or #.
       The shortcut key will override any equivalent default Excel shortcut keys while the workbook that
        contains the macro is open.
5. Select where you want the macro to be stored, i.e., just in the current workbook, in a new workbook,
   or in your personal macro workbook so that it is available in all workbooks.
6. Click OK.
7. The word Recording appears on the status bar and the Stop Recording toolbar
   appears on the screen. (Do not close this toolbar.) Every key press and mouse                    Stop    Relative
   selection is now being recorded.                                                                         Reference

8. Create your macro by carrying out the keystrokes and/or mouse selections you want to record. If you
   make a mistake, correct it. Both the mistake and the correction will be recorded so the macro should
   work OK. If you make a complex mistake, stop the recording, delete the macro (see below) and start
   the whole macro-creation procedure again.
9. When you have completed all the commands you wish to record, stop the recording by clicking on
   the Stop Recording button, or, on the Tools menu point to Macro and select Stop Recording.




UCL Information Systems                                    17                                                 Macros
Absolute and relative references
When creating a macro, cell selections are recorded as absolute references unless you click on the
Relative Reference button on the Stop Recording toolbar. If you click this button, the recorder begins
recording relative references. If you click the button again, the recorder records subsequent actions using
absolute references – and so on. You can toggle between relative and absolute references at any time
whilst recording a macro.
Helpful hint:
The Relative Reference button appears pushed in when selected. This is your indication that the recorder is
now using relative references. There is no other indication, as the button tooltip does not change to say
Absolute Reference.

Using a macro
Ensure the correct workbook is open and the cursor is located correctly on the appropriate worksheet.
(The cursor should be positioned exactly as it was when you recorded the macro.)
You can activate a macro in a number of different ways:
  By selecting the macro name from a list which can be
   found by selecting Macro and then Macros from the
   Tools menu and then clicking Run.
  By using the macro’s shortcut key, if one was
   assigned when the macro was created.
  By clicking on the macro button if one has been
   assigned on the toolbar.
  By selecting the macro from a menu, if the macro has
   been assigned to one.
  By clicking on an object such as a box or a button if
   one has been created on a worksheet.
Assigning a shortcut keystroke
If you did not assign a shortcut keystroke whilst you were creating the macro, you can do so afterwards
as follows:
1. Open the Macro dialog box by selecting Macro and then Macros
   from the Tools menu.
2. Select the macro to which you want to assign a shortcut keystroke.
3. Click on the Options button.
4. Key in the required keystroke. For example, holding down the Shift
   key and pressing a character (e.g. L) would result in a shortcut of
   Ctrl+Shift+L being assigned to your macro.
5. Click OK and close the Macro dialog box.

Adding or amending your macro description
As above, but add or change your macro description.

Deleting a macro
Open the Workbook which holds the macro.
1. From the Tools menu, select Macro and then Macros. The Macro dialog box will be displayed.
2. Select the macro you want to delete from the list offered on the left.
3. Click the Delete button.
Macros                                               18                             UCL Information Systems
Assigning a macro to a toolbar button or menu
Macros can be added to custom buttons that are specifically provided and can be placed on any toolbar.
To create a button on the toolbar for a macro:
1. Ensure the toolbar to which you want to add the button is displayed.
2. From the View menu select Toolbars and then Customise (or right-click on any toolbar and select
   Customise).
3. Select the Commands tab.
4. Select Macros from the list.
5. If you are creating a toolbar button, drag an appropriate button, e.g., the smiley face button onto
   whichever toolbar you want to display it on.
6. If you are creating a menu option, drag the Custom Menu Item onto whichever menu you want to
   display it on.
7. Close the Customise dialog box.
8. Click once on the new button or menu option and then select the macro that you wish to assign.
9. Click OK.
You can edit a toolbar button or menu option if you wish. See page 2 for details.
See page 1 for instructions on how to remove a button from a toolbar.


Adding a macro to a button on a worksheet
Buttons of any size and shape can be drawn on worksheets and macros assigned to them so that they are
run when the button is clicked. The button is drawn using the button tool on the Drawing or Forms
toolbar. When drawn, a current macro can be assigned, or a new macro recorded for it. Any number of
buttons may be created on any worksheet.
To create a button which, when clicked, will run a macro:
1. From the View menu select Toolbars and then Forms.

2. Click on the Button tool.
3. The mouse pointer is now a cross. A button can now be drawn in exactly the same way as
   drawing rectangles. Draw a button approximately the size of one cell, positioned
   appropriately.
4. When the mouse button is release, the Assign Macro dialog box is displayed. Any macro can
   be selected from the list, or a new macro recorded.
5. Select the macro you want to assign (or create your new macro) and click OK.
6. Click in the centre of the button, delete the current text, and key in a label for you macro button.
   Resize the button and/or text to display the text if necessary. Click elsewhere on the worksheet to
   exit the button.
7. Close the Forms toolbar.
8. Save your workbook.
9. Test your new macro button.



UCL Information Systems                            19                                               Macros
Editing a button
Once created, a button can be edited if necessary. The size and shape of the button may be altered, the
text on it may be changed, and various objects of the button may be selected. The button must be
selected before being changed.
To select a button to be edited, instead of its macro being run, hold the Ctrl key down whilst clicking on
it, or, right-click for the shortcut menu.


Deleting a button
Select the button by holding down the Ctrl key down while clicking on it and then press the Delete key.


Macro security
Excel has added security levels to macros because of potential threats from viruses. You can adjust the
security level if necessary as follows:
To reduce the macro security levels on a workbook:
From the Tools menu select Options.
1. Select the Security tab.
2. Click the Macro Security button.
3. Change the Security level to Medium.
4. Click OK twice to save your setting and close the
   dialog boxes.
IMPORTANT: This security level will now be the
default for all documents until it is changed.




Macro projects
When you create macros in a workbook, Excel stores the macros as a single macro project. A macro
project is a collection of macros grouped under one name and stored as a single entity in a document or
template. The macro instructions are saved in a module in the project.
Each workbook has one macro project that contains all the macros you have created for that workbook.
By default, when you create a macro, Excel stores the VBA code in a macro project named VBAProject.




Macros                                             20                             UCL Information Systems
Getting help with creating and working with macros
To get more help when working with macros:
1. From the Help menu,
   select Microsoft Excel
   Help.
2. Click on the Contents tab.
3. Click on Automating
   Tasks to expand it.
4. Click on Using Macros or
   Troubleshoot
   Automating Tasks and
   select the relevant topic to
   display the appropriate
   information in the pane on
   the right.
If you want to print out the
information, you can click on
the print icon at the top of the
Help dialog box.




UCL Information Systems                      21      Macros
Visual Basic for Applications (VBA)
Excel offers two ways for you to create a macro: the Macro Recorder and the Visual Basics Editor. The
Visual Basics Editor is an environment in which you write new and edit existing VBA code and
procedures.
Using VBA is beyond the scope of this course, but here is a brief overview of the Visual Basic Editor.




Visual Basics Editor
To open the Visual Basic Editor:
On the Tools menu, point to Macro, and then click Visual Basic Editor.
The screen is divided into three sections. The area on the right will display your VBA code.
The code window on the right shows:
      Keywords in blue. They are built-in VBA code such as "If", "True", "False".
      Comments in green preceded by a '.
To close the editor select Close and Return to Microsoft Excel from the File menu.

The VBA toolbar

    Return to Excel                     Run your code    Reset
                                        from VBA
                                        window


Visual Basic for Applications (VBA)                     22                       UCL Information Systems
Compile project
When you have written or edited your code, before you try to run it, click on Compile VBA Project on
the Debug menu. This will check your code and make sure that there are no errors in it. If there are any
errors, the first line of incorrect code will be highlighted. You will need to click the Reset button and
make your correction.
Run the debug process again and repeat until there are no more errors.


Step Into
This option is very useful when you are trying to find out where your code is going wrong. You can step
through the code line by line.
To use the Step Into option:
1. Size and position the document window and the VBA window so that you can see them side-by-side.
2. Position the insertion point in the document window and click back into the VBA window.
3. Press F8 to step through the code line by line until you reach your error, or the end of the code.


Get help for Visual Basic for Applications in Excel
To get help with using VBA:
1. On the Tools menu, point to Macro, and then click Visual Basic Editor.
1. If the Office Assistant is not showing, click Microsoft Visual Basic Help.
2. The following screen will appear:




The screen on the right explains the help documentation available under the Contents tab. This is shown
below:

UCL Information Systems                            23                    Visual Basic for Applications (VBA)
Welcome to the Visual Basic documentation.
Visual Basic includes many documentation tools, each designed to help you learn and use a particular
aspect of the product. The documentation provided with Visual Basic includes the following:
     Visual Basic User Interface Help
        Look here for Help on interface elements of the Visual Basic Editor, such as commands, dialog
        boxes, windows, and toolbars.
     Visual Basic Conceptual Topics
        The Conceptual Help topics include information to help you understand Visual Basic
        programming.
     Visual Basic How-To Topics
        Look in the How To section of Help to find useful common procedures, for example, how to use
        the Object Browser or how to set Visual Basic Environment options.
     Visual Basic Language Reference
        The Language Reference is the place to find Help on Visual Basic the language: all its methods,
        properties, statements, functions, operators, and objects.
     Visual Basic Add-In Model
        If you want to customize the Visual Basic editor, see this language reference for Help on the
        object model that allows you to extend the environment.
     Microsoft Forms Reference
        Look here for Help on Userforms and controls, and how to program with them using Visual
        Basic.




Visual Basic for Applications (VBA)               24                           UCL Information Systems
 Drawing tools
The use of diagrams can sometimes add interest and clarity to your worksheet. Microsoft has a range of
drawing tools which are available in PowerPoint, Excel and Word. Excel’s drawing tools are to be found
on the Drawing toolbar. If the toolbar is not already displayed, you can display it as follows:
1. On the View menu click on Toolbars.
2. Click on Drawing.
The Drawing toolbar will normally appear docked at the bottom of the screen. If it appears as a floating
toolbar, you can move it by clicking on and dragging its title bar.
  Draw       AutoShapes   Line   Rectangle Text   Insert Diagram or     Fill     Font      Dash      Shadow
  Menu       Menu                          box    Organization Chart    Colour   Colur     Style



         Select             Arrow   Oval   Insert  Insert     Insert    Line       Line      Arrow    3D
         Tool                              WordArt Clip Art   Picture   Colour     Style     Style


The Draw and AutoShapes menus available on the Drawing toolbar offer further options for creating
and manipulating drawing objects:              AutoShapes
                                                        menu




           Draw
           menu




Drawing basic shapes
1. Click on the AutoShapes menu, select a category (e.g. Lines, Basic Shapes etc.) and then click on
   an appropriate button.                                       Rotate handle

2. The pointer changes shape into a cross + so that you can
    draw with it.
3. Click and drag with the mouse to draw your shape and
    then release the mouse. The AutoShape is added to your
                                                                                                                   handles
    worksheet.
Note that the AutoShape is initially selected allowing you to
further manipulate the shape. This is indicated by the white
circles (called handles) around the shape. An AutoShape
MUST be selected before you can manipulate it in any way. There is also a green handle which can be
used to rotate the object.
Helpful hints:
To create a square you need to use the Rectangle AutoShape, and then hold down the Shift key whilst you
click and drag to constrain the AutoShape to form a perfect square.
To create a circle you need to select the Oval AutoShape, and then hold down the Shift key whilst you click
and drag to constrain the AutoShape to form a perfect circle.
UCL Information Systems                               25                                                Drawing tools
Moving AutoShapes
1. Select the AutoShape. The pointer changes to a four-headed arrow.
2. Click and drag the shape to its new location.

Shadows
To apply a shadow to an autoshape:
1. Select the shape that is to have a shadow.
2. Click on the Shadow button on the drawing toolbar. The Shadow menu appears. Select
   the required shadow option.
3-D settings
To apply a 3-D effect to an autoshape:
1. Select the shape that is to have a 3-D effect.
2. Click on the 3-D Style button on the Drawing toolbar. The 3-D menu appears.
3. Select the required 3-D effect.
To remove the effect, click No 3-D.

Stacking objects
Objects can be thought of as pieces of overlaying paper. Objects sometimes overlap, covering important
information. The order of objects can be changed easily.
To change the order of objects:
1. Select the object and then click on the Draw menu on the Drawing toolbar or right-click on the
   object to display the shortcut menu.
2. Select Order.
3. Select either Bring to Front, Send to Back, Bring Forward or Send Backward.

Grouping and ungrouping objects
When you group objects you combine them so you can work with them as though they were a single
object. You can flip, rotate, resize or scale all objects in a group as a single unit. You can also change the
attributes of all objects in a group at one time. For example, you may change the colour or add a shadow
to all objects in the group. You can also create groups within groups to help you build complex drawings.
You can ungroup a group of objects at any time, and you can easily regroup them by selecting any one of
the objects that was previously grouped.

Grouping objects
1. Select the objects you want to group.
2. On the Drawing toolbar, click the Draw menu, and then click Group.
Ungrouping objects
1. Select the group you want to ungroup.
2. On the Drawing toolbar, click the Draw menu, and then click Ungroup.
Regroup objects
1. Select any one of the objects that was previously grouped.
2. On the Drawing toolbar, click the Draw menu, and then click Regroup.


Drawing tools                                        26                             UCL Information Systems
Learning more
Central IT training
Information Systems runs courses for UCL staff, and publishes documents for staff and students to
accompany this workbook as detailed below:
Getting started with Excel      This 3hr course is for those who are new to spreadsheets or to Excel,
                                and wish to explore the basic features of spreadsheet design. Note
                                that it does not cover formulae and functions.
Getting more from Excel (no     This 3hr course is for users of Excel who wish to learn more about
formulae or functions)          the non-mathematical features of Excel and to work more efficiently.
Using Excel to manage lists     This 3hr course is for those already familiar with Excel who would
                                like to use some of its basic data-handling functions.
Excel formulae and functions    This 3hr course is aimed at introducing users already familiar with
                                the Excel environment, to formulae and functions.
More Excel formulae and         This 3.5hr course is aimed at competent Excel users who are already
functions                       familiar with basic functions and would like to know what else Excel
                                can do and try some more complex IF statements.
Advanced formulae and           This 3.5hr course is aimed at competent Excel users who are already
functions                       familiar with basic functions. It aims to introduce you to functions
                                from several different categories so that you are equipped to try out
                                other functions on your own.
Excel statistical functions     This course aims to introduce you to built-in Excel statistical
                                functions and those in the Analysis ToolPak. The course covers
                                major descriptive, parametric and non-parametric measures and tests.
Excel statistical formulae      This course covers best practice in constructing complex statistical
                                formulae in spreadsheets using common statistical measures as
                                example material.
Excel tricks and tips           This is a 2hr interactive demonstration of popular Excel shortcuts. It
                                aims to help you find quicker ways of doing everyday tasks. This fast-
                                paced course is also a good all-round revision course for experienced
                                Excel users.
Pivot tables                    Pivot tables allow you to organise and summarise large amounts of
                                data by filtering and rotating headings around your data. This 2hr
                                course also shows you how to create pivot charts.
Advanced Excel – Data           This course aims to help you learn to use some less common Excel
analysis tools                  features to analyse your data.
Advanced Excel – Setting up     Would you like to customise and automate Excel to perform tasks
and automating Excel            you do regularly? If you are an experienced user of Excel, then this
                                course is for you.
Advanced Excel – Importing      Do you share workbooks with others? Would you like to see who
data and sharing workbooks      has updated what? Do you know how to import data from text files
                                or databases? This course aims to show you how.


These workbooks are available for students at the Help Desk.


UCL Information Systems                          27                                        Learning more
Open Learning Centre
   The Open Learning Centre is open every afternoon for members of staff who wish to obtain
    training on specific features in Excel on an individual or small group basis. For general help or advice,
    call in any afternoon between 12:30pm – 5:30pm Monday – Thursday, or 12:30pm – 4:00pm Friday.
   If you want help with specific advanced features of Excel you will need to book a session in advance
    at: www.ucl.ac.uk/is/olc/bookspecial.htm
   Sessions will last for up to an hour, or possibly longer, depending on availability. Please let us know
    your previous levels of experience, and what areas you would like to cover, when arranging to attend.
   See the OLC Web pages for more details at: www.ucl.ac.uk/is/olc


Online learning
There is also a comprehensive range of online training available via TheLearningZone at:
www.ucl.ac.uk/elearning
A Web search using a search engine such as Google (www.google.co.uk) can also retrieve helpful Web
pages. For example, a search for ―Excel tutorial‖ would return a useful selection of tutorials.



Getting help
The following faculties have a dedicated Faculty Information Support Officer (FISO) who works with
faculty staff on one-to-one help as well as group training, and general advice tailored to your subject
discipline:
   Arts & Humanities
   The Bartlett
   Engineering
   Life Sciences
   Maths & Physical Sciences
   Social & Historical Sciences
See the faculty-based support section of the www.ucl.ac.uk/is/fiso Web page for more details.




Learning more                                       28                             UCL Information Systems

								
To top