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.

CC Image licensed from Wikimedia Commons

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:

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)


Jamie Bull |

Related Posts

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 […]

Just a quick post to point out a couple of really useful tools.The first is a web-based tool for finding weather files for a location of interest. It’s similar to the Excel EPW finder tool we created a few years back, but much more modern looking. It is however missing a few of the useful […]

Eppy is a really useful library which I’ve written about several times, since before I really had anything to offer in terms of contributing code. Over the past year or so though, I’ve started to contribute back some of the changes and additions I’ve made while using eppy on academic and commercial projects.This post is […]

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

  • Veronica Bitta says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *