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 to be identified.
I then went into the visual basic editor through the developer tab and created the form as the prototype. By double clicking the show button which I had added, I was able to go into the code view and determine what is to happen at the click event of this object. I continue to add code as follows:
A global variable of type Range is declared.
This part of the code is for the click event of the ‘show’ command button. It enables the ‘models’ list box to be populated from a range of cells (E5-E10) once the button is clicked. The Count property is used within the ‘for loop’ to determine the position of the items that are to be added to the list box.
This part of the code is for the click event of the list box. The list item index is set, in order to be able correspond with the rest of the objects on the form and in order to update relevant values. The ‘Model’ textbox is set to a variable named ‘y’, ‘y’ is equal to a cell of the range declared, positioned at the list index of the ‘Models’ list box plus 1.
This code is for the click event of the ‘update’ command button. It enables, at a click of the button, for an items stock quantity to be updated on the worksheet according to the inputted number of stock sold. The update stock quantity value is also shown in a textbox named ‘txtQty’.
The relevant worksheet was first declared as a variable so that various properties and methods could be explored to solve the business problem. A range type variable was also declared to compare it to the range object of this worksheet already named ‘Models’. This was done by use of a ‘for each’ loop with a nested if statement. If the value of a cell in the range was equal to the text property of the ‘Model’ textbox then the ‘txtQty’ textbox was updated according to the value inputted in the ‘Sold’ textbox.
2.4 Functional Testing
2.5 Logical Testing
The following two screenshots show that tests5 and 6 have passed. They are displaying the expected remaining stock in the textbox and updating the inventory worksheet with that value.
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.
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.
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. 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
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:
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.
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.
A For-each loop is used to delete the existing students displayed in the word document and an application dialog box is also set to open mdb files. If an mdb file is opened then the data from a table named ‘tblStudents’ is inserted into the word document. If the ‘Print’ textbox is set to Y then the print and save as dialogs are shown after.
3.4 Functional Testing
4. Task C
4.1 System Requirements
MET university require:
- An access database to contain student data and their grades
- A word template to allow a list of grades for a student
- Data transfer from the database to a word file
- Print and save functionality
- Send a copy by outlook 2007
- Allow a word document to be transferred into a PowerPoint presentation
4.2 Pseudocode
- Click Event Procedure New Button
Declare letter as document type variable
Prompt for student name
Insert Student name into paragraph 5 of letter
Insert grade data from tblGrades to student
End
- Click Event Procedure Email Button
Declare variables
Fill an array with names from outlook contacts folder
Fill the combo box with the names
Set email variable to the combo box value
Convert letter to html
Send letter to email variable via Microsoft Outlook
- Click Event Procedure Convert Button
4.3 Implementation
The above and following code implements at the click event of the ‘New’ command button. The vbatask3 document is set to a variable and the range property is used to insert text from the form into paragraph five of the document.
Firstly, a For-each loop is used to delete the existing grades displayed in the word document. Then an application dialog box is also set to open mdb files. If an mdb file is opened then data from a table named ‘tblGrade’ is inserted into the word document. ‘Print’ and ‘save as’ dialogs are set to be shown after this process.
This code enabled me to send the letter in html format to a variable named recipient that was defined to the outlook contact.
The word document has to be formatted into headers and paragraphs. In the word document file I created a simple GUI with one command button named “cmdConvert”. At the click event of this button I used the presentIt method on the active document to display the formatted text from the word document as a PowerPoint presentation.
I also declared a variable of object type and set the object to a PowerPoint application. The ‘saveAs’ method was used to save the created presentation to a set directory, and I used a ‘with’ statement on the PowerPoint application object to ensure the presentation to be saved is the latest one opened. The quit method closed the PowerPoint application and the value of the powerpoint application variable is set back to its default value.
4.4 Testing
Test No. 23 passes, however if multiple files are open, the code may cause the wrong file to save. Therefore it is a must to close all open presentations before clicking on the ‘convert’ button. See the screen shots below for the results of this test.
Headings and other formatting were set on the task3 document file. I then ran the visual basic form and clicked on the ‘convert’ button and a PowerPoint presentation opened and closed. I checked the H:\drive to ensure a temp.ppt file was created and after opening it I found the following result; a presentation with the correct headings and contents spread over two slides.
5. Evaluation
5.1 Evaluation of the Solutions
I am happy that the requirements have all been met on the three tasks except from the last requirement of task A. Task A was a longer task than the other two and I could not get around to creating a website. However, after creating a website making use of frames in FrontPage, the following code could be used in a new button click event so that the bottom frame of the site is able to be edited:
Obviously the source range can be modified to suit the requirements, as can the filename.
The user forms I created are neat, tidy and consistent throughout. I put this down to the designing of prototypes in the less talked about application from the Office Suite, the CASE tool- Microsoft Visio. Another strong point is that a list box was used within part 2 of task ‘A’ to control the input of data.
One of the weaknesses of the solutions is due to fact that the VBA object library differs from version to version of Microsoft Office. This fact alone can potentially compromise the future development of the solutions. To combat this point, I tried to use the most simple objects and easiest to understand methods.
The appearances of the application forms were dull with a standard grey colour and no images. The default font size and type were used which could render the program inaccessible to those with poor sight. Although I did end up adding some colour to the worksheet used in the first part of Task A, I do feel more could have been done to create more visually stimulating solutions. That being said, I consider the functionality aspect to be more important, and if the solutions were lacking in this regard then I would have been more disappointed.
I am aware that the following improvements could be made to the various solutions:
- Images could be added to make the design-look more professional.
- Accessibility could be improved by investing more effort into the tab order.
- Presentation could be enhanced by focusing more on the font sizes and colours.
- Security features could be added such as a password function displaying at start-up.
5.2 Self Evaluation
Having studied VB.NET in the first semester, I considered myself to have the technical know-how to tackle this assignment. However, the assignment comprised of three different business solutions each with their own requirements, making the understanding and fulfilment of each of the requirements exceptionally harder.
Working with Microsoft Outlook presented to me a double challenge. Firstly, the college network did not allow for students to use Outlook; the solution, I had to go elsewhere to develop and test this feature. Also, my personal email address did not support outgoing mail using the Outlook application. I therefore had to sign up to an alternative email provider.
I had some trouble in the development stage of task ‘A’. The code I had created would not work using two worksheets in one spreadsheet. I couldn’t see a problem within the code so I tried using the same code in two different spreadsheet files each with one worksheet. This did work, however I never found the answer to this problem and I am hopeful that in the future I can get to the bottom of this small bug.
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)
