Analysis of Automation Tools - Essay Example

1. (a) It is possible to create fully functional macros by using the view however one needs to resort to using using the Visual Basic Editor to do dmanual coding. This is primarily when your conditions to perform a certain set of activities changes depending on various conditions. Record Macro function is unable to provide a user with such options. We will look into three examples where we have to rely on manual coding while creating Macros. Using loops and conditons - Using loops like Do...Loop Until are impossible when using record macro as Excel or other types of spreadsheets do not have such a feature built in to then. Lets consider a scenario where you need to enter a particular piece of information based on the number of rows with data in a certain column. You will have to repeat the process of entering data by hand against each cell to acomplish this. However your data might change making your recorded macro unusable. Using a Do - Loop Until function will solve your problem as you are now able to instruct the macro how many times a particular set of instructions need to be completed. Using Variables - You are not able to use a variable to store data using the record macro function. You will have to resort to use the VB editor once again. Apart from the fact that you are able to store information in these variables, a fuction called Evaluate in VBA will let you use MS Excel commands like concatenate etc within VBA. You need not use Excel to do calculations. Even referencing conplicated fuctions such as DSUM varies according to your data. Using properties of a Cell selection - Lets say you need the cell address of every cell containing the word "Penguin" and have that on a separate sheet. Record Macro will not be able to give you the solution whatsoever. Using VB Editor, you can use the Cell.Find function to search for the word "Penguin" along with the selection.address function and enter the cell address into a variable. A message box can then be called using the Msgbox fuction to display the value stored in the variable. This example contains three different offings (variable, Selection.Address and Msgbox) which the record macro function is unable to offer. 1.(b) The are many ways of accessing the library. The easiest is to press the F2 key from within the VBA editor. Others are by clicking the object library button on the toolbar or by clicking the Object Browser button in the view menu. The information provided by the library is quite large and in depth. It will provide the below information: Various Classes available Objects member available in a particular class The type of the object (Function, Property etc) The argument a function accepts along with the type of arguments Also the value type returned (Range, Long etc) As an example, if you search for "Offset" in the search box, you will find the following information in the display area: Property Offset([RowOffset], [ColumnOffset]) As Range read-only Member of Excel.Range This means that the function "Offset" is member of Class Range and Library Excel. The function accepts arguments in the form of the row number followed by column number and will treat it as a range. It is read only which means it can be used only to retrieve information but to enter any into the cell. 1.(c) VBA offers a number of debugging facilities. These are: Jump over: VBA will overlook a piece of code which has a single quotation mark in front of it. This way you can skip a particular line of code that you find to be causing an error. Run and Wait: VBA will allow you to select a line of coding by clicking the margin in front of it and highlighting it in red. It will run all the commands before the highlighted one and wait for you to check the highlighted one. You can then hover your mouse pointer over the highlighted code and the value of the result will be displayed. This is very useful while using variable. This method can be used for an entire selection of codes. 1.(d) Function mean(rng As Range) As Double Dim freq, total As Integer Dim r As Range total = 0 freq = 0 For Each r In rng total = total + r.Value freq = freq + 1 Next mean = total / freq End Function Line1: This function will accept "rng" as an argument as type Range. The data type of mean is double. Line2: This line will create two variables - freq and total as type Integer. Line3: Variable r is created as type Range Line4: Variable total created to carry initial value 0 Line5: Variable freq created to contain initial value 0 Line6: Beginning of the for loop. This loop will run for every value for the range stored in variable rng Line7: Variable total is increased by the value stored in the cell referenced by the current value of variable r. Line8: Variable freq increased by one every time this loop runs. Line9: End of loop if all values in variable rng have been passed on to variable r once and the loop has run for them. Line10: Function mean is calculated as value of variable total divided by value of variable freq Line11: End of function mean This function creates a function accessible via MS Excel. When "=mean(B3:B7)" is enter in cell D3, the range B3:B7 is passed as a range to variable rng defined in the variable. The For loop runs for a total of five times as this range has five cell references in it. For each time it runs, one cell reference is stored in variable r from the range stored in variable rng. The variable total which had an initial value of zero is incremented by the value stored in the cell currently referenced by the address stored by variable r. Variable freq which had an initial value of zero is incremented by 1 every time this loop runs. Code next tells the system that For loop ends here. If there are no further records in the variable rng, the For loop will stop. Function mean is now calculated as value of total divided by value of freq. This is passed on to Excel as a value which is entered into cell D3. Function mean will work in free form as long as the VBA code is present for that file. It will pick any range provided by the user and will function just like any other function of Excel. 1.(e) The function mod( , ) will accept numerator as the first argument and denominator as the second argument. The function will return the denominator as the result. When using in VBA the function needs to be modified as below: n Mod d; where n is the numerator and d is the denominator. The function offset( , ) accepts two integers or variables stored with integer values as arguments. The first argument is the row number and the second is the column number. The address is relative and the function will begin referencing from the cell highlighted in the worksheet. Example: Offset(1,0) will move the pointer to one cell down and zero cells to left. 1.(e).(i) Function parity(rng As Range) As String Dim r As Range Dim a As Integer For Each r In rng a = r.Value Mod 2 If a = 0 Then parity = r.Address Range(parity).Offset(0, 1).Value = "Even" Else parity = r.Address Range(parity).Offset(0, 1).Value = "Odd" End If Next End Function 1.(e).(ii) Function mean(cell1 as string) as Double Dim LastRow, right, total, freq As Integer Dim First, last, celllast As Long LastRow = .Cells(.Rows.Count).End(xlUp).Row LastRow = LastRow - 1 First = Evaluate("=Left(" & cell1 & ",1)") last = Evaluate("=Concatenate(" & First & "," & LastRow & ")") celllast = Evaluate("=Concatenate(" & cell1 & "," & last & ")") right = Evaluate("=Right(" & cell1 & ",1)") freq = lastrow - right For Each r In celllast total = total + r.Value Next mean = total / freq End Function 2. (a). Advantages of COP method are: COP doesn't attempt to create a chain of hierarchy like OOP (Object Oriented Programming) does. OOP will try to emulate of thinking pattern by creating grammar like syntax fashion. COP disregards that method and attempts to join different component of programming which join with each other and not follow hierarchy. Component oriented programming follows the idea of joining prefabricated component of software to form one complete package. Since the end result is unknown at the time designing the software, it is very important to ensure each component is able to survive on its own. The theory is similar to what is being following at the computer hardware industry. A computer can work perfectly well with minimal number of components. Additional components may enhance the system however not having doesn't make the system unusable either. Similarly COP ensures that various components apart from the core components may be added on an as and when required basis. Since COP is component based rather than object based, if makes the platform more reusable. Since the entire code need not be changed in COP, customization and simplified re-usability is easier to implement. OOP on the other hand follows hierarchy. A change in a particular portion of the code requires the entire code to be recalculated since OOP follows hierarchy. COP also makes the software more robust and fast. Since its component based, the local machine doesn't need to compile and run the entire code every time an object is called. Since COP doesn't follow the object path, only the component which needs to run at a particular time does. This also save space and time lap. Especially if deployed over the web. 2. (b). In designing this module I would be assuming that the caravan club's database already has the required information within its tables. Lets assume that the following tables already exist with the relevant data. If not they can be created or modified to match the same in essence. 1. A customer data table containing basics like Name, Contact Information, etc The must have is a membership identification number which will be used as a primary key. 2. An events data table which contain the membership id's along with rally types. We don't need anymore information as this will make the database slower. 3. An entertainment data table which contain the entertainment options per rally type. 4. A parking data table has to be created which will have the membership id number, the parking space number and the caravan's registration number (optional for additional security). The customer data, event data and the parking data tables will have the membership id as the primary key to avoid duplication. The entertainment table need not have a primary key since a single entertainment type maybe be associated with multiple rally types and vice versa. If the client need the parking lot details to be entered manually, a form can be created which will update data into the parking data table. A simple data entry table can be created within MS Access for this. However for to minimize the risk of giving unwanted access, a web form can be created using XML or PHP which can converse with Access using MySQL. Since Access is an RDBMS, it understands SQL syntax and this arrangement will be fully compatible. This structure utilizes the COP principle of re-usability. Since the table's are reduced to the minimum and only relevant information needs to be entered by the parking lot details, that same form and table's are being re-used. 3. For this scenario we will look at the database design first and then at the website design. The database will have the following tables. IdeaInfo, IdeaDetail, IdeaVote Table IdeaInfo will contain the following columns: IdeaID : Auto Number Field, Primary Key IdeaDate : System generated date from the website when user logs in an idea. IdeaDept : User input from form. User selects from a drop down while loggin the idea. Department Idea targeted towards. IdeaCategory : User input from form. User selects from a drop down while logging in the idea. Category or type of the Idea. example. Improvement, New feature etc. Ideadet will have the following columns: IdeaID : constant per Idea from website. Website will return IdeaID from IdeaInfo after logging in an idea and enter the ID number in this table. IdeaSubject : User input from form. Subject of the Idea. IdeaText : User input from form. Description of the Idea. IdeaCost = User input from form. Cost estimation to implement idea. If any. IdeaVote will have the following columns: IdeaID : constant per idea from website. Website will return IdeaID from IdeaInfo after logging in an idea and enter the ID number in this table. Promote : numeric field incremented by 1 from the website Demote: numeric field incremented by 1 from the website TotalVotes : sum of Promote and Demote ipaddress : IP address of the system from where the vote was launched. Website Structure The website will have the following pages. Homepage - This is the main page. The script will import the following columns from their relevant columns and display on the page: IdeaID, IdeaSubj, IdeaText, IdeaCost, Promote, Demote, Promote percentage This will display all Ideas suggested in the past. The use will be able to see the Idea number, subject, the description, any cost, total promoters and demotors and promoter percentage (popularity) Two buttons with "Thumbs Up" and "Thumbs Down" will be against each idea. User is able to vote once and his/her IP address along with the vote is entered in the IdeaVote table. Whenever a vote is cast, the website will check for the IP address generating the request and allow or deny the vote to be counted accordingly. New Idea page - This page will have a form. The user is required to enter the subject, description, any costs, Targeted department and Category in here. Department and Category will be drop down menus. When a user submits a new idea, a code in the system will connect to the MySQL database and update the relevant data in the IdeaInfo table. It will then copy the auto generated number and enter it into the Ideadet along with the rest of the information like subject, description etc. Reports Page - Will run a query based on what the user selects. The result will be read only. Options will include report by date range (logged), category, Department, Cost. This will let the company's various departments search through the ideas without giving them access to the database. Restrictions to the website's design. This website may fail any unforeseen circumstances but below are the one's where I would expect it stop working. 1. Slow network connection. The web page may expire before data gets updated. 2. Proxy settings. The network proxy might mask the system IP address with the proxy IP address causing none of the users to vote. The site will be more robust if only once channel of communication is open at one time to the database. Although MySQL server will let one update at a time to avoid data duplication, restricting one user access at a time from the website will prevent the user to see error codes. Since the IP address is logged, this site is secure. Thank you. Read More