need help writig VBA code This is how the EmployeeScore.xlsx looks like Download

Question

need help writig VBA code

This is how the EmployeeScore.xlsx looks like

Download the file EmployeeScores.xlsx and complete the following task. Start by opening the file and saving it as a Macro Enabled Workbook (file extension .x1sm). Name this spreadsheet with your network login followed by_103.xlsm. For example, if your network login was barn4520, then the file should be named barn4520_103.xlsm. Remember to save your work frequently in case there are problems Put all items below in the same subroutine. Write the code, don't record a macro 1. Use VBA to name the following ranges with the specified names: ell Al as Title, the headings in row 3 as Headings, the employee numbers in column A as EmpNumbers, and the range of scores as Scores. 2. Complete the following items by using VBA to reference the named cell ranges: 1. Boldface the font of the label in the Title cell, and change its font size to 14 2. Boldface and italicize the headings in the Headings row, and change their horizontal alignment to right justified 3. Change the colour of the font for the employee numbers in the EmpNumbers range to blue (any shade you like) 4. Change the background (the Interior property) of the Scores range to gray (any shade you like) 5. Enter the label Averages in cell A22 and boldface it. 6. Enter a formula in cell B22 that averages the scores above it. Copy this formula to the range c22:F22 (using VBA) This Extra Mile" icon represents an item that may challenge you a bit and is worth attempting, but will not have any impact on your grade. If you do it, you don't get any extra marks and if you don't do it, you don't lose out either, try. Extra Mile: Try altering the subroutine to work regardless of the number of scores or the number of employces. This means dynamically checking to see how many rows and columns are in use before creating the range names in step 1. Get your task marked first before attempting this Extra Mile, or you can try it at home on your own.

Explanation / Answer

Steps to write a VBA Macro or module are:

Right click on the sheet(bottom)àselect view codeà write VBA code on the windowàrun it.

1).VBA code :

Sub name()

Dim rg As Range

Set rg = Range("A1")

rg.name = "Title"

Dim rg1 As Range

Set rg1 = Range("A4:A21")

rg1.name = "EmpNumbers"

Dim rg2 As Range

Set rg2 = Range("B4:F20")

rg2.name = "Scores"

End Sub

2).Private sub style()

Dim sty As Range

Set sty = Range("A1")

sty.Font.Bold = True

sty.Font.Name = "Arial Black"

sty.Font.Size = 14

Dim sty1 As Range

Set sty = Range("A3")

sty.Font.Bold = True

sty.Font.italic=True

sty.Font.align=Right

Dim sty2 As Range

Set sty2 = Range("A4:A21")

Sty2.Font. Font.Color = RGB (0,0,255)

Dim sty3 As Range

Set rg2 = Range("B4:F20")

rg2. BackgroundColor = RGB(128,128,128)

Dim sty4 As Range

Set sty4 = Range("A22")

Sty4.Value = "Average"

sty4.Font.Bold = True

Dim sty5 As Range

Set sty5 = Range("B22")

Sty4.Value = [Average(C22:F22)]

End sub

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.