MS Office solutions. Fantasy mobiles require: An excel spreadsheet with two work sheets; Inventory & Monthly Sales GUI to update stock quantities on the first worksheet GUI to calculate total and average sales of a

Authors Avatar

Unit 17 MS Office Solutions                


1. Introduction

Microsoft Office is an office suite of related applications. VBA (Visual Basic for Applications) is an advanced feature which can be used within the office suite applications to develop solutions for office environment problems. When coming from a VB.Net background, you have a head start but nonetheless there is still a learning curve to undergo. The VBA object library also differs from version to version of Microsoft Office making it more complicated for developers.

The crux of VBA is that it allows automation of already featured tasks found in the MS office applications. Its major benefit comes from the fact that by hitting a key, or clicking a button, it can initiate and complete an entire process removing many time-consuming and frustrating steps. It does also however provide additional functionality at times. According to Jacobson (2001, p. vii), ‘Excel was the first major application to include this exciting new architecture’.

Microsoft Excel Spreadsheets can perform powerful calculations. It also has the power to create, from the resulting data, different charts such as column, bar, pie, scatter and line charts. However, relationships between data are not possible as in Microsoft Access. Large amounts of data from different sources can make excel spreadsheets look very unorganised and at times complicated for the end-users. Therefore it is not considered a suitable solution for storing large amounts of data from a wide range of sources.

Most of the office applications have an integrated Visual Basic Editor (VBE), allowing the effortless creation of user-friendly forms.  Much like the Microsoft Visual Studio environment, objects such as textboxes and command buttons can all be added by dragging them on to a form from the objects toolbar. It is the same concept as VB.NET, whereby the code is automatically generated and then modified.

Microsoft Access allows records to be stored within the computer memory securely and can establish relationships between this data. The data can even be made more secure with the use of VBA. Through the use of Validation Rules, Input Masks, lookup wizards and combo boxes the chances of human error are reduced. Output documents can also be automated through the use of reports.

It is the ease of use that attracts many organisations to employ Microsoft Access in organising its various components. Microsoft Access however is considered too insecure for some organisations especially commercial, such as banks; more industry-strength database software (e.g.  Microsoft's SQL Server, Oracle) may be needed. It is also unsuitable for large dynamic websites due to performance issues. Sanger (2009) explains Microsoft Access is meant for individuals and small businesses. One of the reasons he gives for this is that the data within the database is limited to an overall 2GB size.  However, if an organisation grows, migrating Access data to a more robust database, Microsoft MySql Server, is possible. Access is however, a cheaper option than many other Database Management Systems.

Microsoft Word enables good management of many aspects of a document's appearance. Font types, sizes and colours are just one aspect of this functionality but a powerful one through the use of style sheets. Other aspects include the modification of Margins, Headers & Footers, Tables and Image attributes.

2. Task A

2.1 System requirements

Fantasy mobiles require:

  • An excel spreadsheet with two work sheets; Inventory & Monthly Sales
  • GUI to update stock quantities on the first worksheet
  • GUI to calculate total and average sales of a selected sales person
  • Two webpages for publishing inventory details

2.2 Prototyping

I used visio 2007, a CASE tool, to develop prototypes.  It features pre-defined interface objects that can be dragged and dropped onto the page. One of its advantages over paper prototyping is due to HTML Export which allows interactive prototypes that can link together via hyperlinks.

When the ‘Show’ button is clicked, the list box displays a list of the phone models and the first textbox is updated with the model number selected from the list box. The quantity that is sold is entered into the ‘Sold’ textbox and the remaining stock is updated when the update button is clicked.

The list box lists all of the sales staff IDs, one of the two radio buttons should be selected to decide what calculation will be made on the sales data of the chosen salesperson.  When the calculate command button is clicked then the calculation textbox will be updated according to the radio option. The cancel button should exit the form.

Join now!

2.3 Implementation part 1

The first thing I need to do before developing any of the MS Office solutions is to go into the excel options and set the developer tab to show in the ribbon. This is so that I can access the visual basic editor.

I then went about creating a worksheet and named it ‘inventory’. I added make, model and stock quantity columns and below them entered relevant data.

The actual range of data underneath the model column header had to be collectively named in order for it ...

This is a preview of the whole essay