Accountancy 2258

Written Macro Assignment Solutions True/False Questions. Read each statement carefully, and neatly write the word TRUE on the line next to the statement if you believe the statement is true, or neatly write the word FALSE on the line next to the statement if you believe the statement is false. FALSE1. Do†¦Loops are used in macros to repeat lines of code a specified number of times. TRUE2. The area where macro code is displayed in Visual Basic is called the Code Window. TRUE3. A macro name is not required when creating a private macro. TRUE4. When declaring a variable, Dim stands for â€Å"define in memory†. FALSE5.Objects, Variables, and Actions are used to write macro code. TRUE6. One way to create a new macro is to use Record New Macro. TRUE7. The If†¦Then†¦Else†¦End If code allows code to be conditionally executed depending on whether a specified logical condition has been met. FALSE8. The last line of code for a private macro should be End Private Sub. FALSE9. The formula for a user-defined function must be entered in quotation marks. TRUE10. A public macro/procedure is activated by the user pressing the shortcut keys or clicking on a button. Problems. Read each problem carefully and follow the instructions given. 1.Open up Excel. Go to Cell E9 and use the Name Box to name this cell â€Å"Winter†. Then go to the Developer tab on the Ribbon, and click on the Record Macro button in the Code group. Name your macro â€Å"Referencing† and give it a shortcut key of â€Å"R†. After clicking OK to close the Record Macro dialog box, do the following: †¢ Select Cell C7 using absolute referencing (do not select the Use Relative Reference button). †¢ Using the drop-down arrow to the right of the Name Box, select â€Å"Winter†. †¢ Click on the Use Relative Reference button in the Code group on the Developer tab to use relative referencing, and then select Cell A4. Click on Stop Recording button in the Code group on the Developer tab. After you have performed these steps, view your code in Visual Basic Editor, and answer the following questions: a. What code was written when you selected Cell C7 using absolute referencing? Range(â€Å"C7†). Select b. What code was written when you selected the cell named â€Å"Winter† using the Name Box drop-down menu? Application. Goto Reference:=†Winter† c. What code was written when you selected Cell A4 using relative referencing? ActiveCell. Offset(-5, -4). Range(â€Å"A1†). Select Remember that ActiveCell.Select is the basic format used when writing macro code using relative referencing. Also, please note that when writing out the VBA code to move from one cell to another using relative referencing (as we did above) the Range(â€Å"A1†) portion of the code is not necessary. It shows up automatically (as displayed above) when we are recording a macro and moving from one cell to the next using relative r eferencing, but if you are just writing out the macro code, you do not need to include that portion of the code. 2. Assume Cell B3 is the current cell you are in on your Excel spreadsheet.Write the appropriate VBA code to perform the following: a. Move from Cell B3 to Cell E8 using relative referencing ActiveCell. Offset(5,3). Select Note that I did not include Range(â€Å"A1†) before . Select, as this is not necessary (see my note above). b. Move from Cell B3 to Cell A1 using relative referencing ActiveCell. Offset(-2,-1). Select c. =Move from Cell B3 to Cell J10 using absolute referencing Range(â€Å"J10†). Select 3. Write the necessary code to define a variable in Visual Basic named Age to store whole numbers with no decimal places.Dim Age As Integer 4. Write the necessary code to define a variable in Visual Basic named Expenses to store dollar values. Dim Expenses As Currency 5. Write the necessary code to define a variable in Visual Basic named City to store text values. Dim City As String 6. Write the appropriate code to create a dialog box asking the user to enter their employee ID number. Give the dialog box the title ID. Your code should place the user’s response in Cell A5 on your worksheet. Then create a loop around your code that will loop while Cell A5 is blank.Do Range(â€Å"A5†). Value = InputBox(â€Å"Please enter your employee ID number†, â€Å"ID†) Loop While Range(â€Å"A5†). Value = â€Å"† 7. Assume the employee ID number entered in the above dialog box is expected to be greater than or equal to 1 and less than or equal to 99999. Rewrite your loop so it requires the user to enter a number that is equal to or between 1 and 99999. Do Range(â€Å"A5†). Value = InputBox(â€Å"Please enter your employee ID number†, â€Å"ID†) Loop Until Range(â€Å"A5†). Value >= 1 and Range(â€Å"A5†). Value 9999 and ActiveCell. Value