Arrays in UDFs for VBA Excel
Posted on 2015-02-16
I have a UDF that pulls variables to calculate a customer's annual utility bill. The UDF references 12 months of usage data, each month is an individual cell reference which means 12 different variables in the UDF (plus the other ones needed). I've tried to name them as a range and simplify the UDF but have had no success. So, the question is, how can I pull in the 12 months of data as an array, have it cycle through the calculations and then spit out one annual number? Do I have name the range in Excel and reference that in the UDF cell equation?I have a copy of the spreadsheet attached.
Also, for some reason, I am unable to show more than one subroutine at a time in each module (I have to use the drop down box where is says declarations.) Is there a way to change this back?