need help on excel based programming

EET 1003

Name: _______________Section:___

Assignment # 11:

Refer to -

Book: Microsoft Excel VBA Programming for the Absolute Beginner

Chapter: 3

Recorded Lecture: Lecture 13, 14 (eet100313.html, eet100314.html)

Power Point Presentation:

Lecture13_Excel_VBA_04.pptx, Lecture14_Excel_VBA_05.pptx

Objective:

To familiarize with Decision structures, the If..Then statement, and the If..Then..Else

statement.

1. Open the file Practice If_Then_.xlsx

2. Open VBE

3. In sheet1 (bonus) code, write a procedure that obtain earnings from the Earning column in the worksheet, calculates bonus, and displays it in the corresponding cell in

the Bonus column. The bonus should be calculated as follows:

 If Earning is more than $100,000, the bonus is 15%

 If Earning is more than $80,000, the bonus is 12%

 If Earning is less than or equal to $80,000, the bonus is 10%

4. You need to calculate the bonus for rows 4 thru 20 (Use If…Then…Else structure)

Option Explicit

Dim RowNumber As Integer, Bonus As Currency, Earnings As Currency

Sub Calculate_Bonus()

MsgBox ("enter the Row Number to calculate bonus")

RowNumber = InputBox("row number is ")

Earnings = Cells(RowNumber, 5).Value

If Earnings > 100000 Then

Bonus = Earnings * 15 / 100

ElseIf Earnings > 80000 Then

Bonus = Earnings * 12 / 100

Else

Bonus = Earnings * 10 / 100

End If

Cells(RowNumber, 6).Value = Bonus

End Sub

5. Update the code and run the procedure

6. Update the code and run the procedure.

7. Save the file as a macro enabled excel file, and close VBE

8. Compare and worksheet with the exhibit shown

9. Update and close the workbook