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
Extracts from this document...
Introduction
Unit 17 MS Office Solutions
MS Office Development |
Contents
1. Introduction
2. Task A
2.1 System requirements
2.2 Prototyping
2.3 Implementation part 1
2.4 Functional Testing
2.5 Logical Testing
2.6 Implementation Part 2
2.7 Functional Testing
2.8 Logical Testing
3. Task B
3.1 System Requirements
3.2 Prototyping
3.3 Implementation
3.4 Functional Testing
4. Task C
4.1 System Requirements
4.2 Pseudocode
4.3 Implementation
4.4 Testing
5. Evaluation
5.1 Evaluation of the Solutions
5.2 Self Evaluation
6. Bibliography
6.1 Books
6.2 Websites
6.3 Online Articles
6.4 Other
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)
Middle
2.6 Implementation Part 2
I created a new worksheet naming it ‘November ‘. I added Date, Stock No, Sales in £ and Sales Person ID columns and below them I entered the relevant data. I then created the form as in the Visio prototype.
I used the following code to enable the form to interact with the spreadsheet.
Private Sub UserForm_Initialize() Dim strId As String, rngData As Range, rngCell As Range Set rngData = _ Application.Workbooks("November.xls").Worksheets("November").Range("a3").CurrentRegion strId = "SalesPerson" For Each rngCell In rngData.Columns(4).Cells If rngCell.Value <> strId Then lstID.AddItem rngCell.Value strId = rngCell.Value End If Next rngCell 'select default list box item lstID.ListIndex = 0 'select default option button optTotal.Value = True End Sub |
This code assigns a column heading to a variable named ‘strId’. By the use of this column heading I could make sure that only unique IDs are added to the list box by the use of a ‘for-each’ loop.
Option Explicit Private Sub cmdCalc_Click() Dim strId As String, intRow As Integer, intNumSales As Integer, curSales As Currency Dim rngData As Range, jan As Worksheet Set rngData = _ Application.Workbooks("NovemberSales.xls").Worksheets("November").Range("a3").CurrentRegion strId = lstID.Value intRow = 2 Do Until rngData.Cells(RowIndex:=intRow, columnindex:=4).Value = strId intRow = intRow + 1 Loop Do While rngData.Cells(RowIndex:=intRow, columnindex:=4).Value = strId curSales = curSales + rngData.Cells(RowIndex:=intRow, columnindex:=3).Value intNumSales = intNumSales + 1 intRow = intRow + 1 Loop If optTotal.Value = True Then lblAnswer.Caption = Format(expression:=curSales, Format:="currency") Else lblAnswer.Caption = Format(expression:=curSales * intNumSales, Format:="currency") End If End Sub |
The variables for the end calculation are set according to the selected list box item by use of do loops. A if statement is used to determine which calculation should be made depending upon the radio button selected. The end calculation is displayed in the ‘Answer’ label.
2.7 Functional Testing
Test No. | Test Details | Expected Result | Actual Result |
7. | Check when the form loads all of the sales staff IDs are listed in the list box | All sales staff IDs are displayed within the list box | Pass |
8. | Click Calculate button with the radio button total option | The total label value should display an amount from a calculation | Pass |
9. | Click Calculate button with the radio button average option | The total label value should display a NEW amount from a calculation | Pass |
10. | Click Cancel Button | Form and spreadsheet should exit | Fail |
Test No. 10 failed due to using VB.Net code instead of VBA by mistake. I used me.Close while I should have used End. The following error box appeared as a result after clicking the cancel button.
At clicking the help button, I received more information that enabled me to understand what was wrong. The exact error was ‘Method or data member not found (Error 461)’. The debugger highlighted the start of the cancel button click event enabling me to understand the whereabouts of the error. After researching I found that the close method is not valid in VBA.
2.8 Logical Testing
Test No. | Sales Staff ID | Option | Expected Value | Observed Value |
11 | 1 | Total | 6,377.00 | 6,377.00 |
12 | 1 | Average | 1,062.83 | 38,262.00 |
13 | 2 | Total | 19,185.00 | 19,185.00 |
14 | 2 | average | 1,918.50 | 191,850.00 |
Tests 12 and 14 are both dealing with the average of each salesperson’s sales for the month. They failed so I therefore need to look into the logic of the code that calculates the average. I found that the expression used a multiplication symbol while it should have used division, see below:
OLD CODE | lblAnswer.Caption = Format(expression:=curSales * intNumSales, Format:="currency") |
NEW CODE | lblAnswer.Caption = Format(expression:=curSales / intNumSales, Format:="currency") |
3. Task B
3.1 System Requirements
MT Training Centre requires:
- A database with student and lecturer tables
- A word template to allow a student list
- Data transfer from the database of a student list from a chosen lecturer
- Print and save functionality
3.2 Prototyping
The lecturer first name should be entered into the first textbox in order to transfer the relevant student data. If the user would like to print and save the resulting document then a ‘y’ should be entered into the second textbox and if not, then an ‘n’ should be entered. The OK command button will initiate the data transfer process. The Exit button will exit from the word document.
3.3 Implementation
At the beginning of this task I created the above word document template in order that data from a database can be transferred to it. Then after creating the lecturer database I was able to start the visual basic editor in that same Word file.
Option Explicit Private Sub cmdOK_Click() Dim blnOpen As Boolean, strFileName As String, docStudents As Document, tblStudents As Table Set docStudents = Application.Documents("vbatask2.doc") 'display trainer name in document docStudents.Paragraphs(6).Range.Text = _ "Lecturer:" & vbTab & txtLecturer.Text & vbNewLine |
The above and following code implements at the click event of the ‘OK’ command button. The vbatask2 document is set to a variable and the range property is used to insert text from the form into paragraph six of the document.
For Each tblStudents In docStudents.Tables tblStudents.Delete Next tblStudents 'get the name of the Access database With Application.Dialogs(wdDialogFileOpen) .Name = "*.mdb" blnOpen = .Display strFileName = .Name End With If blnOpen = True And Right(strFileName, 4) = ".mdb" Then Selection.EndKey unit:=wdStory Selection.Range.InsertDatabase Format:=wdTableFormatClassic2, Style:=63, _ connection:="tblStudents", DataSource:=strFileName If UCase(txtPrint.Text) = "Y" Then Application.Dialogs(wdDialogFilePrint).Show Application.Dialogs(wdDialogFileSaveAs).Show End If End Sub |
Conclusion
Overall, I enjoyed building the solutions as it has helped me to develop a clearer understanding of how object oriented programming works. Although I had used all the Microsoft Office Suite applications before, terrifying visions still came to mind whenever the word ‘macro’ was mentioned. This was because older versions using a combination of keystrokes were embedded deep into my memory. The use of VBA definitely makes automation of tasks much simpler than in previous releases of Microsoft Office.
I am very happy with the design of the solutions, especially Task A, and I believe the components work well together leaving room for possible future developments. I am pleased to have gained the capability to adapt what I’ve learnt in class to different scenarios. This assignment has really given me an insight into how integrated the various Microsoft office applications are. The hidden power and functionality also surprised me immensely, and I shall never look at the Microsoft Office Suite the same again!
6. Bibliography
6.1 Books
Heathcote, P. M. Succesful ICT Projects in Excel (2002) 3rd Edition, Ipswich: Payne-Gallway
Jacobson, R. Microsoft Excel 2002 Visual Basic for Applications Step by Step Washington: Microsoft Press
6.2 Websites
http://msdn.microsoft.com/en-us/library [Accessed 2ndMay 2009]
http://msdn.microsoft.com/en-us/library/0x9tb07z.aspx [Accessed 6th May 2009]
http://msdn.microsoft.com/en-us/library/bb221539.aspx [Accessed 8th May 2009]
http://msdn.microsoft.com/en-us/library/bb178796.aspx [Accessed 9th June 2009]
http://www.tech-archive.net/Archive/Word/microsoft.public.word.vba.general/2008- 10/msg00524.html [Accessed 10th June 2009]
6.3 Online Articles
By Katherine Sanger, 2009. Advantages & Disadvantages of Microsoft Access [Online] (Updated 03 May 2009)
Available at: http://www.ehow.com/facts_4827300_advantages-disadvantages-microsoft-access.html
[Accessed 16 May 2009]
6.4 Other
Tutorials & Examples from the Barnet College Extranet (extranet.barnet.ac.uk)
Page of
This student written piece of work is one of many that can be found in our University Degree Software Engineering section.
Found what you're looking for?
- Start learning 29% faster today
- 150,000+ documents available
- Just £6.99 a month
