Cross Section Calculation

[philiphales]

For about 20 years I have used the attached file, with extremely clumsy macros. However, as I had not used it for the last 4 or 5 years I needed to calculate the area of a shape. I mainly used to use it for roads to get volumes. However, since becoming a member of this most wonderful forum, and having been helped by you guys recently, I thought knowing how you like a challenge, thought you guys will be able to change my most foul macros into a thing of beauty. The brief is on the spreadsheet.

 

[Hui]

Would you be happy with a solution that uses a VBA User Defined Function for the area calculation instead of worksheet Formulas?

 

[Hui]

If VBA is Ok

Copy this to a Code Module in VBA

Function PolygonArea(X_Range As Range, Y_Range As Range)
Dim N As Integer
Dim xFirst As Integer
Dim xSecond As Integer
Dim QuickSum As Double

N = X_Range.Count
If N <> Y_Range.Count Then
  PolygonArea = "Unequal Points"
  Exit Function
End If

For i = 0 To N - 1
  If i = 0 Then
  xFirst = N
  Else
  xFirst = i
  End If
  xSecond = i + 1
 
  QuickSum = QuickSum + X_Range.Cells(xFirst, 1).Value * Y_Range.Cells(xSecond, 1) _
  - X_Range.Cells(xSecond, 1).Value * Y_Range.Cells(xFirst, 1).Value
Next i

PolygonArea = Abs(0.5 * QuickSum)

End Function

Then on the worksheet simply use:
=PolygonArea(A8:A25,E8:E25)
=162.989

 

[philiphales]

I will look at this later, but the basic sheet I enter the co-ords on uses - the 'co-ordinate' method of calculation. Folk who understand this want to see it presented in the way I use it so that it can be seen and checked.

 

[philiphales]

I have had time to review this and I would much prefer it if my macro could be re-written to produce what it does, but in a more professional looking way. Most grateful for your help thus far.

 

[Hui]

So you want to see the workings, even though the workings aren't required?

 

[philiphales]

I work in construction and whilst the information can be jointly taken from drawings (contractor and Client) people who understand the Co-ordinate Method (which I have been using since 1969, even before we had electronic calculators, and passed the sheets to a comp operator) we like to see that the information is correctly set out. So I am afraid the answer is - yes, please.

 

[philiphales]

So nobody wants to help......................

 

[Hui]

Philip

Please don't phrase responses like that.

A gentle reminder would suffice.

People here donate their time for free, without reward.

I have given you a solution, which works but doesn't look like doing it manually. That is a common problem in introducing new technologies into old workplaces. I know it works well as I am a Mining Engineer and have used it for in excess of 20 years in calculating polygon areas from survey strings.

I will get to your question at some stage.

 

[philiphales]

Hui

I am very grateful for the help I have received in the past and I know that time is donated, however the donors do get the satisfaction of being helpful and I am sure that everyone is grateful. All I asked was if no one was going to take this any further I would need to try and seek assistance elsewhere. I just did not want to sit thinking someone was was helping when nothing was being done, as that helps no one. I use AutoCAD quite often to create polygons and interrogate it for areas, but have an awful job then convincing non-AutoCAD users that the answer is what it is and that I have not doctored it to suit myself. So please accept this as an apology as I was not trying to be ungrateful or disrespectful. I thank you in advance for the re-newed offer of help and would add that I am not desperate for an answer, so a month or two would be useful to me, if that is OK with you.

 

[Hui]

Please try the attached file:
Enable Macro's and Click on the Calc Area Button

 

[philiphales]

Hui
That is spot on, thanks. There are two minor issues I did mention - my clumsy effort copied the first Y and X co-ordinate to the end of the list so that the graphical representation completes, at the moment even if I copy them manually and regenerate the drawing with ReDraw the final line does not complete. The other is that each time I save one of these files with a different name the ReDraw throws up a runtime error because you have to edit the macro on line - 'Windows("Cross Section Calc ewks outline rev 01.XLSM").Activate', to the file name to get it to run.

I am very grateful for this and have made a $10 donation - hope this is OK.

 

[Hui]

I have adjusted the code to allow for the chart and the Clear Calcs button as well

All donations are appreciated

 

[Hui]

What does 'Windows("Cross Section Calc ewks outline rev 01.XLSM").Activate' do?

If I comment it out by putting a ' in front of it, it runs fine

 

[philiphales]

Hi
That was quick, thanks again. Yes, this all works as I hoped it would - I never even thought about asking for a range, impressive. I cannot remember where the activate line came from, it may have been that I copied it from somewhere, or that I used the create macro within Excel itself. I never thought about using a '. I guess this works like a rem statement I remember from my old BBC computer days when I tried (and failed) to understand BASIC!

regards Philip

 

[Hui]

' = Rem = Comment