Can anyone help me with a school assignment?

Rolltide89 · Nov 4, 2011

I have an assignment for school I need to get done and I cannot figure out the right codes for this for the life of me. This is my first course in VBA and I really need some help badly! Heres the project:

 [FONT=&quot]CS385: Project 1 [/FONT]
[FONT=&quot]Check Calendar for Due Date[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Create a macro called Calculations that will perform the following tasks:[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]1. [/FONT][FONT=&quot]Prompt the user to enter three numbers and store them in appropriate variables.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]2. [/FONT][FONT=&quot]Create a private function Min which will take the three variables as arguments. The procedure will determine the smallest of the three numbers entered by the user and return it to a variable named Smallest using an assignment statement.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]3. Create a private function Max which will take three variables as arguments. The procedure will determine the largest of the three numbers entered by the user and return it to a variable named Largest using an assignment statement.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]4. [/FONT][FONT=&quot] Determine if each variable is even or odd and print the variable and result to the spreadsheet.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]5. [/FONT][FONT=&quot] Output the following with labels starting in cell A1:[/FONT]
[FONT=&quot]a. [/FONT][FONT=&quot]All three numbers entered by the user.[/FONT]
[FONT=&quot]b. [/FONT][FONT=&quot]Smallest of numbers entered by the user.[/FONT]
[FONT=&quot]c. [/FONT][FONT=&quot]Largest of numbers entered by the user.[/FONT]
[FONT=&quot]g. [/FONT][FONT=&quot]Numbers are Even or Odd[/FONT]
[FONT=&quot]h. [/FONT][FONT=&quot]Column A should be resized using code to the widest statement[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]6. [/FONT][FONT=&quot]Save the workbook in this form CollegeJoeHW1.xls and submit it to WebCT.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Sample Output Results:[/FONT]
[FONT=&quot][/FONT]
Column A in Excel Column B in Excel
First number= 46
Second number= 68
Third number= 23
The largest nuber is 68
The smallest number is 23
The number 45 is odd
The number 68 is even
The number 23 is odd

*He gave us a starter code for it:

This is some code to get you started on Project 1.
Option Explicit
Public Sub Calculation()
'Define variables
Dim Var1 As Long, Var2 As Long, Var3 As Long, Largest As Integer, Smallest As Integer
'Input 3 numbers from user
Var1 = InputBox("Enter the first integer :", "First")
Var2 = InputBox("Enter the second integer :", "Second")
Var3 = InputBox("Enter the third integer :", "Third")
'Write information to the spreadsheet
Range("a1").Value = "First number = "
Range("a2").Value = "Second number = "
Range("a3").Value = "Third number = "
Range("b1").Value = Var1
Range("b2").Value = Var2
Range("b3").Value = Var3
'Find the Largest Number
Largest = Max(Var1, Var2, Var3)
Range("a4").Value = "The largest number is " & Largest
'Find the Smallest Number
Smallest = Min(Var1, Var2, Var3)
Range("a5").Value = "The smallest number is " & Smallest
If Var1 Mod 2 = 0 Then
Range("a6").Value = "The number " & Var1 & " is even"
Else
Range("a6").Value = "The number " & Var1 & " is odd"
End If
If Var2 Mod 2 = 0 Then
Range("a7").Value = "The number " & Var2 & " is even"
Else
Range("a7").Value = "The number " & Var2 & " is odd"
End If
If Var3 Mod 2 = 0 Then
Range("a8").Value = "The number " & Var3 & " is even"
Else
Range("a8").Value = "The number " & Var3 & " is odd"
End If
Columns("a:a").EntireColumn.<wbr>AutoFit
End Sub
You will need to create the Max Function and Min Function in the following form:
Private Function Max(Var1 As Integer, Var2 As Integer, Var3 As Integer) As Integer
‘Place your If statements here from Computer Exercise 2, Problem 4 which finds the largest and smallest values from three integers
End Function

