Hierarchical prioritization of on-site wastewater treatment systems using combined decision analysis and Microsoft Visual Basic

Decision makers are increasingly considering environmental sustainability and social impacts when choosing a wastewater treatment unit, along with the system's costs and technical capabilities. Number of factors must be taken into consideration when making this decision, including specific site constraints as well as the preferences of the client. Furthermore, while the literature acknowledges the value of scenario-based analysis, it does not provide decision-makers with the means to perform a tailored analysis specific to their project. Consequently, the aim of this research is to develop a user-friendly program for conducting sustainability assessments that can be used by decision-makers to conduct their own individual assessments. Additionally, the paper prioritizes 21 on-site wastewater treatment units for two different scenarios based on the availability of units in New Zealand. Microsoft Visual Basic for Applications (VBA) was used to develop a multi-criteria decision analysis program in Excel. The main novelty of our research is the incorporation of VBA with a decision-making process to create a user-friendly and efficient decision aid tool, simplifying the process and improving its efficiency and usability. The results of the analysis for Scenario A (a rural Iwi school wanting an environmentally-friendly, locally-produced system) indicated that the Advanced Wastewater System, a bed reactor aerated wastewater treatment system, was the most sustainable unit. Eco Sewage System was found to be the most sustainable option for Scenario B (a residential house in a recently developed rural area), due to its ability to treat wastewater solids aerobically without the need for separate primary treatment. •Developing a user-friendly program for conducting sustainability assessments•Considering 20 sustainable criteria to evaluate the performance of treatment units•Successful application of VBA for multi-criteria decision analysis•Considering specific site constraints as well as the preferences of the client•Identifying the most sustainable solution for each scenario