Help with this code, please!!

kgvba

New Member
Joined
Jun 19, 2012
Messages
2
-- removed inline image ---

Using Excel’s VBA, write a program to perform volume calculations on a set of shapes in accordance with the instructions and similar to the spreadsheet layout below:
• Three types of shapes are to be processed: 1-cylinder, 2-cone, and 3-sphere segment.
• Volume equations for the shapes are as follows:

Volcyl = πr2h
Volcon = πr2h/3
Volsph = πr2h/2+ πh3/6
Where r=radius, h=height
• Data defining the shapes will be read directly from the spreadsheet. The program will read one row of data at a time. Each row of data will contain the shape type (1 = cylinder, 2 = cone, 3 = sphere segment), radius and height of the shape. The program will continue to read and process shape data until it reaches a blank row.
• The main program will use a single subroutine or function to calculate the volume of each shape and return this data to the main program. The main program will immediately output the results in a cell adjacent to each row of data.
• The program will also calculate summary data in a 2D array (4 rows x 2 columns, as shown in the spreadsheet above), including: o Total count of each shape
o Total count of all shapes
o Total volume of each shape
o Total volume of all shapes

• The summary statistics should be written back to the spreadsheet
• The program should also check for illegal data including o Negative numbers
o Illegal or undefined volume types
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
So far I got this code, but I'm completely lost. any idea??
Sub vol()
ReDim Shape(1 To 7), rad(1 To 7), ht(1 To 7), vol(1 To 7)
'Read data from spreadsheet
For i = 1 To 7
Shape(i) = Cells(i + 1, 1)
rad(i) = Cells(i + 1, 2)
ht(i) = Cells(i + 1, 3)
Next i
End Sub

Function volume()

If Shape(i) = 1 Then
vol = Worksheet.Function.Pi * rad(i) ^ 2 * ht(i)
ElseIf Shape(i) = 2 Then
vol = Worksheet.Function.Pi * rad(i) ^ 2 * ht(i) / 3
ElseIf Shape(i) = 3 Then
vol = Worksheet.Function.Pi * ht(i) ^ 2 / 2 + Worksheet.Function.Pi * ht(i) ^ 3 / 6
Else
End If
'output vol
For i = 1 To 7
Cells(i + 1, 4) = volume


End Function
 
Upvote 0

Forum statistics

Threads
1,212,046
Messages
6,105,585
Members
447,972
Latest member
carrieann

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top