*Then he gave us another function code to check if we were right. He gave us the Min and Max function:
Private Function Min(v1 As Long, v2 As Long, v3 As Long) As Long
If v1 < v2 And v1 < v3 Then
Min = v1
ElseIf v2 < v1 And v2 < v3 Then
Min = v2
ElseIf v3 < v1 And v3 < v2 Then
Min = v3
End If
End Function
Private Function Max(v1 As Long, v2 As Long, v3 As Long) As Long
If v1 > v2 And v1 > v3 Then
Max = v1
ElseIf v2 > v1 And v2 > v3 Then
Max = v2
ElseIf v3 > v1 And v3 > v2 Then
Max = v3
End If
End Function

*If anyone give me the correct code so I can figure out my mistakes please. I would be very thankful!!

Andrew Poulsom · Nov 4, 2011

Welcome to MrExcel.

You seem to have pretty much all of the necessary code there. What are you stuck on?

Rolltide89 · Nov 4, 2011

Yes I figured that one out. Could you help me with this one??
 [FONT=&quot]CS385: Project 3 [/FONT]
[FONT=&quot]Check Calendar for Due Date[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Create a macro called Amortization with a button that will perform the following tasks:[/FONT]
[FONT=&quot]1. [/FONT][FONT=&quot]Prompt the user to enter the rate of the loan in decimal form and write label Rate of Loan in A1 with the value in B1 formatted as a percentage.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]2. [/FONT][FONT=&quot]Prompt the user to enter the number of years for the loan and write label Number of Years for the loan in A2 with the value in B2 formatted as currency.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]3. [/FONT][FONT=&quot]Prompt the user to enter the amount of the loan and write label Amount Borrowed in A3 with the value in B3 formatted as currency.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]4. [/FONT][FONT=&quot]Write the label Payment in A4 with the actual value in B4 formatted as currency. Use the PMT function for this calculation.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]5. [/FONT][FONT=&quot]Create an amortization table with the following headings and format the values with currency style formatting:[/FONT]
[FONT=&quot]a. Period-this will reflect the number of payments.[/FONT]
[FONT=&quot]b. Payment-use the PMT function to calculate monthly payment[/FONT]
[FONT=&quot]c. Interest-use the IPMT function to calculate monthly interest.[/FONT]
[FONT=&quot]d. Principal-use the PPMT function to calculate monthly principal.[/FONT]
[FONT=&quot]e. Balance-first time subtract the principal from loan amount. Afterwards, you will use the old balance minus the current principal to calculate the new balance.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]6. [/FONT][FONT=&quot]Creation of the amortization table will require a While or For loop where the rate must be divided by 12 and the years of loan multiplied by 12. You should create variables for Rate, Time, Amount, and Payment. It will also help to create variables for Period, Interest, Principal, and Balance.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]7. [/FONT][FONT=&quot]Make sure your macro also widens columns a-g to an appropriate width.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]8. [/FONT][FONT=&quot]You should bold all labels.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]9. [/FONT][FONT=&quot]Create a Clear Macro with a button that will clear the spreadsheet.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]10. [/FONT][FONT=&quot]Save the Excel worksheet as LastNameFirstNameP3.xls.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]11. [/FONT][FONT=&quot]Submit to WebCT.[/FONT]

*This is what he gave as as the starter code:

1. Prompt the user to enter the rate of the loan in decimal form and write label Rate of Loan in A1 with the value in B1 formatted as a percentage.
Rate = InputBox("Enter rate of loan in decimal from", "RATE")
Range("a1").Value = "Rate of Loan"
Range("b1").Value = FormatPercent(Rate, 2)

2. Prompt the user to enter the number of years for the loan and write label Number of Years for the loan in A2 with the value in B2 formatted as currency.
Time = InputBox("Enter the number of years", "TIME")
Range("a2").Value = "Number of Years"
Range("b2").Value = Time

