WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]

This VBA function was written today to try out an algorithm I found while looking for a way to check surface areas of building models in EnergyPlus. The standard EnergyPlus outputs do include surface area summaries but for some reason they don’t include roofs. And in any case I find it much simpler to interrogate a model building by analysing the text file than by running a simulation with the model.

The code takes the x,y coordinates of each vertex of the surface and returns the surface area as a double. Working with EnergyPlus will be a little harder as there will be surface elements which are horizontal (floors, ceilings and flat roofs), vertical (walls, doors and windows) and potentially inclined (pitched roofs). That’s a problem for another day though. For now, just working with x,y rather than x,y,z coordinates is enough to test the approach.

The final code will be part of a Python package I’m developing to work with EnergyPlus so the VBA code probably won’t go any further than this – unless there’s a huge outcry for it to do so!

It’s basically just a translation of this code here. The code is an implementation of Green’s Theorem which describes the relationship of the integral of the perimeter of a 2D polygon to the integral of it’s area. It works for closed polygons, both concave and convex. The only limitation is for shapes which intersect themselves which isn’t a problem when you’re dealing with building surfaces.

The code is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Function Area_2D(XY_pairs As Variant) ' Calculates the area of a 2-dimensional polygon when passed a 2D array. Dim Area As Double Dim N As Long Dim i As Long Dim j As Long Dim x As Integer Dim y As Integer x = 1 y = 2 N = UBound(XY_pairs) Area = 0 For i = 0 To N - 1 j = (i + 1) Mod N ' The following two lines need the + 1s because arrays in VBA are 1-indexed Area = Area + XY_pairs(i + 1, x) * XY_pairs(j + 1, y) Area = Area - XY_pairs(i + 1, y) * XY_pairs(j + 1, x) Next Area_2D = Abs(Area / 2) End Function |

To use it, just pass in a 2 x N matrix made up of x and y pairs. For example you can pass in the named range “Vertices” on a worksheet with `Area = Area_2D(Range("Vertices").Value2)`

.

## 1 Comment on “VBA to find the area of a surface”

I’m working with Arcmap as an elementary activist, my interest is to find area of a polygon using a field calculator found within the attribute table of the data layer.

Please assist me, I tied but ended on error code. I’m waiting to hear from you