Excel VBA Practice 6 - VBA For Excel

  1. Open a new workbook. Using the default Sheet1,enter the values into the cell range A1 to A5 the numbers 3,11,9,12, and 5 respectively. Now enter the following macro using the VBE:
  2. Sub GreaterThanTen() Dim thisCell as Object For each cell in Range ("A1:A5") If thisCell >10 Then MsgBox "Another One Found" thisCell.Font.Bold =True End If Next End Sub

    Explain what you think this program may be doing. Run the macro and check, using the data given to see that it works properly.

  3. Using the macro that you have written in Exercise 1, describe each line of code that contains an object reference. Identify properties and methods in the references.
  4. Use the Object Browser to look up the Range object and find out about the property or method called Cells. State whether it is a property or a method.
  5. Given the same worksheet data as in Exercise 1, state the following property value:
  6. Range ("A1:A5").Cells (3)
  7. Explain what the following section of code is doing:
  8. Charts (1).Activate With ActiveChart .Type = xlLine .HasTitle = True .ChartTitle.Text = "February Week Sales" End With
  9. Open the SALESMAN workbook and make the weeklysales sheet active. Now create and run a macro to reset the color of all the cells in the week_sales range to the colour light grey. (Hint: Use the With End With construct that was used in Listing “Validating Data in the week_sales Range”,and set the ColorIndex = 15). Check to see that it has run correctly.
  10. Open the SALESMAN workbook and make the weeklysales sheet active. Now design pseudocode, and from it, create a new macro that will check each cell in the end_month_sales range and if their sales have exceeded 200,then set a border of medium thickness around the cell. Create another macro using the SALESMAN workbook that will check the same as above, but this time instead of setting a medium thickness border around the end_month_sales cells that are greater than 200; it will instead set the border around the corresponding salesperson. (Hint: Use the same macro code as above, but this tim use the Offset method to reference the corresponding salesperson position in the worksheet).

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

VBA For Excel Topics