FIT1013 Digital Futures: IT for Business
Assignment 2 Developing an application using Excel (VBA) (35%)
Part 1: Friday, 22 September 2023, 4:30 PM
Part 2: Friday, 13 October 2023, 4:30 PM
Group Assignment (4 Students)
Learning Objectives
Upon successful completion of this assignment, you should be able to:
i. Use appropriate data types, declare and use variables and/or constants.
ii. Write event procedures for some Excel and VBA objects.
iii. Use repetition and selection structures in VBA code.
iv. Use the Workbook, Worksheet and Range objects.
v. Use other objects as necessary.
vi. Perform data validation on user input.
vii. Construct arithmetic expressions in VBA code.
viii. Follow appropriate rules relating to the scope of variables.
ix. Design user forms using a variety of controls.
x. Apply other useful worksheet functions where appropriate, e.g. Vlookup() and Format() functions in
VBA code.
Group Assignment
● This assignment promotes students' collaborations working in groups. Students must form their
assignment grouping within the same applied session, the latest being the end of Week 8. You will
choose your group via the Assignment Two Groups (see Moodle Assessments page). Students who did
not form a group by the end of Week 8 will be allocated to a random group - minimum one student,
maximum four students, there will be no exemptions in completing the assignment tasks for groups with
fewer members.
● Every student in the group is required to participate actively and contribute to all parts of the assignment
(i.e., not simply dividing/doing their own tasks). At the end of the assignment, all students must complete
their Peer Evaluation (to be made available in Moodle) confidentially and individually. These would be
used as a basis for marks adjustment for the assignment's final score (if necessary). Failure to complete
the assignment peer evaluation by the due date will result in a lower mark for the final score.
Submission Requirements
● You are required to submit the assignment via Moodle as an MS Excel Macro-Enabled Workbook.
The filename should include the Unit Code, assignment number and your Group ID/number, following
this format: FIT1013A2_GroupID.xlsm
● Submit timesheets (FIT1013A2_GroupID_Timesheets.xlsx) that contain each member’s timesheet.
● Only one of the group members will need to upload the assignment files on Moodle for the group, but
all members must click the 'submit' button to accept the student submission statement.
2
Late Submissions
The late-submission penalty is 10 percent of the available marks in that task, not the marks you received. For
instance, an assignment has 100 marks, and you submitted the assignment one day late and received 65 marks.
In this case, the penalty is 10 marks deduction (10% of the total available marks).
65 - (10% of 100) = 55 of 100 marks.
Assessment Criteria
The assessment will give attention to how well you demonstrate your skills to complete the tasks – e.g., ensure
all assignment requirements are met, fulfil the functional requirements of the scenario, the development is
robust and maintainable.
It is important for each team member to contribute and participate equally, otherwise, you will be marked
based on the amount of effort and quality of the work that you produce. You should not divide the tasks among
yourselves – the assignment will be assessed as a group.
Demonstrate your application (Part 1 and Part 2) to your tutors in Week 10 and Week 12 respectively (see
Requirements section). This will allow you to explain your design. Any student who fails to turn up to any of
the demonstrations will fail that component of the assignment. In addition to the marks allocated for the tasks
that require demonstration, some marks are allocated for demonstration, e.g., for clarity and understanding.
Scenario
At the forefront of the sharing economy, LuxDrive has reimagined the car rental landscape in Australia. The
company sources luxury vehicles directly from manufacturers and dealers at wholesale prices, enabling them
to provide customers the luxury of driving high-end cars without the usual hefty costs. For instance, patrons
can cruise in a top-tier Porsche that would normally cost hundreds of thousands of dollars, all for just as low
as AUD 500 per day.
LuxDrive's transactions have been managed using conventional methods such as simple Excel spreadsheets.
As LuxDrive's popularity soars and the number of reservations increases, the company recognizes the need
for a comprehensive solution to manage its fleet, customer data, bookings, and vehicle deliveries and returns.
To address this, LuxDrive's CEO, Alex, envisions a tailored Excel application written in VBA to modernize
their existing rental management system. This application would optimize day-to-day operations, improve
efficiency, and enhance user-friendliness. The application should seamlessly integrate with the current data
structure, maintaining format and structure integrity. Alex is now obliging you to develop an Excel application
(file can be saved in “.xlsm” extension) that integrates their existing functionalities into several more user-
friendly interfaces. This development aims to facilitate the management of their day-to-day business
operations.
As the project unfolds, the CEO has provided the Excel spreadsheets that they currently use to maintain their
transactions – “FIT1013 A2_2023.xlsx”. Please note that this file does not contain all their rental data (for
business privacy), but they are enough for you to understand the business scenario. Some of the data in the
file are de-identified due to privacy issues but they maintain the same structure. Some of the data are imported
from the Vehicle Return Tracking System (the development of the Vehicle Return Tracking System (VRTS)
is not in the scope of this assignment). Your application should work the same using the provided actual data.
In short, do not change any format or structure of the original data in the worksheets, unless stated otherwise.
Alex emphasizes that the application must be compatible with the English version of Microsoft Excel to ensure
smooth usability across the company. He also insists on monitoring the progress through periodic
demonstrations so that he can be sure that the application is completed on time. Failing to do so may result in
reduced payments or cancellation of the project.
3
Other documents that need to be submitted are individual timesheets for payment purposes. Failure to do so
may result in penalties like reduced payments. The template for the timesheet is available in Moodle.
Part 1 Requirements
Functionality
1. A user form named “Main Menu” is presented upon opening the workbook. The user form will contain
buttons to perform the key activities described in the points below. If the user closes the form, it should
be possible to bring the form back by clicking on the button named “Show Menu” in the ‘Menu’
worksheet. This form should display the following buttons:
i. Customer Information
ii. New Rental
iii. Complete Rental
iv. Display Rental
Consider using shortcut keys/accelerators and tab order for your buttons.
(2 marks)
2. A user form named “Customer Information” will be shown when the corresponding button in the main
menu is clicked. It allows the details of a customer to be added, deleted or modified. This form should
allow users to cancel/close the form, or to confirm before the details are added/deleted/changed to the
“Customer” worksheet. If it is a new customer, then the details should be added under the last row.
Note: To add a new customer, the Customer ID is generated automatically by the system and
increments by 1 each time. E.g., if the ID of the last customer (last row) is 5, then the new customer
ID is 6.
Hint: In order to modify or delete an existing customer, the user needs to enter the Customer ID,
then other information will be populated to the user form.
(3 marks)
3. A user form named “New Rental” will open when the corresponding button in the main menu is
clicked. It allows the user to enter the details of a new rental order.
i. The form should display the following information when it is opened: an auto-generated Rental ID
(in chronological order), a list of customers, a list of available vehicles (CarMake, CarModel,
CarYear, etc), a list of Add-ons will be made available for selection. Date can be entered manually
(set default at current date and time).
ii. Note that more than one vehicle can be ordered at a time (more than one vehicle in a single delivery,
see existing data in the RentalDetails worksheet for examples). Design your application in such a
way that the user can add more than one vehicle in a single form, for the same customer.
iii. The form should include a ‘Confirm’ button that transfers the details of the new rental order to the
RentalDetails worksheet, updates item quantity in Add-ons worksheet, then returns to the previous
form i.e., “Main Menu”.
iv. Highlight the return information (ReturnDate, ReturnCondition, TotalRentalFees) for this rental in
light colour (e.g., yellow).
(6 marks)
4
Documentations
Your application should be briefly documented on the first worksheet (which should be named ‘Menu’). The
documentation should include:
● Group Number
● Authors’ details (Student ID & Name)
● Date of completion
● Instructions on how to use the application (including any features used or assumptions made)
Submit timesheets (FIT1013A2_GroupID_Timesheets.xlsx) that contain each member’s timesheet.
Complete the Peer Evaluation form in Moodle individually.
(2 marks)
Demonstrations
Demonstrate your application to your tutors in Week 10. (2 marks)
Files Provided
FIT1013 A2.xlsx which contains the initial data for each sheet in the Excel file.
● Customer worksheet contains information about customers.
● CarDetails worksheet provides information about the various vehicles that are available.
● Add-ons worksheet provides information about the quantity on hand for the optional Add-ons
features.
● RentalDetails worksheet with the detailed information for each rental.
Notes and Assumptions:
1. Please note that some of the details may not be normalised, as our intention is to cover mainly on the
functionalities rather than the data recorded in the sheets.
2. If you are an experienced VBA programmer and know other features and plug-ins that are not
covered in this unit’s materials, please speak to your tutor before using them. You must be using
VBA version 6.0, NOT VB.NET, which is available in Office 2019 and Microsoft Office 365.
3. Please check with your tutors if you have any assumptions.