Spreadsheet Automation - itsy.co.uk - Home Page

heavyweightuttermostMechanics

Nov 5, 2013 (3 years and 10 months ago)

79 views

44221: Information Systems

Lecture 8
(Week 10)


Spreadsheet Automation


By Ian Perry


http://itsy.co.uk/ac/0506/Sem1/44221_IS/

Ian Perry

Slide
2

44221: Information Systems: Spreadsheet Automation

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’.

Ian Perry

Slide
3

44221: Information Systems: Spreadsheet Automation

How would you print this?

Ian Perry

Slide
4

44221: Information Systems: Spreadsheet Automation

Always use
File
,
Print Preview

Oops!


Too wide forA4 with

a Portrait Orientation

Much better, but, what if I only want
to print the table of data?

Use
‘File
’, ‘
Page Setup…


in order to
change the Orientation to Landscape,
and make a few other adjustments.

Ian Perry

Slide
5

44221: Information Systems: Spreadsheet Automation

Set the
Print Area

Highlight
the range
you want to
print.

Use menu to:



File



Print Area



Set Print Area

Now, when you:



File



Print Preview

Ian Perry

Slide
6

44221: Information Systems: Spreadsheet Automation

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.

Ian Perry

Slide
7

44221: Information Systems: Spreadsheet Automation

The ‘Shape’ of the Worksheet?

Top

Quarters

Months

Left

Ian Perry

Slide
8

44221: Information Systems: Spreadsheet Automation

To Name a Range?

Highlight
range to be
defined.

Use menu
to:



Insert



Name



Define …

Name
the
range.

Press OK

Ian Perry

Slide
9

44221: Information Systems: Spreadsheet Automation

Now ‘
File
’, ‘
Page Setup…


Setting the:




Print area





Rows to repeat at top





Columns to repeat at left


using the range names you
have just defined.

AND always remember to ‘
Print Preview
’,
otherwise you may waste a lot of paper.

Ian Perry

Slide
10

44221: Information Systems: Spreadsheet Automation

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.

Ian Perry

Slide
11

44221: Information Systems: Spreadsheet Automation

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.

Ian Perry

Slide
12

44221: Information Systems: Spreadsheet Automation

What do we want to achieve?

Ian Perry

Slide
13

44221: Information Systems: Spreadsheet Automation

To Record a Macro


Step 1



Tools
’, ‘
Macro
’, ‘
Record New Macro…
’:

Ian Perry

Slide
14

44221: Information Systems: Spreadsheet Automation

To Record a Macro


Step 2


Enter a suitable ‘
Macro name’


& Choose a

‘Shortcut key’

Ian Perry

Slide
15

44221: Information Systems: Spreadsheet Automation

To Record a Macro


Step 3


The ‘
Stop Recording’

pop
-
up will appear.


Simply select the
‘YearSum’

tab.

Ian Perry

Slide
16

44221: Information Systems: Spreadsheet Automation

To Record a Macro


Step 4


The
‘YearSum’

Worksheet will be displayed.


Select the ‘
Blue
’, ‘
Stop Recording’

button.

Ian Perry

Slide
17

44221: Information Systems: Spreadsheet Automation

To Run this new Macro


Or:


Select ‘
Tools
’, ‘
Macro
’,

Macros…
’ from the
menu system,
choose
the macro you want to
run

from the list
presented in the ‘
Macro’
pop
-
up window
, and
press the ‘
Run
’ button.


Either:


press the ‘
Ctrl+Y
’ keys at the same time.

Ian Perry

Slide
18

44221: Information Systems: Spreadsheet Automation

To complete the Navigation


Simply record another 4 Macros, in the
same manner, using the following settings
in the ‘
Record a Macro
’ pop
-
up window:

Macro Name

Short
-
cut Key

GoToQtr1

Ctrl+a

GoToQtr2

Ctrl+b

GoToQtr3

Ctrl+c

GoToQtr4

Ctrl+d


Can now move from one Worksheet to any
other using these Macros.


BUT, what about Printing?

Ian Perry

Slide
19

44221: Information Systems: Spreadsheet Automation

The Printing Plan!


Set some ‘Named Ranges’, i.e.:


SummaryTable

and
SummaryCharts


Then record two Macros, that use;



File
’, ‘
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}


Ian Perry

Slide
20

44221: Information Systems: Spreadsheet Automation

Set the ‘Named Ranges’

SummaryCharts

(A1:K21)

SummaryTable

(A1:D9)

Ian Perry

Slide
21

44221: Information Systems: Spreadsheet Automation

Recording a Printing Macro


Step 1



Tools
’, ‘
Macro
’, ‘
Record New Macro…
’:

Ian Perry

Slide
22

44221: Information Systems: Spreadsheet Automation

Recording a Printing Macro


Step 2



File
’, ‘
Page Setup…
’, then on ‘
Page
’ tab:



Orientation
’ = ‘
Landscape


Ian Perry

Slide
23

44221: Information Systems: Spreadsheet Automation

Recording a Printing Macro


Step 3


On ‘
Margins
’ tab:



Center on page
’ = ‘
Horizontally
’ & ‘
Vertically


Ian Perry

Slide
24

44221: Information Systems: Spreadsheet Automation

Recording a Printing Macro


Step 4


On ‘
Sheet
’ tab:



Print area
’ = ‘
SummaryCharts




Print
’ = ‘
Gridlines


Print

Preview

Ian Perry

Slide
25

44221: Information Systems: Spreadsheet Automation

Recording a Printing Macro


Step 5

Close the
Print Preview
window.

Then stop
the Macro
Recording.

AND


Repeat for
the other
Print Range.

Ian Perry

Slide
26

44221: Information Systems: Spreadsheet Automation

The Full set of Macros:


OK, but:


Having to remember the correct ‘
Short
-
cut Key
’, or go
through the whole ‘
Tools
’, ‘
Macro
’, ‘
Macros…
’, etc.
rigmarole is not very user
-
friendly


is it?

Macro Name

Short
-
cut Key

GoToYearSum

Ctrl+y

GoToQtr1

Ctrl+a

GoToQtr2

Ctrl+b

GoToQtr3

Ctrl+c

GoToQtr4

Ctrl+d

PrintSummaryCharts

Ctrl+s

PrintSummaryTable

Ctrl+t

Ian Perry

Slide
27

44221: Information Systems: Spreadsheet Automation

Associating Macros with Buttons
-

1


Need to display the ‘Forms’ Toolbar;



View
’, ‘
Toolbars…
’, ‘
Forms
’:

Select the
Button
Control

‘Draw’
the
Button

Ian Perry

Slide
28

44221: Information Systems: Spreadsheet Automation

Associating Macros with Buttons
-

2


When the ‘
Assign Macro
’ pop
-
up appears;


Choose the Macro

to assign

to the Button, and
press ‘
OK
’:

Ian Perry

Slide
29

44221: Information Systems: Spreadsheet Automation

Associating Macros with Buttons


3



Right
-
Click
’ on the Button;



Edit Text
’ to make it more meaningful:

Ian Perry

Slide
30

44221: Information Systems: Spreadsheet Automation

And there you have it!


All that remains to be done, is to:


Repeat the ‘
Associate Macros with Buttons

process for all of the other Macros.


Then ‘
Copy
’ & ‘
Paste
’ the Buttons as and where
required, so that the user can easily:


Move from one Worksheet to any other
Worksheet.


Print Preview specific ranges of the

YearSum
’ Worksheet.


All without the user having to know very much
about how to use Excel.