3. Prompt the user to enter the amount of the loan and write label Amount Borrowed in A3 with the value in B3 formatted as currency.
Amount = InputBox("Enter the amount of loan", "AMOUNT")
Range("a3").Value = "Amount Borrowed"
Range("b3").Value = FormatCurrency(Amount, 2)

4. Write the label Payment in A4 with the actual value in B4 formatted as currency. Use the PMT function for this calculation.
Range("a4").Value = "Amount of Monthly Payment"
Range("b4").Value = FormatCurrency(-Pmt(Rate / 12, Time * 12, Amount), 2)

5. Create an amortization table with the following headings and format the values with currency style formatting:
a. Period-this will reflect the number of payments.
b. Payment-use the PMT function to calculate monthly payment
c. Interest-use the IPMT function to calculate monthly interest.
d. Principal-use the PPMT function to calculate monthly principal.
e. Balance-first time subtract the principal from loan amount. Afterwards, you will use the old balance minus the current principal to calculate the new balance.
Range("c6").Value = "Period"
Range("d6").Value = "Payment"
Range("e6").Value = "Interest"
Range("f6").Value = "Principle"
Range("g6").Value = "Balance"
Range("a1:a4").Font.Bold = True
Range("c6:g6").Font.Bold = True
Payment = Range("b4").Value
Balance = Amount

6. Creation of the amortization table will require a While or For loop where the rate must be divided by 12 and the years of loan multiplied by 12. You should create variables for Rate, Time, Amount, and Payment. It will also help to create variables for Period, Interest, Principal, and Balance.
For x = 1 To Time
Sheet1.Cells(y, 3) = x
Sheet1.Cells(y, 4) = FormatCurrency(Mpayment, 2)
Sheet1.Cells(y, 5) = FormatCurrency(-IPmt(Rate, x, Time, Amount), 2)
Principle = FormatCurrency(-PPmt(Rate, x, Time, Amount), 2)
Sheet1.Cells(y, 6) = FormatCurrency(Principle, 2)
y = y + 1
Next
This is not necessarily all of the code needed to complete the project.

**Are there any changes I need to make to the code or omit for this program to run?

Andrew Poulsom · Nov 4, 2011

I don't think you need to omit any, but you will need to add some. Have a go yourself and post back with any specific queries. Nobody is going to complete the entire assignment for you. That wouldn't really be helping you.

Rolltide89 · Nov 4, 2011

Thank you for your help Andrew. I am trying to learn how to use loops and get them to repeat something but I am a little stuck. The 1st part is 1. Prompt the user to enter the number of random numbers between 20 and 100 to be generated.

I have so far:
Dim X As Integer
Dim Y As Integer
Dim Average As Double
Dim Size As Integer
Dim Sum As Integer

Size = InputBox("Enter the number of random numbers to be generated between 20 and 100", "Input")

2. Use a While/Wend loop to generate the random numbers, and write them to A1:A100.

I have:

While X <= Size
Sheet1.Cells(X, 1) = Int(20 + Rnd() * 100)
Sum = Sum + Sheet1.Cells(X, 1)
X = X + 1
Wend

How do I apply it to the range A1:A100 and make the loop happen that many times??

Andrew Poulsom · Nov 4, 2011

If you set X = 1 before your loop it will fill the cells for you.

Rolltide89 · Nov 4, 2011

Thank you. I am struggling on the question I had before that one. I've created a code for everything except for #6. Could you show me how to create the code for the amortization table? When I run the code I made, I can see the headings of the table, but I don't know how to create the data cells. I appreciate your help! This is what I have:

Public Sub Amortization()

Dim Rate As Long
Dim Amount As Currency
Dim Time As Currency
Dim Payment As Currency
Dim Balance As Currency

Rate = InputBox("Enter rate of loan in decimal from", "RATE")
Range("a1").Value = "Rate of Loan"
Range("b1").Value = FormatPercent(Rate, 2)

