OK - Here's my good deed for the day.
Here's the visual basic code to calculate the volume of water in a pond in the shape of a frustrum (ie a truncated rectangular pyramid)
This example the user can choose between any of 3 pond sizes using the PondNum variable. The PondHeight is the only other input variable.
The Pond Dimensions for each pond are defined inside of the function and can be tweaked to your specific application.
Our application, we need to determine the volume of liquor from a height reading on a daily basis for 3 different ponds.
If you want the function to be useable in all of your workbooks, you will need to define it as Public. This is easy. Just put the word "Public" in front of "Function" in the first line"
Function PondVol(LiquorHeight As Single, PondNum As Integer) As Single 'Constant Definitions 'BL = Base Length 'BW = Base Width 'TL = Top Length (at height H) 'TW = Top Width (at height H) 'H = Distance from base to the top surface of the pond measured vertically. Const Pond1BL = 13, Pond1BW = 17.5, Pond1TL = 33.5, Pond1TW = 38, Pond1H = 4.1 Const Pond2BL = 14.19, Pond2BW = 13.95, Pond2TL = 38.25, Pond2TW = 38.01, Pond2H = 4.81 Const Pond3BL = 26.75, Pond3BW = 14, Pond3TL = 50.75, Pond3TW = 38, Pond3H = 4.8 'Area Calculations Pond1BA = Pond1BL * Pond1BW Pond2BA = Pond2BL * Pond2BW Pond3BA = Pond3BL * Pond3BW 'Calculation of "Chunk" for to allow for Tangent Calculation. 'The Chunk is half of the difference between the top length and bottom length Pond1LC = (Pond1TL - Pond1BL) / 2 Pond1WC = (Pond1TW - Pond1BW) / 2 Pond2LC = (Pond2TL - Pond2BL) / 2 Pond2WC = (Pond2TW - Pond2BW) / 2 Pond3LC = (Pond3TL - Pond3BL) / 2 Pond3WC = (Pond3TW - Pond3BW) / 2 'Calculate Pond Height depending upon Pond Number Select Case PondNum Case 1 Pond1HL = 2 * (LiquorHeight * Pond1LC / Pond1H) + Pond1BL Pond1HW = 2 * (LiquorHeight * Pond1WC / Pond1H) + Pond1BW Pond1HA = Pond1HL * Pond1HW PondVol = LiquorHeight * (Pond1BA + Pond1HA + Sqr(Pond1BA * Pond1HA)) / 3 'PondHeight Case 2 Pond2HL = 2 * (LiquorHeight * Pond2LC / Pond2H) + Pond2BL Pond2HW = 2 * (LiquorHeight * Pond2WC / Pond2H) + Pond2BW Pond2HA = Pond2HL * Pond2HW PondVol = LiquorHeight * (Pond2BA + Pond2HA + Sqr(Pond2BA * Pond2HA)) / 3 Case 3 Pond3HL = 2 * (LiquorHeight * Pond3LC / Pond3H) + Pond3BL Pond3HW = 2 * (LiquorHeight * Pond3WC / Pond3H) + Pond3BW Pond3HA = Pond3HL * Pond3HW PondVol = LiquorHeight * (Pond3BA + Pond3HA + Sqr(Pond3BA * Pond3HA)) / 3 Case Else PondVol = 0 End Select End Function