Automation Tools Essay Example | Topics and Well Written Essays - 1500 words

Automation Tools - Essay Example 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. ... 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