Spreadsheet Work Stuff

Henry asked for a list of stuff he had to do...

Here's a list:

1. get a timeplan together (row A)
2. make sure you have all your client intro stuff done (row B)
3. check your client needs (row C part 1) is done
4. come up with some evaluation criteria (row D)

Then...

5. get a prototype spreadsheet up and running (do not include this in your timeplan!)

Then...

6. check your client needs and evaluation criteria still make sense. Add anything in you need to (and delete anything you need to as well)
7. check your client intro makes sense as well - you might need to tweak this

Then...

8. do the inputs, outputs, processing section (row C part 2). This needs to be quite detailed. Anything anyone types in (or uses the mouse) on the prototype is an input for example. Most inputs will produce outputs (at least to the screen) and may well lead to processing.
9. develop annotated designs (row E). This will take a while
10. show the designs to your client and get them to write on them. Each one needs to at least be signed off
11. they have to ask for changes on at least some of the screens (these can be cosmetic or can be in the functionality of the spreadsheet (what it does...)
12. produce final annotated designs taking into account the changes
13. produce a detailed test plan (row F)

That's the prep work done. The designs will take ages and can be really frustrating. As ever, That Blue Square Thing has some suggestions.

In the exam time you will need to produce the spreadsheet, test it (documenting every test you do) and produce screenshots demonstrating what you have done. Then there'll need to be an in depth evaluation section.

Hannah says showing formulae view is easy

Someone's going to have forgotten how to do this...

Hannah says here's the way to do it:

1. is your sheet saved? Good.
2. hold the shift key down. Don't let it go, even if a bee is about to land on your nose.
3. look at the top left hand corner of the keyboard. Find the Esc key. DON'T TOUCH IT
4. look underneath it. You'll find a wierd looking key that only Mr Bagshaw (and other sad geeks) know the name of
5. that's the key you want
6. I call it the Star Wars button because I don't have a better name for it
7. still holding shift down? Good
8. tap - ONCE - the Star Wars button. Feel the force? Excellent!
9. see the formulae? Kewl!

To toggle back again all you need to do is Shift-Star Wars and it'll go back again.

You might need to twiddle around with column widths to show the formulae of course. Beware if you try and toggle back - you'd be better off saving under a different file name before you try and toggle back if you've changed too many column widths.

Hannah says hands that do dishes, they can be as soft as your face...

Pasting date values

OK - so you know how to copy stuff from one sheet to another and probably know how to do the Paste Special > Values thing to get the actual cell values rather than the cell reference.

But, when you do that the formatting can go dodgy, dates in particular.

So, rather than pasting the values go Paste Special > Values and number formats instead. And it works.

Rock and roll.

Hannah decides to do some concatenation

Concatenation is when you link stuff together in a cell.

For example, you might link cells A2 and B2 together using

=A2 &" " &B2

The ampersands (the & thingie) do the linking. The " " just adds a space between the two cell contents. If A2 and B2 had forename and surname in then that might produce a more useful cell to use.

There's an example of how to use concatenation to produce a cool and funky order number at That Blue Square Thing. Check it out, it's cool and funky and there are no chickens to be seen.

Hannah says concatenation is cool.

e2a: I've not thrown the macro code in as a screenshot as well. Just because I'm feeling kind.

Normal, Extreme and Erroneous

And that's just the usual Year 13 class...

So, this is about testing and test plans.

When you create them - and this applies equally for unit 10 (spreadsheets) and unit 3 (databases) you need sets of test data. That means data you want to use to test that your system works properly and check that it gives you the expected results.

You need to be really clear about the data you're going to use - that means I expect to see precisely what you're going to input - 10 in that box, Blue in that option and so on.

Now, the data needs to include 3 types:

• Normal - this is data which you would expect. Standard data that the system should accept without any problems
• Extreme - this is data at the extreme range of acceptable. You sometimes see this called 'boundary values'. You need to test each side of the boundary - so if a data value should accept values from 1-9 then I'd expect to see 9 and 10 being tested; if it has to be a maximum length then I'd expect testing at that length and one above the length and so on
• Erroneous - this is data which is clearly wrong - data which should never be accepted by the system. You want the system to reject this data. So, if I wanted a value 1-9 then I'd test things like 14, 473535, -1, 0, 4.5 and red.

OK?

Testing and Test Plans and Tests and other duck stuff

Ooh, I love a test. Especially in the Unit 10 spreadsheet exam

There are 12 marks (out of 70...) for testing and test plans - so they're important! It's rows f and g of the markscheme you need to consider: row f is in the prep work; row g is in the exam.

1. Plan first:

a) You need (second row of f):

