• Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month
Page
  1. 1
    1
  2. 2
    2
  3. 3
    3
  4. 4
    4
  5. 5
    5
  6. 6
    6
  7. 7
    7
  8. 8
    8
  9. 9
    9
  10. 10
    10
  11. 11
    11
  12. 12
    12
  13. 13
    13
  14. 14
    14
  15. 15
    15
  16. 16
    16
  17. 17
    17
  18. 18
    18
  19. 19
    19
  20. 20
    20
  21. 21
    21
  22. 22
    22
  23. 23
    23
  24. 24
    24
  25. 25
    25
  26. 26
    26

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)

...read more.

Middle

image12.png

2.6 Implementation Part 2

image13.png

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.

image14.png

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.

image02.png

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

image03.jpg

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

image04.png

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

...read more.

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

...read more.

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

Not the one? Search for your essay title...
  • Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month

See related essaysSee related essays

Related University Degree Software Engineering essays

  1. Programming Concepts. Andora Video is a small shop selling videos. The owner, Raul, wishes ...

    as this will enable us to better understand the logic needed behind such a project. REQUIREMENTS Input: String vdID, vdTitle, int vdQty, double vdPrice, final double vatRate = .175, disFor15Plus = 0.25, disFor10To14 = 0.17, disFor5To9 = 0.12 Output: String vdID, vdTitle, int vdQty, double vdPrice, vdSubtotal, dSubtotal, vdTotal, discount, VAT Process: vdSubtotal = vdQty*vdPrice discount=vdsubtotal*disFor...

  2. Rich Internet Applications

    frameworks, servers, and services as a complete solution for building RIAs, or use them in combination with other familiar web technologies.' [http: 4] Figure: 1 Adobe platform for RIAs [http:5] There are other technologies that can be linked in with some of the above technology to help in creating RIA's

  1. Systems Development and Implementation

    With some notable exceptions, firms in the industry generally specialize in one or the other. The core competencies for providing applications and providing infrastructure are different. Applications focus the value proposition on end users, and infrastructure provides value primarily to application developers and to operators.

  2. 3ISE517 Internet Application Programming - CGI

    The database engine using CGI executes a CGI script which would then process the request and return the results to the web browser. As the script is being processed on the web server itself, this technique is known as server-side scripting.

  1. 3SFE504 - Object Oriented Programming - Linear and Independent Hash Table code

    const; void printList() const; bool isln (const int) const; bool isEmpty() const; private: Definition* head; }; //List.cpp #include<iostream> #include "List.h" #include <string> using namespace std; List::List() { head = NULL; } List::~List() { } bool List::isEmpty()const { return head == NULL; } void List::insertAtHead(const string value)

  2. AIBO : A TOOL OR A TOY

    AIBO: The Wonder Dog In this category of robots, nothing has become as successful as the Sony AIBO. Sony's AIBO is an acronym for Artificial Intelligence roBOt. It also means 'friend' or 'companion' in Japanese. AIBO was first released in the year 1999.

  1. Evaluating Websites Usability - comparing 5 websites.

    Staff Directory 3. Phone 4. Fax 5. Address Browser Compatibility: 1. Screen shot were taken through browsershots.org in FireFox18.0, Safari 6.0 , and Google chrome 23.0 2. All screen shots remained the same throughout all. Knowledge of Users: 1.

  2. Abstract This report is dedicated to demonstrating and critically reviewing new features and developments ...

    1. Performance 1. Developing Efficient Background Processes for Windows [15] Windows 7 significantly improves the Service Control Manager (SCM). The improvements are introduced to allow services to be started only when they are required and to help reduce the number of services that use Automatic Start.

  • Over 160,000 pieces
    of student written work
  • Annotated by
    experienced teachers
  • Ideas and feedback to
    improve your own work