Since I know a lot of people who come here do so for Excel-related posts, I thought it would be useful to rewrite last week’s Pareto frontiers function in VBA for Excel.

Remember that 2D Pareto frontiers are the set of options which can’t be bettered on one metric without making things worse on the other metric. In the graph below, the options highlighted on the Pareto frontier are those where better quality options can only be had by increasing the cost you are willing to pay. All of the other options can be bettered on both quality and cost by at least one other option.

Pareto frontier

Rewriting the code for VBA is instructional for two reasons. First, it shows the power of Python, and why I’ve been using it more and more lately. The code I wrote in Python required just 13 lines without having to import any other modules. This VBA code was much more work as I had to adapt Andy Pope’s BubbleSort() to behave like Python’s sorted(), including the reverse option. And even if VBA had had a convenient native way to sort, the rest of the code is also fiddly, requiring loops to populate arrays and for arrays to be re-dimensioned rather than having a simple array.append() method.

Nevertheless, there are clear benefits to working with Excel. Not least the familiarity, auditability and simplicity of generating numbers on the spreadsheet. I still use Excel to write drafts of any numerical calculations and often to mock up GUIs, even if it will be translated into another language at a later date.

So in the spirit of making tools available, here’s the code rewritten for VBA. As with the Python code it takes two equally-sized arrays, but unlike the Python code it returns the Pareto frontier as a 2D array rather than as two lists.

And the adapted BubbbleSort() function is as follows. BubbleSort is a notoriously slow algorithm but is about the simplest to implement in VBA (without creating and deleting a new worksheet as an intermediate stage, which has its own drawbacks). You shouldn’t have too much difficulty substituting a different sort function if you need to work with very large arrays.

And to make things nice and simple, here’s a piece of test code you can adapt for calling the function. Just put some values into the range A1:B25 in Sheet 1 and run the macro.

Jamie Bull | jamiebull1@gmail.com

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 “Find Pareto frontiers in VBA”

  • vertexvortex says:

    For what it’s worth, this can be done in excel with 0 lines of VBA. And even the excel formulas required are minimal, to say the least.

    If your data are listed in columns A and B, and with sample set size of n

    [C1:Cn]=COUNTIFS(A1:An,”<"&A1,B1:Bn,”<"&B1)

    A 0 determines there are no datum that dominate this point.

    Then, to generate a list of frontier points:

    The first point:
    [X]=INDEX(A1:An,MATCH(0,C1:Cn,0)), [Y]=INDEX(B1:Bn,MATCH(0,C1:Cn,0))

    The subsequent points:
    [X]=INDEX(OFFSET(A1:An,MATCH(E2,A1:An,0),0),MATCH(0,OFFSET(C1:Cn,MATCH(E2,A1:An,0),0),0))
    [Y]=INDEX(OFFSET(B1:Bn,MATCH(F2,B1:Bn,0),0),MATCH(0,OFFSET(C1:Cn,MATCH(F2,B1:Bn,0),0),0))

    Here is an example. I included the scatter plot and used dynamic ranges as well.
    If you want to see the line connecting the frontier, the data must be sorted C,B,A or C,A,B.

Leave a Reply

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