Dept. Informatics /
Referred/Deferred Coursework 2010/11
CSD-1-BSY Software Development for Business Systems
UNIT CO-ORDINATOR: Mike Child ()
COURSE(S):
BSc, HNC, HND, FdSc: BIT, EBIT & EC, (2010 - 2011)
If not available please contact the department administrator
Oliver Nguyen ()
who will attempt to locate an academic member of staff who can advise.
Please note the non-availability of advice is not a valid reason for non-completion of the coursework as it repeats knowledge and skills delivered during the unit.
The coursework must be submitted, with a standard yellow front sheet to:
L105 by the referred coursework hand-in deadline
marked for the attention of the unit coordinator shown above
or
L105 by 16:00 on the day of the referred SDBS exam
(whichever is the later)
(as some students will need their logbook during the referred exam)
marked for the attention of the unit coordinator shown above
BSc, HNC, HND, FdSc: BIT & EC Year 1 (2010 - 2011)
Software Development for Business Systems - CSD-1-BSY - Referred Coursework
The "SalesDemo2" Scenario
(1) You should first of all tidy up your logbook and fill in any gaps from the weekly exercises.
(2) You are to work with the Excel workbook called "SalesDemo2.xlsm" which is available from the SDBS web area:
Within the workbook "SalesDemo2.xlsm" there is a key worksheet called "WeeklySales", with named ranges and formulas and some example data. As you are aware, software developers try where possible to reuse successful code, and you should notice that the design of this worksheet bears similarities to the design of the worksheet in the "Car Mileage Scenario" used in the lecture notes. However, some of the named ranges have poorly chosen names which are not meaningful - providing more meaningful names would be a good exercise.
Your task is to develop some VBA macros to manipulate the "WeeklySales" worksheet in similar ways to the macros used in the "Car Mileage Scenario". You may also wish to create more meaningful names for the named ranges. You should develop your macros based upon your experience of developing VBA macros for the "Car Mileage Scenario". The macros required are:
a)A macro to be called "ShowHighWeekTotals", which is to check through the 'Wk Total' range (J16:J25) and set the cells over 200 to be coloured red (it should use a For each myCell in range control structure).
b)A macro to be called "FindAStore", which uses an InputBox to ask the user for a store's name and then checks (using a For each myCell in range control structure) to find it in the range "Stores" (A16:A25). If it finds such a name then the colour of that cell should be highlighted to yellow.
c)Another version of macro (a), this one to be called "ShowStoresWithHighWeekTotals", and is to use the Offset method, so that it highlights the name of the store(s) who's 'Wk Total' is above the value of 200.
d)A slightly different version of macro (c), this one to be called "ShowStoresWithHighWeekTotalsVers2", that uses a Do While .... Loop control structure instead of the For each myCell in range control structure.
Deliverables and marking guidance
For all four macros you should provide the following documentation:
1.Structured English of the Macro.
2.A printout of the VBA macro code, (meaningful names, proper indentation, suitable internal documentation and comments).
3.A set of test data which could be used to check that your macro is doing what it is supposed to do, with predictions as to what should happen with the particular data.
Your work should be documented with comments and notes in your logbook - following the criteria for logbooks that is specified in the unit guide (see web page if you need copies). Furthermore, if your logbook is not up to date, then you should make sure your logbook is up to date.
If you are just doing the referred coursework but not the resit exam, then your logbook should be handed in to the school office by the date at the top of this specification. If you are doing the resit exam, and if the exam is later than the date at the top, then your logbook should be handed in at the end of the resit exam (you will need your logbook in the resit exam).
Copies of unit notes, etc. are available at:
SDBS - Referred Coursework - Page 1 of 2