Question

EXCEL VBA

1. ABC company gives discount as follows:
   Purchase > $400                                                                     Discount rate = 5%
   Purchase between $300 and $400                                          Discount rate = 4%
   Purchase >= $100 and < $300                                              Discount rate = 2%
   Purchase < 100                                                                       No discount
   Purchase = Price * Quantity
   Discount = Purchase * Discount rate                                    
   Net Purchase = Purchase – Discount

Total Net Purchase is the running total of Net Purchases

Create a user interface form as shown below. Use text boxes to input price and quantity. Use labels to display the discount, net purchase (purchase amount after discount) and total net purchases. Click calculate button to calculate and display discount, net purchase, and total net purchases; click clear button to clear values from the text boxes and labels, except total net purchases; send the focus to the price text box.
Click exit button to end the application.

2. Create a sub procedure in a module to calculate:
   a) the sum of even integers from 4 to 12 using DO WHILE. Display the sum in the message box. Name the sub procedure as Q2a.
   b) the sum of odd integers from 5 to 9 using FOR NEXT. Display the sum in message box. Name the sub procedure as Q2b.
   
3. Create a sub procedure named as Question3 in a module to calculate the value and total value of items using a Do/while Loop. Value = Price * Quantity. Total value is the running total of values. Use INPUTBOX function to get the price and quantity. Display the value in the message box. Display total value in the message box after exiting the loop. Loop is entered by inputting “Y” at the prompt “Do you wish to continue?” and exited by entering “N” at the prompt “Do you wish to continue?”
   
4. Create a sub procedure named as Question4 in a module to find the capital. Use INPUTBOX function to get the state and message box function to display the capital.

State                Capital

MN                   St.Paul

WI                   Madison

TX                   Austin

Answer 1

Answer: Hey dear student find the solution of your query, if you have any doubt feel free to ask. Thanks!!

1- UserForm at the running time

Code to copy:

Private Sub CommandButton1_Click() 'commamdButton click event
Dim purchase As Double 'variable to store price
purchase = TextBox1.Value * TextBox2.Value 'calculate total purchase
If (purchase > 400) Then 'calculate discount creteria purchase
TextBox3.Value = purchase * 5 / 100 'discount display in textbox3
Else
If (purchase >= 300 And purchase <= 400) Then
TextBox3.Value = purchase * 4 / 100
Else
If (purchase >= 100 And purchase <= 300) Then
TextBox3.Value = purchase * 2 / 100
Else
If (purchase < 100) Then
TextBox3.Text = "No discount"
End If
End If
End If
End If
purchase = purchase - TextBox3.Value 'calculate net purchase
TextBox4.Value = price
End Sub

Private Sub CommandButton2_Click() 'exit button
UserForm1.Hide
End Sub

Private Sub CommandButton3_Click() 'clear button
TextBox1.Text = Clear
TextBox2.Text = Clear
TextBox3.Text = Clear
TextBox4.Text = Clear
End Sub

a - Procedure to add even integers:

Code to Copy:

Public Sub Q2a() 'procedure to add 4 to 12
Dim sum, i As Integer 'variables
sum = 0
i = 4
Do While (i < 13) 'do while loop to sum up integers
If (i Mod 2 = 0) Then
sum = sum + i
End If
i = i + 1
Loop
MsgBox ("Sum of even integers: " & sum) ''print sum on the msgbox
End Sub

b- Procedure Q2b:

Code to copy:

Public Sub Q2b() 'procedure to add 5 to 9 odd integers
Dim sum, i As Integer 'variables
sum = 0
For i = 5 To 9 'for nextloop to sum up integers
If (i Mod 2 <> 0) Then
sum = sum + i
End If
Next i
MsgBox ("Sum of odd integers: " & sum) ''print sum on the msgbox
End Sub

Procedure Question3:

Code to Copy:

Public Sub Question3() 'procedure to enter price and quantity
Dim price, q As Double 'variables

ans = "Y"
Do While (ans = "Y")
price = InputBox("Enter price for item") 'takes input from user
q = InputBox("Enter Quantity")
ans = InputBox("Do you want to continue?")
MsgBox ("Total price: " & price * q) ''print total price on the msgbox
Loop

End Sub