• a plan which shows how you will test each individual element of the spreadsheet. This means each time data is entered, a button pressed, an option chosen or a spinner or tick box or something clicked.
• this plan needs to be in a sensible order
• it needs to say what you're going to do, what data (precisely) you're going to test the spreadsheet with, and what exactly the expected output is (bear in mind, doing something on one worksheet might have an impact on several worksheets)

b) Then you need (first row of f):

• a plan for the whole system - i.e. taking a series of clients through the spreadsheet from start to finish as if they were doing it for real
• this needs to include the expected output as well
• there must be sets of test data (second mark requires this)

2. Do the Tests

For row g all you do is actually follow the test plan through and provide evidence of doing so. It's important in this bit to show whether or not the actual outcome is what you expected it to be.

This means for both parts of the test plan - the individual testing elements and the whole system testing

Here's a hint - this will need a bunch of screenshots and might take a while. Numbering your tests will make this a load easier.

You'll also want to look at Testing, Testing 124 which I blogged last year (it's about databases but it'll kinda work) as well as Normal, Extreme and Erroneous which I'll blog in a minute.

Hannah's Guide to Excel Keyboard Shortcut Heaven

Hannah says...

• Showing Formulas: Ctrl+Star Wars (the star wars button being the odd one without a name underneath Escape in the top left of the keyboard). Think: control the force... :-)
• Showing Macro Code: Alt+F11. You might then need to navigate to the different sheets and modules in the left window pane.
• Spell Checking: F7

Hannah says Cowabunga

Viewing macro code

You'll need to be able to print the macro code - the actual VBA code. This proves you've actually done the macro and not just drawn a button and pretended to do it!!

This is easy: go ALT + F11

This will open the macro thingy. You may need to click on the individual sheets and/or modules down the left side of the screen to see all the code. You can then copy and paste into a word doc (or screenshot if you need to). You can simply close this window down to go back to your spreadsheet any time you want.

Recording a macro

OK, macros simply automate a routine process. Pretty much.

Start by setting up the macro to record:

Tools > Macro > Record new macro

Now give the macro a suitable name - something like clear_sheet (no spaces!!)

Hit OK - everything you do now is recorded as part of the macro. This will simply repeat everything you do each time the macro is run by hitting the button.

For example, you might want to delete all the cells in the sheet. Simply click in the first one, press delete, click in the second one...

Make sure you click at the end in the cell you want to leave the cursor in.

Finally hit the Stop Recording button (or go Tools > Macro > Stop recording)

Now we need to create a button to run the macro. You'll need to be able to see the Forms toolbar first of all - go View > Toolbars > Forms to do this.

Draw a button (the grey button shaped icon may be a good guess here...)

You'll then be able to choose the macro to assign to the button - so I'll add clear_sheet to it.

To finish off you can format the text on the button (Button 1 isn't the most helpful name in the world...).

Then whenever you hit the button you should get the macro being repeated. This makes stuff a lot easier for the user - they can do stuff a tthe press of a button rather than having to think about it. This is (probably) a good way to meet some client needs.

Macros, macros, macros!

First thing you need to do is change your security settings in Excel.

To do this on your home machine:

Tools > Options > Security Tab

Hit the macro Security button

Set this to Medium.

Easy.