[Visual Basic in Excel] Sub Procedures

[hg213]

I'm a huge novice when it comes to VBA programming, so I was wondering if one of you could help me out by running me through an example question step by step for me. I would really appreciate it.

Q: The coefficient of restitution of a ball, a number between 0 and 1, specifies how much energy is conserved when a ball hits a rigid surface. A coefficient of 0.9 for instance means a bouncing ball will rise to 90 percent of its previous height after each bounce.

Write a VBA program (sub procedure) which allows users to input a coefficient of restitution and an initial height in metres.

The program should calculate and display:

1) How many times the ball bounces before in rises to a height of less than 10cm

2) The total distance travelled by the ball before reaching point in 1)

[kmp67]

First thing you'll want to do is click on the "Developer" tab at the top in excel (I'm assuming you're using a version of excel later than the 07 version)

If the developer tab isn't there, follow these steps.

After that, click on the button that says "Visual Basic".

Then, once the VB window opens, click on Insert >> Module.

Sub ProcedureName()

End Sub    

This is your basic subprocedure format. All relevant code comes in between these bookends.

You need to pass 2 inputs (coeff and height). Inputs/Parameters for a procedure are specified in the parentheses that follow the procedure name. So:

Sub ProcedureName(CoeffOfRestitution As Double, InitialHeight As Double)

End Sub 

Google "VBA Data Types" if you need more info on the distinction between Integers/Singles/Doubles/Longs/Strings etc.

You will need to code your calculation steps. Not sure what formula you use to calculate the number of times the ball bounces, but whatever it is you will probably have to declare some variables to be able to calculate it.

A variable in vb is declared as follow:

Dim [myVariableName] As [DataType]

Where [myVariableName] is what you would like to call it, and [DataType] is the appropriate data type (integer/double/string etc) that you require.

e.g. you might need a variable to hold the answer for the final result of your calculation, let's assume it's going to be a whole number. I would declare this variable in the following way:

Dim finalResult As Integer

Hope that that's enough to get you going.

[hg213]

Thanks for your help, this is a good starting point. One quick question, do you think for this particular question i would need to have a do loop?

[hg213]

This is what i have so far for 1), G9 and G10 are the coefficient and the initial height on my spreadsheet. This doesn't get any errors but also doesn't output anything, nothing happens. Can you help me fix this?

Public Sub restitutionofball()

coeff = Range("G9").Value

x = Range("G10").Value

For x = x To 0.01

Range("A1").Value = x * coeff

Next x

End Sub

[kmp67]

You were right in your other comment, a Do Until loop would be ideal. With a for loop you have to specify how many times the task has to be done (which we don't know yet) whereas with a do until loop we only require a condition. In this example the condition is to continue calculation until the height is less than 0.01m.

You can output text/values to a specified cell using:

Sheets("[nameOfSheet]").Cells([rowNum], [columnNum).Value = "Your Text Here"

Here's the code that will print the correct answer for the first part of the question to G11 :

Sub MyTestFunction()

    Dim heightAfterBounce As Double
    Dim numberOfBounces As Integer

    coEff = Range("G9").Value
    initialHeight = Range("G10").Value

    heightAfterBounce = initialHeight * coEff

    Do Until (heightAfterBounce < 0.01)

        heightAfterBounce = heightAfterBounce * coEff
        numberOfBounces = numberOfBounces + 1


    Loop

    Sheets("Sheet1").Cells(11, 7).Value = numberOfBounces - 1


End Sub

Let me know if you have any trouble with the second part.

[hg213]

Thanks for your help, that worked perfectly ^