Appropriate Hardware identified
This is the computer and printer I believe is suitable for use now and in the future of the Maintenance and the running of this efficient system:
Manufacturer: Packard Bell BV
Processor: Intel(R) Core(TM) 2 CPU 6300 @ 1.86GHz (2 CPUs)
Memory: 4066MB RAM
Hard Drive: 200 GB Total
Video Card: NVIDIA GeForce 7300 SE
Monitor: SyncMaster 911N/920N/920Nx, SyncMaster Magic CX918N
Sound Card: Realtek HD Audio output
Speakers/Headphones: Realtek HD audio
Keyboard: Terminal Server Keyboard Driver
Mouse: Terminal Server Mouse Driver
Operating System:
Windows XP Home Edition (5.1, Build 2600) Service Pack 2
(2600.xpsp_sp2_qfe.070227-2300)
Print technology
Printing method 4-colour inkjet printer with Dual Black Ink System, Epson Micro Piezo™ print head
Nozzle configuration 360 nozzles Black / 59 x 3 nozzles Colour (cyan, magenta, yellow)
Droplet size 3 Pl (minimum) with Variable-sized Droplet Technology
Ink system Epson DURABrite™ Ultra Ink
Print resolution
Up to 5760 x 1440 optimised dpi on suitable media using RPM (Resolution Performance
Management)
Print speed
Black text A4 Up to 37 ppm (Fastest), 25 ppm (Business quality)*
Colour text A4 Up to 20 ppm (Fastest), 11 ppm (Business quality)*
Photo 10x15 cm Approx. 49 secs*
Data collection (Data required, source, and method of collection)
Products
The only data needed for the invoice is the product names some detail (specifications) of the product and the price. This will be obtained from Alex, as some of the products he offers he makes himself, which runs under the name ‘WARLORDTF’. The other products he uses are high quality and can be found in stockists worldwide. Alex uses a 3rd Party company called BATE AND SON in which he buys the components wholesale. The prices he charges are reflective of the prices BATE AND SON charges. The information about the specifications are from the boxes of the products (except the ones made by Alex himself).
Customer Information
The customer’s information will be inputted by the customer and is only saved in the main file briefly but is stored in the archive until it is deemed ok to remove.
Data input explained with details of validation.
The information about the products sold will be written into tables, which will then be inserted into a respective spreadsheet. The table will have the product name, the Features, Size (be it physical or data storage), speed (if needed) and price. The information will be inserted onto static tables, which are referenced from by other sheets.
Data flow diagrams the explanations of what happens to the data between input and output.
Alternative methods of output considered
Display (on Screen)
There are many alternate methods of how the data on screen can be displayed to the user and the secondary user (the customer). The information can be displayed on screen, e.g. the user makes his order and saves it on the computer so that Alex can view later.
Spoken (Word Of Mouth)
The customer wishes to make an order, Alex could start the document, and tell them the options of order and the customer selects respectively. Then Alex could tell the customer the price and they could pay immediately.
Printed
The information can be displayed in a hard copy printed format, purely for Alex’s purposes. So that it gets printed out, the customer then gives Alex the sheet, then Alex would charge correspondingly and put that piece of paper into his ‘inbox’ for manufacture. Then after building it, he will sellotape the sheet to the assembled computer itself, then when the customer comes to retrieve the computer; Alex can easily find it because he stuck the paper to it, which makes the computer clear.
Choice of output method justified
The output method I have the preposition to use is the printed method, as it the most effective system as it prevents confusion between sold products, it also safeguards against errors as the customer takes the copy to Alex, which he then rings up to the till and he asks them to authenticate the information and pay.
Backup strategy identified
The file, after the customer has stipulated which components He/she wants, after printing it clears the file of the customer details and re-places the data within an alternative file called ‘Backup.xls’ this file remains concealed by the main Excel Window. This file (‘Backup.xls’) is copied to an online server for security; also a copy in an encrypted on a memory stick, which will be taken to Alex’s House and using ACER backup decryption software replicate it to another Stand-Alone Computer. The backup file, which is to be copied increases in size every time a customer saves, the file increase is approximately 200kb a time, I theory if 20 customers a day were to use it the file size will approximately be
Security strategy (e.g. password) explained
The shop is a single floor shop, with two main sections. The computer is situated in an alcove next to the till:
The red filled box (to the top right) is the computer which will have the software, the box filled in with thatched red lines will be the printer which the orders are printed from. Next to the red thatched box is a blue thatched box which is the till, inserted to enable the user to able to see how it looks aesthetically. The computer will be visibly marked with the address of the establishment, and again marked with UV-reflective ink. There are no windows in this building apart from a 3 ft x 4 ft pane at the front of the shop. The computer itself will be bolted to the table and the table respectively will be bolted to the floor. On the computer itself, the administrators account (system administrator) will be hidden and encrypted with a password. The guest account will be turned off and the main account that will have the file active will password encrypted also.
Design (9 marks)
Initial designs of worksheets sketched out.
The initial designs mentioned will be inserted after this page.
Written explanation of initial designs.
Sheet6
The cell range C4:J6 will be merged into one cell, as will D7:K9 and C15:L20. This has been done to enable that the text “Custom Computer Parts ¦ By WarlordTF ¦ Click ‘Next’ TO Start Your Order” will be fitted in with this font calibrations:
Font: Calibri
Font Style: Regular
Size: 36pt
Colour: Black (RGB #000000)
Example Text
The Background will be a neutral colour suggested by the user: Aqua.
Aqua Properties: R=51 G=204 B=204 Creating a blue/green colour.
The button labelled ‘Let’s Go!’ has a macro attached to it:
Sub FirstNext()
'
' FirstNext Macro
Sheets("Sheet1").Select
End Sub
This means that the sole purpose of the macro was the intent to take the customer to Sheet1.
Sheet1
Sheet1 is the main page, of which all the functions work through and around.
There are four main columns each with titles. On the far left within merged cells (B3:D3) is the title of Categories (font: Calibri, Font Style: Bold, Size: 11pt, Colour: Red – Example Text). Next, to the right, is the title of Shell Make (same Specifications) furthermore, is size and furthermost the price column. As the object, type changes so does the titles that it encompasses. (E.g. The Hard Disk Drive title reflects the following 3 titles making them HDD Make, size and price.
The Cells marked:
D4
D7
D10
D13
Are of particular interest because they are value input boxes for VLOOKUPS and macros on this page. To the left of the table there is a row of numbers, which are hidden, as they too are reference values but for another VLOOKUP on another page. The table and the hidden figures are within a named range (A3:G19) called FinalTBL, this makes it easy for reference should they be necessary.
The formulae in the cells will be written:
The IF command queries the value of a cell and returns two values: value if true and value if false.
The buttons link to sheets: 2, 3, 4 and 5 with the button at the bottom taking you to sheet7 (receipt).
Sheet2, sheet3, sheet4 and sheet5
These are all the same, so have been grouped together, the VLOOKUP values are hidden in the 1st column and the ranges are named to select these. The buttons contain another macro:
Sub Nvidia500()
'
' Nvidia500 macro
'
'
Sheets("Sheet1").Select
Range("D13").Select
ActiveCell.FormulaR1C1 = "1"
Range("D14").Select
End Sub
This macro changes the value in D13 to the number 1 which changes the values of the VLOOKUPS which reference from it to the data on the row marked with the ‘1’ and the column referenced would be displayed.
Sheet7
There is only one new thing on sheet7 and it is the macro to save print and close as this macro: Backs up the search to a different file, prints, remove all the details inserted, save and close.
The macro scripting for that would look like this:
Sub FINALFINALFINAL()
'
' FINALFINALFINAL Macro
'
'
Workbooks.Open Filename:="F:\ICT\EXCEL\Backup.xls"
Sheets("Sheet1").Select
Sheets.Add
Windows("Xl0000000(1).xlsm").Activate
Range("A2:E19").Select
Selection.Copy
Windows("Backup.xls").Activate
ActiveSheet.Paste
Range("B10").Select
Application.CutCopyMode = False
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("Xl0000000(1).xlsm").Activate
Range("B2:E19").Select
ActiveSheet.PageSetup.PrintArea = "$B$2:$E$19"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A1").Select
ActiveWorkbook.Save
Range("D13").Select
Selection.ClearContents
Range("D14").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D18").Select
Selection.ClearContents
Range("D19").Select
Selection.ClearContents
Sheets("Sheet1").Select
Range("D13").Select
ActiveCell.FormulaR1C1 = "0"
Range("D10").Select
ActiveCell.FormulaR1C1 = "0"
Range("D7").Select
ActiveCell.FormulaR1C1 = "0"
Range("D4").Select
ActiveCell.FormulaR1C1 = "0"
Range("D7").Select
ActiveWindow.SmallScroll Down:=3
Range("A1").Select
Sheets("Sheet1").Select
Range("B16").Select
Selection.ClearContents
Range("B19").Select
Selection.ClearContents
Range("E19").Select
Selection.ClearContents
Sheets("Sheet6").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
The parts of the macro listed in blue are referenced to the other file, Backup.xls, in red is the part of the macro, which clears the file in readiness for the next user.
User feedback on initial designs (comments, letter or questionnaire results)
The feedback from Alex is written on the initial designs. The Final design, which takes user comments into account, is after this page.
Subtasks identified
Test plan of up to 10 tests and expected results
Implement (12 marks)
Brief description of how the design was implemented explaining any changes that were made to the design
First, the sheets are created, by right clicking the bottom taskbar and selecting ‘New Sheet’:
In addition, arrange them into this order:
Sheet6
Select Range C4:J6, D7:K9 and C15:L17 and merge them using the cell merge function:
Change the font into Calibri, Size 36 and centre align (horizontal) and enter the relevant text and it will look like this:
Then a button must be created:
Drawn in, and a macro created:
Then, the background colour must be changed to ‘Aqua’ R=51 G=204 B=204 –
First, select all of the cells:
In addition, select the background colour to the specification making the file look like this:
We must create the tables of which the information is stored, on sheets: 2, 3, 4 and 5: leaving the tables looking like this:
Then, as the numbers on the left are to be hidden they are changed to Aqua, and the titles are in red and the text in white, as mentioned in the specific initial design.
The result will look like this:
Then, the back button will be inserted, (with the ‘Back’ macro attributed) and the buttons for the objects to be selected, however, the macro will not be allocated until the main page has been finished.
This is repeated on all the other pages (sheet 2, 3, 4 and 5) making the sheets look like this:
Each of the previous, sheets have ranges named and specified, below is an example for the specifications of the ranges being named:
The above table is prime example of how the data is named within the ranges including the ‘hidden’ values.
Now the main page is constructed, the table structure is set in place:
First, we create the VLOOKUP for the other cells, here is an ingenious section of intuition of where the figure, which is being referenced, is hidden by the button that takes the user to the option page. However there is an immediate problem, as it is covered by the button the user is not able to input the value, however the idea of the scheme is that the user will not input the value but the buttons (attached to macros) which will input the figures however hidden they are, this enables the VLOOKUPS to work and relay the values you want. So we enter values in these cells and attribute the VLOOKUPS to them, for this example we shall use the figure 1, 2, 1, 3:
Now the formula shall be put in, as we wish to reference from cell D4 it will be inputted into the formula, the formula used this time is: =VLOOKUP($D$4,Shells,2). However, this means the end query, when it clears the value the VLOOKUP sources from it would create a #VALUE error. I suggest a modification to the formula to ‘tidy up a corner’ (so to speak): =IF(D4>0,VLOOKUP($D$4,Shells,2),"") (without quotes). This checks the cell and asks a question, “Does the cell D4 have a value greater than 0? If the function passes (e.g. a value greater than 0 is inputted) then the formula states to run another formula (or sub-formula as is embedded within the main formula) then it will change the value of the cell to =VLOOKUP($D$4,Shells,2) which, coincidentally brings up the value we want. If the value check returns a false figure then the value “” will be inserted (it will leave the cell Blank, which is more aesthetically pleasing upon the eye).
When the formula is typed in, a box (usually blue but does change upon nested or multiple selection) appears around the cell which is referenced to in =IF(D4>0,VLOOKUP($D$4,Shells,2),"").
In addition, whenever a reference (usually a cell reference) is preceded by ‘$’ signs, it means that the cell encapsulated within them is locked and if the figure or the formula is moved the reference cell remains the same. As normally when the reference cell is moved any formulae linked to the cell gets re-written to accommodate the movement, if it’s the formula that is moved then the reference is changed but maintains its position relative to the formula cell.
Now, click enter on the cell and check the value within:
This is not the value we wanted, this is because the active formula within the cell was: =VLOOKUP($D$4,Shells,1). The logical assumption would be an error in the properties of the formula, which would be the correct assumption the specific allocation of the ‘column index number’; we look at the table being referenced, if we remove the buttons, and look at the table with the ‘hidden’ numbers shown, we can see that it is actually column 2 we needed:
Now, with the formula corrected, we can see the correct result of the VLOOKUP:
We repeat the formula to the other fields, leaving the fields looking like this:
Moreover, they are checked with the tables and sought to that they are correct.
The buttons are then inserted which takes the customers to the relevant pages enabling them to make their choice. In addition, on the sheets which have the unallocated buttons we allocate macros to them. The macro for selecting a product will look like this:
Sub Seagate100()
'
' Seagate100 Macro
'
'
Sheets("Sheet1").Select
Range("D7").Select
ActiveCell.FormulaR1C1 = "2"
Range("D8").Select
End Sub
The macros purpose (in this case selecting the Seagate 100GB Hard Drive). This macro takes you to the main page and inserts the figure, which tells the VLOOKUP to lookup information for the Seagate 100GB Hard Drive; it then selects another cell (usually the one above) to prevent the customer ‘accidentally’ selecting a different number. Therefore, with the buttons inserted the modifiable invoice looks like this:
As you can see, the boxes where you input the data is totally covered enabling the user to remain totally impervious to the background user functionality.
Now the OS cell (B16:D16 (merged)) this has multiple values but it is not important enough to have a separate sheet, so a drop-down box will be created. We select B16 and, under the data validation tab, we are brought to this pop-up box:
As we wish for this cell to be a ‘drop down’ box the Validation criteria must be changed from ‘Any Value’ to ‘List’ as can be seen below:
Now the multiple drop downs must be selected, None, Medusa, XP Home Edition SP3, Vista Ultimate, Vista Premium, Vista Basic, Mackintosh. These are the selections made by Alex and must be used in the drop down menu.
Note: no spaces are needed in the source, as it will input a space prior to the option.
As an option is selected, I need the maker’s cell to update with each choice. For this purpose, I see that a nested IF function would be appropriate as it needs to check the cell for a specific value. The formula I am going to use is this:
=IF(B16="None","None",IF(B16="Vista Basic","Microsoft",IF(B16="Vista Premium","Microsoft",IF(B16="Vista Ultimate","Microsoft",IF(B16="XP Home Edition SP3","Microsoft",IF(B16="Medusa","WarlordTF",IF(B16="Mackintosh","Apple Inc.","")))))))
As the formula checks the cell for the value “None” if the value is found then insert none into the value of the cell, however, if the cell does not contain the information it looks for the next specification in the formula and so on and so forth ad infinitum.
Then you insert a like formula into the price cell:
=IF(B16="None","£0",IF(B16="Vista Basic","£120",IF(B16="Vista Premium","£200",IF(B16="Vista Ultimate","£290",IF(B16="XP Home Edition SP3","£150",IF(B16="Medusa","£340",IF(B16="Mackintosh","£280","")))))))
This is nearly the same except instead of returning textual results it returns figures (currency - GBP).
However, in the price for the Wi-Fi the nested IF covers two cells: which causes a slight change in the formula:
=IF(B19="Yes",IF(E19="502.2gb","£50",IF(E19="502.2bg","£55","£0")),"")
Here the main cell is the cell B19 this is where the customer wishes to choose to have Wi-Fi or not, if the cell does not contain ‘Yes’ then it doesn’t bother continuing and returns a null value.
The only formulaic thing left to do on this sheet is quite simple and it is the total box, the formula is just a simple cell addition. However, we do not use the SUM operand as we only wish to deal with these specific cells, not a range of cells.
After =G4+G7+G10+G13+G16+G19 has been inserted the sheet should look like this:
This sheet is completed in the context of construction, bar the insertion of a button to the final invoice page and naming cell range A3:G19, ‘FinalTBL’.
The final table looks like this:
On sheet7, the only thing that is different formula-wise is a modification to the cell selection, but apart from that, the basic gist of it is already done on sheet1.
The formula, which has a slight modification, is the formula which VLOOKUP’s the information for the Wi-Fi:
=IF(Sheet1!B16=0,"Not Selected",VLOOKUP(A11,FinalTBL,2))
As you can see, the addition in the formula states a specific sheet and cell (a cell on sheet1). This is to prevent confusion within the formula with the cell B16 on the current sheet (sheet7).
With this addition in place, the sheet should look like this:
Printouts of each report, with screenshots of each worksheet and showing formulas used.
I am going do screenshots of each sheet in three ways: First, with all text black and all cell backgrounds absent. Secondly, with the formulae shown and finally the ‘customer’ view. The sheets will be shown in the order of the customer’s viewing: Sheet6, sheet1, sheet2, sheet3, sheet4, sheet5, and sheet7.
Sheet 6
Black and White
Formula View
There are not any formulae on this page, so there will be no reason to show it.
‘Customer’ View
Sheet 1
Black and White
Formula View
‘Customer’ View
Sheet 2
Black and White
Formula View
There are not any formulae on this page, so there will be no reason to show it.
‘Customer’ View
Sheet 3
Black and White
Formula View
There are not any formulae on this page, so there will be no reason to show it.
‘Customer’ View
Sheet 4
Black and White
Formula View
There are not any formulae on this page, so there will be no reason to show it.
‘Customer’ View
Sheet 5
Black and White
Formula View
There are not any formulae on this page, so there will be no reason to show it.
‘Customer’ View
Sheet 7
Black and White
Formula View
‘Customer’ View
Printout Report
Evidence that each test in the test plan was carried out comparing actual results with expected results
Objective 1 and 2
The macro, which copies the order to another file, is:
Sub FINALFINALFINAL()
'
' FINALFINALFINAL Macro
'
'
Workbooks.Open Filename:="F:\ICT\EXCEL\Backup.xls"
Sheets("Sheet1").Select
Sheets.Add
Windows("Xl0000000(1).xlsm").Activate
Range("A2:E19").Select
Selection.Copy
Windows("Backup.xls").Activate
ActiveSheet.Paste
Range("=1:1048576").Select
Application.CutCopyMode = False
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWorkbook.Save
ActiveWorkbook.Close
...(Not Complete)...
What this macro should do is open the file entitles backup.xls (on the F:/ drive (but the details can be changed). The macro then selects sheet1 on ‘Backup.xls’ and creates a new sheet (in creating the new sheet it automatically takes you to the newly created sheet) it then selects all the cells in the entire sheet and copies it in the new sheet.
This is what a customer has ordered:
The next page should have the same results:
The results are the same therefore; it passed this part of the test and now the macro is initiated and the sheet is copied to ‘Backup.xls’.
(Above is the test for Objective 1)
In addition, the print-off (Objective 2) will be situated after this page and does include the same values as the previous replications.
When errors occurred, explain how they were corrected
The errors that have occurred, have been rectified and checked within the implement stage, however I shall outline the errors themselves, which lead me to make adjustments to the construction.
FINALFINALFINAL Macro
There is a slight error of the transference of formulae. As the formulae are transferred the values are transported too, but if the ‘Backup.xls’ is open at the same time as ‘Xl0000000(1).xlsm’ the fields update to the current selection (be it present or not).
This is the result of opening the file whilst ‘Xl0000000(1).xlsm’ is open it updates the fields which is not what I wanted.
Solution
The only appropriate solution for this issue is to change the macro to change the type of paste, and specify a ‘paste special’ and choose ‘value only’ this will require the macro script to be changed slightly:
It means that the line: ActiveSheet.Paste must be changed to adopt a ‘paste special’ the new lines will look like this:
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
This specifies that only the format, cell widths and cell values must be copied. This will be checked to see if this edition will perform as expected.
This is the new version of the macro:
Sub FINALFINALFINAL()
'
' FINALFINALFINAL Macro
' Macro recorded 06/10/2008 by BVGS
'
'
Workbooks.Open Filename:="F:\ICT\EXCEL\Backup.xls"
Sheets("Sheet1").Select
Sheets.Add
Windows("Xl0000000(1).xlsm").Activate
Range("=1:1048576").Select
Selection.Copy
Windows("Backup.xls").Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B10").Select
Application.CutCopyMode = False
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("Xl0000000(1).xlsm").Activate
Range("B2:E19").Select
ActiveSheet.PageSetup.PrintArea = "$B$2:$E$19"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A1").Select
ActiveWorkbook.Save
Range("D13").Select
Selection.ClearContents
Range("D14").Select
Selection.ClearContents
Range("D15").Select
Selection.ClearContents
Range("D17").Select
Selection.ClearContents
Range("D18").Select
Selection.ClearContents
Range("D19").Select
Selection.ClearContents
Sheets("Sheet1").Select
Range("D13").Select
ActiveCell.FormulaR1C1 = "0"
Range("D10").Select
ActiveCell.FormulaR1C1 = "0"
Range("D7").Select
ActiveCell.FormulaR1C1 = "0"
Range("D4").Select
ActiveCell.FormulaR1C1 = "0"
Range("D7").Select
ActiveWindow.SmallScroll Down:=3
Range("A1").Select
Sheets("Sheet1").Select
Range("B16").Select
Selection.ClearContents
Range("B19").Select
Selection.ClearContents
Range("E19").Select
Selection.ClearContents
Sheets("Sheet6").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
This is the form I am going to use, and the macro shall act upon it:
Below is a screenshot of the successful copy and, as you can see, the text within the cells is only text, not formulae:
VALUE# error
This error is common for any mathematical function as it requires figures to calculate, but if there is a number missing from a selected field, it cannot add a NULL text value. There is a nifty new way of sorting this, using a formula recently introduced in Excel 2007, IFERROR is a formula which returns a value if there is an error in a function, whether mathematical of statistical.
Now, when an error occurs in the function it returns the value “Please Check Results”, as can be seen above.
If the formula completes successfully then the formula lets the mathematical function continue and complete:
Evaluate (5 marks)
Each original objective fully evaluated. Comments on how well the objectives are fulfilled.
Objective 1
The aim of this objective was ‘The user wants an electronic archive, which will stay separate from the main file, which gets updates as each customer completes his/her order. This archive will contain the customer’s information and the order that he/she is going to make.’
This objective was completed in the construction of the macro, which copies the order to the file ‘Backup.xls’ copying only the format and values, not the formulae, preventing confusion of updating. As this objective is completed with just a button click, I assume that this whole process is very easy, virtually idiot-proof and completed well.
Objective 2
The aim of this objective was ‘The user wants the final invoice, which is selected by the customer, to be printed as a hard copy to go in his ‘inbox/outbox’ file. This will enable him to be able to make the order without having to constantly re-check the virtual copy saved in an archive.’
This objective was completed in the construction of the macro mentioned prior, after copying the order to the file ‘Backup.xls’, the macro prints the table, to the default printer. As this objective is completed with just a button click, I assume that this whole process is very easy, virtually idiot-proof and completed well.
Objective 3
The aim of this objective was ‘The user would like to have his customers view four static tables (where he can edit the prices) and the customer can choose the component by clicking on it’s name and therefore activates a macro which will transfer the selected item onto the invoice. If the customer changes his/her mind then they can return to the static table to review the choice and re-select accordingly (and delete the previous order and write the new order in). The customer will repeat the process until he/she has made a final decision of choosing singular components to build up the computer; also, the accumulative total will be calculated. The customer can then input billing/address details and have their order archived (objective 1) and printed three times (Objective 2) one for the customer, one for the accounts and one for the computer construction.
’
This objective was completed by constructing the tables, the main invoice page (sheet1) has multiple links to the static tables (sheets 2, 3, 4 and 5) Alex can change the price any time by editing the value on the static table.
A critical Comment on anything that you think could be improved
I think that the spreadsheet could be improved by adding links to images (whether taken by a camera or images provided from the internet that could be decided later) this would enable the customer to view the product they are going to buy. Also, another issue I have found is unfortunately, because the computer uses office 2007 and the file is saved as a macro-enabled 2007 document, as commands and operands are added into Office 2007 are not functional with earlier versions and if this file was to be opened in 2007 many of the formulae would not work.
User feedback in the form of a letter or questionnaire.
The letter is inserted after this page.
Jack Jones
44 Manor Road
Walsall
West Midlands
WS2 9PU
Friday, 24 April 2009
Dear Jack,
I am writing this letter to thank you for your expert construction of the shops spreadsheet invoice.
However, I feel there would be some improvement if some items were added. As my stock can fluctuate with production, supply and demand, I would like a stock number which can be updated as stock comes in and as a customer chooses the part the value decreases by one.
Also, I may need to add operating systems as Microsoft are bringing out a new OS, Windows 7. Can I have a button which can add values to the drop-down box?
Those are the only additions I feel are necessary at this stage.
Many Thanks in Advance,
Mr. Alexander Joseph Heather
WARLORDTF
Evidence that you understand the user’s comments by making suggestions for future improvement
The stock number would be hard to set but I could set a cell and forbid an item being selected if it were to cause the cell to go into negate figures. Also, a macro can be created to reset all of ‘stock values’ as Alex could maintain his stock at 20 and order accordingly (the figure 20 is approximate as I am not sure how components he may use).
The macro for the drop down box I feasible as a macro can be recorded to take the text value from a cell and copy (or cut) it into the validation source box.