Time = InputBox("Enter the number of years", "Time")
Range("a2").Value = "Number of Years"
Range("b2").Value = FormatCurrency(Time, 2)

Amount = InputBox("Enter the Amount of Loan", "AMOUNT")
Range("a3").Value = "Amount Borrowed"
Range("b3").Value = FormatCurrency(Amount, 2)

Range("a4").Value = "Amount of Monthly Payment"
Range("b4").Value = FormatCurrency(-Pmt(Rate / 12, Time * 12, Amount), 2)

Range("c6").Value = "Period"
Range("d6").Value = "Payment"
Range("e6").Value = "Interest"
Range("f6").Value = "Principle"
Range("g6").Value = "Balance"
Range("a1:a4").Font.Bold = True
Range("c6:g6").Font.Bold = True
Payment = Range("b4").Value
Balance = Amount

End Sub

Andrew Poulsom · Nov 4, 2011

Doesn't this do that?

Code:

For x = 1 To Time
    Sheet1.Cells(y, 3) = x
    Sheet1.Cells(y, 4) = FormatCurrency(Mpayment, 2)
    Sheet1.Cells(y, 5) = FormatCurrency(-IPmt(Rate, x, Time, Amount), 2)
    Principle = FormatCurrency(-PPmt(Rate, x, Time, Amount), 2)
    Sheet1.Cells(y, 6) = FormatCurrency(Principle, 2)
    y = y + 1
Next

Note the misspelling of principal as principle.

Rolltide89 · Nov 4, 2011

I have fixed the spelling mistake and tried to define all of the variables, but the program is still not running like it needs to in the directions. Am I missing anything?

Option Explicit

Public Sub Amortization()

Dim Rate As Long
Dim Amount As Currency
Dim Time As Currency
Dim Payment As Currency
Dim Balance As Currency
Dim X As Currency
Dim Y As Currency
Dim MPayment As Currency
Dim Principal As Currency

Rate = InputBox("Enter rate of loan in decimal from", "RATE")
Range("a1").Value = "Rate of Loan"
Range("b1").Value = FormatPercent(Rate, 2)

Time = InputBox("Enter the number of years", "Time")
Range("a2").Value = "Number of Years"
Range("b2").Value = Time

Amount = InputBox("Enter the Amount of Loan", "AMOUNT")
Range("a3").Value = "Amount Borrowed"
Range("b3").Value = FormatCurrency(Amount, 2)

Range("a4").Value = "Amount of Monthly Payment"
Range("b4").Value = FormatCurrency(-Pmt(Rate / 12, Time * 12, Amount), 2)

Range("c6").Value = "Period"
Range("d6").Value = "Payment"
Range("e6").Value = "Interest"
Range("f6").Value = "Principle"
Range("g6").Value = "Balance"
Range("a1:a4").Font.Bold = True
Range("c6:g6").Font.Bold = True
Payment = Range("b4").Value
Balance = Amount

For X = 1 To Time
Sheet1.Cells(Y, 3) = X
Sheet1.Cells(Y, 4) = FormatCurrency(MPayment, 2)
Sheet1.Cells(Y, 5) = FormatCurrency(-IPmt(Rate, X, Time, Amount), 2)
Principal = FormatCurrency(-PPmt(Rate, X, Time, Amount), 2)
Sheet1.Cells(Y, 6) = FormatCurrency(Principal, 2)
Y = Y + 1
Next

End Sub

Andrew Poulsom · Nov 5, 2011

What in particular isn't working?

Can anyone help me with a school assignment?

Rolltide89

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

Rolltide89

New Member

Andrew Poulsom

MrExcel MVP

Rolltide89

New Member

Andrew Poulsom

MrExcel MVP

Rolltide89

New Member

Andrew Poulsom

MrExcel MVP

Rolltide89

New Member

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

Can anyone help me with a school assignment?

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock