44221: Information Systems

44221: Information Systems Spreadsheet Automation By: Ian Perry Room: C41C E-mail: i.p.perry@hull.ac.uk Tel: 01723 35 7287 http://itsy.co.uk/ac/0809/Sem1/44221_IS/

Named Ranges & Macros • As spreadsheets get larger and larger, and especially when a spreadsheet has multiple worksheets, it becomes increasingly difficult to: • Print specific portions of the spreadsheet. • Find your way around this complex 3D model. • Named Ranges & Macros • Provide ‘relatively easy to implement’ solutions to both of the above ‘problems’.

How would you print this?

Always use Print Preview Use ‘Page Layout’, ‘Page Setup…’in order to change Orientation to Landscape, and make a few other adjustments (e.g. display Gridlines). Oops! Too wide forA4 with a Portrait Orientation, and missing the Gridlines. Much better, but, what if I only want to print the table of data?

Now, when you ‘Print Preview’ Highlight the range to print. Use sub-menu to ‘Set Print Area’ Set the Print Area Switch to ‘Page Layout’ menu.

How would you print this? It is relatively easy to print it all, or to print any contiguous block of cells. BUT, what if I only want to print the data in Columns A, F, G & H? This is where named ranges come in very handy.

Top Left Months Quarters The ‘Shape’ of the Worksheet?

Use sub-menu to Define Name Highlight range to be defined. Press OK Name the range. Switch to ‘Formulas’ menu. To Name a Range? And repeat for the other Named Ranges

Set the: • ‘Print area’ • ‘Rows to repeat at top’ • ‘Columns to repeat at left’ • using the Named Ranges you have just defined. Now using ‘Page Setup’ AND always remember to ‘Print Preview’, otherwise you may waste a lot of paper.

Macros • What is a Macro? • A series of stored commands (i.e. a program) that perform a specific task. • The easiest way to create a Macro is to record it. • Before you record a Macro, it is a good idea to plan the commands you want the Macro to perform. • If you make a mistake when you record the Macro, any errors/corrections you make are also recorded. • Having recorded the Macro: • You can then then ‘run’ the Macro (in a number of ways), in order to repeat, or “play back”, the commands.

What are Macros used for? • Macros can be used for almost anything you want to do with a spreadsheet. • They are most useful when they are used: • to automate repetitive tasks. • and/or: • to simplify complex tasks. • We are going to use macros to: • Automate the process of moving about a workbook containing multiple worksheets. • Print specific sections, not necessarily in contiguous blocks, of a worksheet.

What do we want to achieve? Navigation between ALL Worksheets. Printing of specific parts of this Worksheet.

To Record a Macro – Step 1 Switch to the ‘View’ menu. Choose to ‘Record Macro…’ from the ‘Macros’ sub-menu.

To Record a Macro – Step 2 • Enter a suitable ‘Macro name’ • & Choose a ‘Shortcut key’

To Record a Macro – Step 3 This Blue button ‘warns’ you that; “A macro is currently recording”. Select the ‘YearSum’ tab; to ‘complete the Macro.

To Record a Macro – Step 4 Press the Blue button; to stop the macro recording.

To Run this new Macro • Or: • Select ‘View Macros…’, from the ‘Macros’ sub-menu. • Then: • choose the “Macro name:” you want from the list presented, and press the ‘Run’ button. • Either: • press the ‘Ctrl+Y’ keys at the same time.

To complete the Navigation • Simply record another 4 Macros, in the same manner, using the following settings in the ‘Record Macro’ pop-up window: • Can now move from one Worksheet to any other using these Macros. • BUT, what about Printing?

The Printing Plan! • Set some ‘Named Ranges’, i.e.: • SummaryTable and SummaryCharts • Then record two Macros, that use; • ‘Page Setup’ • to set the following: • Page • Orientation {Portrait or Landscape} • Margins • Center on page {Horizontally and/or Vertically} • Sheet • Print Area {SummaryTable or SummaryCharts} • Print {Gridlines}

SummaryTable (A1:D9) SummaryCharts (A1:J21) Set the ‘Named Ranges’

Recording a Printing Macro – Step 1 • Switch to the ‘View’ menu & choose ‘Record Macro…’ from the ‘Macros’ sub-menu. • Enter a ‘Macro name’ & choose a ‘Shortcut key’.

Recording a Printing Macro – Step 2 • Switch to the ‘Page Layout’ view & choose to display the ‘Page Setup’ pop-up window. • then, on the ‘Page’ tab; set ‘Orientation’ = ‘Landscape’

Recording a Printing Macro – Step 3 • On the ‘Margins’ tab, set; • ‘Center on page’ = ‘Horizontally’ & ‘Vertically’

Then, press the ‘Print Preview’ button Recording a Printing Macro – Step 4 • On the ‘Sheet’ tab, set; • ‘Print area’ = ‘SummaryCharts’ • ‘Print’ = ‘Gridlines’

Close the Print Preview window. Recording a Printing Macro – Step 5 Then stop the Macro Recording. AND Repeat for the other Print Range.

The Full set of Macros: • OK, but: • Having to remember the correct ‘Short-cut Key’, or go through the whole ‘View Macros…’, ‘Macros’, “Macro name:”, ‘Run’ rigmarole is not very user-friendly – is it?

Switch to the ‘Insert’ Menu Select the ‘Shapes’ sub-menu Select the Shape you wish to use. Associating Macros with Shapes - 1

Enter a suitable label Draw the Shape Right-click on the Shape you have just drawn; and select ‘Edit Text’ from the pop-up menu. Associating Macros with Shapes - 2

Right-click on the Shape a second time; and select ‘Assign Macro…’ from the pop-up menu. Associating Macros with Shapes - 3

Associating Macros with Shapes - 4 • When the ‘Assign Macro’ pop-up appears; • Choose the ‘Macro name:’ and press ‘OK’.

And there you have it! • All that remains to be done, is to: • Repeat the ‘Associating Macros with Shapes’ process for all of the other Macros. • Then ‘Copy’ & ‘Paste’ the Shapes, as and where required, so that the user can easily: • Navigate from one Worksheet to any other Worksheet. • Print specific ranges of the ‘YearSum’ Worksheet. • All without the user having to know very much about how to use Excel.

This week’s Workshop Add a Navigation System; to enable ‘easy’ movement between all 3 Worksheets. Add a Printing System; for 4 specific parts of the ‘Summary’ Worksheet.

44221: Information Systems