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:
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)
Area_2D = Abs(Area / 2)
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).