Automation Tools - Math Problem Example

Question a VBA might be created within Excel by recording a macro. Even so it often proves necessary to resort to hand coding. Provide threeexamples where hand coding rather than recording a macro is required to accomplish a particular task. You might wish to consider for example the use of variables or how 'selection' and 'iteration' are accomplished. Answer 1(a): 1. The below code snippet puts the system's current date and time value in cell B1 of active sheet, whenever the workbook, containing this code fragment, is opened. Private Sub Workbook_Open() Dim d As Date d = Now() ThisWorkbook.ActiveSheet.Range("B1").Value = d End Sub Output: 2. The below code snippet selects the used range of the active sheet whenever the workbook, containing this code fragment, is opened. Private Sub Workbook_Open() ThisWorkbook.ActiveSheet.UsedRange.Select End Sub Output: 3. The below code snippet creates a list of numbers (1 to 5) in Sheet1, whenever the workbook, containing this code fragment, is opened. Private Sub Workbook_Open() For counter = 1 To 5 ThisWorkbook.Sheets("Sheet1").Cells(counter, 1).Value = counter Next counter End Sub Output: Question 1(b): VBA is supported by an extensive library of classes. Describe how this library is accessed and what information relating to the various classes is provided. Answer 1(b): To view the list of classes available for VBA, open an excel workbook and go to Tools Macro Visual Basic Editor (shortcut key: Alt + F11) option. A new, above shown window, which is actually a code editor for visual basic (VBA IDE), will appear. In this new window, go to option View Object Browser (shortcut key: F2). This action will change the interface of the window; select 'VBA' from the drop-down list of combo box (encircled in red) as shown in figure below. The pane, labeled 'Classes' (encircled in blue), displays the name of all objects that are contained in the library, VBA; these objects are called classes, modules and enumerations. These objects have constants, properties and functions, which are listed in pane, labeled 'Members of XXXXX' (encircled in green), through which they define their attributes and set of actions that they can perform. For example, in above figure, it can be noted that there are only two constants, vbModal and vbModaless, are defined in enumeration, FormShowConstant. Also note that the summarized detail for the selected member, vbModaless, is also displayed in the bottom pane (encircled in pink). Question 1(c): What debugging facilities are available within the Visual Basic Editor (VBA IDE).Describe how they might be used to debug code Answer 1(c): In Visual Basic Editor, all debugging facilities can be accessed through options that are available in 'Debug' menu; this menu contains the following menu items: 1. Step Into Executes code one statement at a time; when not in design mode, Step Into enters break mode at the current line of execution. If the statement is a call to a procedure, the next statement displayed is the first statement in the procedure. At design time, this menu item begins execution and enters break mode before the first line of code is executed. If there is no current execution point, the Step Into command may appear to do nothing until something is done that triggers code, for example clicking on a document. 2. Step Over This option is similar to Step Into; the difference in use occurs when the current statement contains a call to a procedure. It executes the procedure as a unit, and then steps to the next statement in the current procedure. Therefore, the next statement displayed is the next statement in the current procedure regardless of whether the current statement is a call to another procedure. This option is available in break mode only. 3. Step Out Executes the remaining lines of a function in which the current execution point lies. The next statement displayed is the statement following the procedure call. All of the code is executed between the current and the final execution points. Similar to Step Over, this option is also available in break mode only. 4. Run to Cursor When an application is in design mode, use this command to select a statement further down in code where execution needs to stop. An application will run from the current statement to the selected statement and the current line of execution margin indicator appears in the Margin Indicator bar. This command can be used, for example, to avoid stepping through large loops. 5. Add Watch Displays the Add Watch dialog box in which a user-defined expression, a watch expression, needs to be entered that enables to observe the behavior of a variable or expression. Watch expressions appear in the Watch window of the Visual Basic Editor and are automatically updated when an application enters break mode. The Watch window displays the value of an expression within a given context. Watch expressions are not saved with the code. 6. Edit Watch Displays the Edit Watch dialog box in which any existing watch expression can be edit or even deleted. This option is only available when the watch is set even if the Watch window is hidden. 7. Quick Watch Displays the Quick Watch dialog box with the current value of the selected expression; this command checks the current value of a variable, property, or other expression for which watch expression has not been defined. 8. Toggle Breakpoint Sets or removes a breakpoint which is actually a selected program line at which execution needs to be automatically stopped. Neither can they be created on lines containing non-executable code such as comments, declaration statements and blank lines, nor can they be saved with code. 9. Clear All Breakpoints This command removes all breakpoints that are used through out the project. However, still there can be interruption during execution of application, in case, if any watch expression has been set or the option Break on All Errors in the General tab of the Options dialog box has been selected. Once an action has been carried out by this command, it cannot be undo. 10. Set Next Statement Sets the execution point to the line of code in which the cursor is located. A different line of code can be set to execute after the currently selected statement by selecting the line of code that needs to be executed and choosing the Set Next Statement command or by dragging the Current Execution Line margin indicator to the line of code that needs to be executed. Through this option, a line of code located before or after the currently selected statement can be chosen; this means that no intervening code is executed when the code is run. By the help of this option, statements within the current procedure can be rerun or skipped intentionally, however, this option can not be use against statements that are located in different procedures. 11. Show Next Statement This option highlights the next statement to be executed and places the cursor on the line that will execute next. It is available only in break mode. Question 1(d): Consider the following VBA user-defined function: 1 Function mean(rng As Range) As Double 2 Dim freq, total As Integer 3 Dim r As Range 4 total = 0 5 freq = 0 6 For Each r In rng 7 total = total + r.Value 8 freq = freq + 1 9 Next 10 mean = total / freq 11 End Function lf = mean(B3:B7) is entered in to cell D3, the average of the numbers in the range 83 to 87 inclusive is produced. Explain the purpose of each line in the code. Answer 1(d): Line 1: Begin of function's, mean, block; the syntax starts from keyword Function, followed by name of function, mean and list of argument(s), rng of type Range, in case of our example. The list of arguments is followed by keyword As and then type of the value that the function returns; in our example, the type of return value is Double. Line 2 and 3: Declaration of variables; in this case two variables, freq and total of type Integer, and one variable, r, of type Range are declared. Line 4 and 5: Initialization of variables; both Integer variables, in our example, are initialized to zero. Line 6: Begin of loop's, For, block. Line 7: Adds the value stored in variable total and value that is represented by the range variable, r. The value that results by addition of two above variables is stored in the variable, total; the previous value of variable is overwritten with this new value. Line 8: Increments the value stored in variable, freq, by one. Line 9: End of loop's, For, block. Line 10: After the loop's execution is finished, the final value stored in variable, total, is divided by the final value in variable, freq. The outcome value is returned by the function, which gets displayed in cell D3. Line 11: End of function's, mean, block. Question 1(e): Explain the action of following functions: mod( , ) offset( , ) (i) Write a function parity(rng As Range) As String to step through each cell in the range parameter and enter either 'odd' or 'even' in the adjacent cell to the number according to whether it is in fact odd or even. (ii) Rewrite the function given in (d) to take the cell address of the top of the column of values and return the average of the column where the number of values in the column is unknown. Comment on the robustness of your functions i.e. what might cause them to fail. Briefly describe how you might improve them. Answer 1(e): mod( , ): Returns the remainder after a first argument is divided by a second argument; both arguments must be numeric. offset( , ): Returns a reference to a range that is a given number of rows and columns from a given reference. Function (i): Function parity(rng As Range) As String Dim r As Range For Each r In rng If Application.WorksheetFunction.IsNumber(r) = True Then If r.Value Mod 2 = 0 Then Cells(r.Row, r.Column + 1).Value = "Even" Else Cells(r.Row, r.Column + 1).Value = "Odd" End If Else Cells(r.Row, r.Column + 1).Value = "Not a number" End If Next End Function Possible cause(s) of failure and way(s) to improve: If any cell contains non-numeric value in the given range, then it will cause runtime error. To avoid this, a check can be added as highlighted in yellow in the above code snippet. We can also intimate to the user by displaying an appropriate message, for example "Not a number", in adjacent to those cells that contains non-numeric value. Function (ii): Function mean(rng As Range) As Double Dim freq, total As Integer total = 0 freq = 0 Do If Cells(rng.Row + freq, rng.Column).Value = "" Then Exit Do If Application.WorksheetFunction.IsNumber(Cells(rng.Row + freq, rng.Column).Value) Then total = total + Cells(rng.Row + freq, rng.Column).Value freq = freq + 1 Loop If freq > 0 Then mean = total / freq Else mean = 0 End If End Function Possible cause(s) of failure and way(s) to improve: If any cell contains non-numeric value in the given range, then we can skip such value to avoid runtime error or we can simply stop the execution of the program; in our example, we simply skip non-numeric values. Also, a runtime error can propagate if the divisor is zero while returning the final value. To avoid both of these possibilities, code highlighted in yellow must be added to the above code fragment. Question 2(a): What advantages are to be gained by using a component oriented programming (COP) approach to software development Identify the core principles of COP and in your own words briefly explain the benefits to be gained by adhering to these principles. Answer 2(a): Component Oriented Programming (COP) is an approach to development of software systems out of existing components, instead of building them from scratch. The core motivation behind COP is the "composition" of software systems through the use existing "components"; in other words, it emphasizes on re-use of software components. However, components are also considered as units of extension as they may provide the services to extend the functionality of application. This approach has many benefits over the object oriented programming, few of which all listed below: Emphasizes on reuse of software components, thus reducing the amount of time for development of software system. Component vendors could concentrate on their areas of expertise without having to actually produce applications to survive. Application vendors could concentrate on the needs of their users without having to become experts in all the areas their application touches upon. Users could expect higher-quality applications at lower prices since the cost savings and productivity gains would "trickle down" to them. Enables users to purchase the "parts" for desired software systems from various vendors. Specifically, users can in principle obtain a complete system without being tied to application vendors. Question 2(b): A caravan club maintains an MS Access database that records details of the membership, the season's rallies (when members come together at a particular caravan site) and any entertainments organized for a particular rally. A rally organizer would like to produce a notice that indicates which members will be attending and their reserved plot (where they will park their caravan) and for which entertainments they have booked. Accordingly the notice might comprise a series of tables containing members' names and possibly other information relating to a particular member. Consider the module coursework and describe how you might construct a Visual Basic.Net client to make use of appropriate MS application objects to produce the desired notice. Describe, in particular, how applications objects are incorporated in to the client code. Do not attempt to program this application but you might give examples of how application objects declared and instantiate. Answer 2(b): To create a notice, we may require to use object of MS Access in our .NET application; to use this object, we must first declare it in the application using the following syntax: Dim oAccess As Access.Application The above declared object is still of no use, as it needs to be initialized as illustrate below: oAccess = New Access.Application() Similarly, we may use MS Outlook objects in this application to send the notices to the members through email. Dim oOutlook As Outlook.Application oOutlook = New Outlook.Application() Question 2(c): Your boss has asked you to set up a web site where employees can make suggestions for improvements to the work place. Submissions will be anonymous but he would like everyone to be able to see all the currents suggestions and to vote on them. The page that shows suggestion should show them in date order with the most recent at the top. To comply with company policy, the site must be implemented using PHP and MySQL. You should create an initial design for the site. In particular, you should describe: The structure of the MySQL database in terms of the tables and fields it will use. The web pages that users will be able to see and how they will be organized. How the pages will be implemented in PHP. You do not need to code the pages in detail, just say what tasks they will need to perform and in what order. Any steps you will take to make sure that the site is robust and secure. Answer 2(c): The structure of the MySQL database in terms of the tables and fields it will use. The required tables, along with their fields, are depicted below: The relationship between these two tables is illustrated as under mentioned: The web pages that users will be able to see and how they will be organized. There is one web page required, MainPage.php. The users will not able to view all the posted suggestions (20 posts at a time) but they will also be able to post their own suggestion as well as vote for posted suggestions. How the pages will be implemented in PHP. You do not need to code the pages in detail, just say what tasks they will need to perform and in what order. Before a page is rendered by browser, the following tasks must be carried out: 1. Execute a query to fetch first 20 most recent suggestions through the posting date. 2. Execute another query to fetch count of votes for each record (suggestion) that is retrieved in the above step. 3. Populate the suggestion in the associated frame. 4. Display the count of votes against each suggestion. 5. Display the captcha/security code below the text area in which the user can type the text for suggestion. Any steps you will take to make sure that the site is robust and secure. 1. Before posting suggestion, anonymous user must enter captcha /security code to minimize risks of scripting attack. 2. The web application must perform basic input validations like check for null and invalid characters which cannot be stored or accepted by database. Read More