In: Computer Science
Sub HW4_1_1()
'a. Using the properties window in the VB editor assign
' the code name wsEx2 to the sheet with tab name Sheet1.
'b. Using VBA code and the code name for worksheet Sheet1,
' make this worksheet the active sheet.
'c. Declare a range variable and assign to this variable
' the range from A2 to A10 in the worksheet Sheet1.
'd. Write VBA code to create a range name and call it
“Scores”.
' This range name should refer to the range B6to B14 in
' the worksheet labeled Sheet2
'e. Write VBA code that uses an Excel worksheet function
' that calculates the weighted average score (i.e.,
' the dot product of the range defined in part c. and the range in
part d.
' and place the calculated value in cell A12 of the Sheet1
worksheet.
'f. Using VBA code change the background color of cell A12 to
green.
'g. Using VBA code change the font type in cell A12 to italic and
bold.
'h. Using VBA code change the format property of cell A12 so
that
' it is displayed as a number with exactly three digits to the
right
' of the decimal point and at least three digits to the left of the
decimal point.
End Sub
Solution:
..........................................................................................................................................................
VBA CODE:
Sub wxEx2()
'''''' Question b''''''''
' Making the sheet active
wsEx2.Activate
'''''' Question c''''''''
' Setting range
Dim r1, Scores As Range
Set r1 = Sheets("Problem 2").Range("A2:A10")
'''''' Question d''''''''
' Set range Scores
Set Scores = Sheets("Problem 3").Range("B6:B14")
'''''' Question e''''''''
' Weighted average
Dim val As Double
Sheets("Problem 2").Range("A12").Value =
Application.WorksheetFunction.SumProduct(r1, Scores) /
Application.WorksheetFunction.Sum(Scores)
'''''' Question f''''''''
' Chane background color
Sheets("Problem 2").Range("A12").Interior.ColorIndex = 4
'''''' Question g''''''''
' Make italic bold
Sheets("Problem 2").Range("A12").Font.Bold = True
Sheets("Problem 2").Range("A12").Font.Italic = True
End Sub