Module Descriptors
SPREADSHEET AUTOMATION WITH VBA
MATH60323
Key Facts
Digital, Technology, Innovation and Business
Level 6
15 credits
Contact
Leader: Emily Raeburn
Email: E.Raeburn@staffs.ac.uk
Hours of Study
Scheduled Learning and Teaching Activities: 36
Independent Study Hours: 114
Total Learning Hours: 150
Assessment
- PRACTICAL ASSIGNMENT weighted at 40%
- LAB BASED CLASS TEST (Allocate Slot on Exam Timetable) weighted at 60%
Module Details
Module Special Admissions Requirements
Prior study of Level 4 Mathematical Software / CE62025-5 Spreadsheet Modelling Techniques, or equivalent.
Module Resources
N/A
Module Learning Strategies
The module will be delivered via 12 hours (1 per week) of lectures and 24 hours (2 per week) of practicals.
Outside of class contact hours you will be expected to complete set exercises, to read background literature and to improve your skills by revising the material issued during the lectures and practicals.
Outside of class contact hours you will be expected to complete set exercises, to read background literature and to improve your skills by revising the material issued during the lectures and practicals.
Module Texts
Essential Reading
Reed Jacobson (2007). Microsoft Excel 2007 Visual Basic for Applications: Step by Step. Microsoft Press, ISBN: 073562402
Reed Jacobson (2007). Microsoft Excel 2007 Visual Basic for Applications: Step by Step. Microsoft Press, ISBN: 073562402
Module Additional Assessment Details
1. 40% practical (optionally group) assignment assessing Learning Outcomes 1 - 3 and consisting of 2 equally weighted milestones requiring the development of a VBA model based on a specification.
2. 60% lab based 1 hour and 30 minute assessing learning outcomes 1 - 3.
The class test will be the final assessment.
2. 60% lab based 1 hour and 30 minute assessing learning outcomes 1 - 3.
The class test will be the final assessment.
Module Indicative Content
VBA is the programming language behind most Microsoft applications. It can be used to automate series of spreadsheet tasks and to customise them for use by non-specialists.
VBA macro programming techniques for automating spreadsheet calculations:
objects, properties and methods: Ranges, Charts, PivotTables; procedures and functions, variables, control structures, input and output, debugging tools.
VBA user interface design:
customizing Userforms, ActiveX controls, WorkSheet and WorkBook events, toolbars and menus.
Quantitative applications:
eg. data analysis, financial modelling, simulation, production planning, budgetting, forecasting, stock control, statistical analysis, performance review, quality control.
VBA macro programming techniques for automating spreadsheet calculations:
objects, properties and methods: Ranges, Charts, PivotTables; procedures and functions, variables, control structures, input and output, debugging tools.
VBA user interface design:
customizing Userforms, ActiveX controls, WorkSheet and WorkBook events, toolbars and menus.
Quantitative applications:
eg. data analysis, financial modelling, simulation, production planning, budgetting, forecasting, stock control, statistical analysis, performance review, quality control.