Module Descriptors
SPREADSHEET AUTOMATION WITH VBA
MATH60275
Key Facts
Faculty of Computing, Engineering and Sciences
Level 6
15 credits
Hours of Study
Scheduled Learning and Teaching Activities: 36
Independent Study Hours: 114
Total Learning Hours: 150
Assessment
- GROUP ASSIGNMENT weighted at 50%
- CLASS-TEST weighted at 50%
Module Details
Module Additional Assessment Details
1. Group Assignment 50% (Learning Outcomes 1,2 and 3)
2. Practical Test 50% 1.5 hours (Learning Outcomes 1,2 and 3)
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.
Module Learning Strategies
The module will be delivered via 12 hours of lectures and 24 hours of computer-based workshops. A tutor will be present during the workshops to help you and to provide whole class instruction.
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 workshops.
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 workshops.
Module Special Admissions Requirements
Prior study of CE61025-4 Spreadsheet Modelling Techniques / CE62025-5 Spreadsheet Modelling Techniques, or equivalent.
Module Resources
Microsoft Excel and VBA
Module Texts
Essential Reading
Reed Jacobson (2007). Microsoft Excel 2007 Visual Basic for Applications: Step by Step. Microsoft Press, ISBN: 073562402 X
Reed Jacobson (2007). Microsoft Excel 2007 Visual Basic for Applications: Step by Step. Microsoft Press, ISBN: 073562402 X