Multiverse
New Member
- Joined
- Nov 15, 2019
- Messages
- 16
Hi Folks
I thought I had written a clever bit of code here. However while it initially solved my problem, it's now causing me a problem with a loop that writes the results to the Worksheet. And it violates the principles of encapsulation.
I have a collection of Consultants of which one of the properties is a collection of Fiscals, of which one of the properties is a collection of months which has functions to retrieve revenue, billing and other metrics. In my loop, I'm assigning the function to an Array and writing the array directly to the worksheet. Still with me?
it works fine... but... The user controls the metric via a drop down list - at which point I need to run another loop, like the one below for billing. The thing is I have 7 metrics and therefore 7 functions doing the same thing (returning an array) except for the function call .Billing or .Revenue. How could I wrap these 7 separate functions into one?
In hindsight, the nested collections might not be a good solution, but given I'm wrapping this project up, it would be great if you have a simple solution. Thanks in advance!
I thought I had written a clever bit of code here. However while it initially solved my problem, it's now causing me a problem with a loop that writes the results to the Worksheet. And it violates the principles of encapsulation.
I have a collection of Consultants of which one of the properties is a collection of Fiscals, of which one of the properties is a collection of months which has functions to retrieve revenue, billing and other metrics. In my loop, I'm assigning the function to an Array and writing the array directly to the worksheet. Still with me?
VBA Code:
'Loop through myColl and add Consultant to outputArray
For n = 1 To noOfConsultants
theOutputArray(n, 1) = theConsultantColl(n).Consultant
theOutputArray(n, 2) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("May").Revenue
theOutputArray(n, 3) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("June").Revenue
theOutputArray(n, 4) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("July").Revenue
theOutputArray(n, 5) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("August").Revenue
theOutputArray(n, 6) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("September").Revenue
theOutputArray(n, 7) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("October").Revenue
theOutputArray(n, 8) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("November").Revenue
theOutputArray(n, 9) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("December").Revenue
theOutputArray(n, 10) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("January").Revenue
theOutputArray(n, 11) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("February").Revenue
theOutputArray(n, 12) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("March").Revenue
theOutputArray(n, 13) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("April").Revenue
Next n
it works fine... but... The user controls the metric via a drop down list - at which point I need to run another loop, like the one below for billing. The thing is I have 7 metrics and therefore 7 functions doing the same thing (returning an array) except for the function call .Billing or .Revenue. How could I wrap these 7 separate functions into one?
VBA Code:
'Loop through myColl and add Consultant to outputArray
For n = 1 To noOfConsultants
theOutputArray(n, 1) = theConsultantColl(n).Consultant
theOutputArray(n, 2) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("May").Billing
theOutputArray(n, 3) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("June").Billing
theOutputArray(n, 4) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("July").Billing
theOutputArray(n, 5) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("August").Billing
theOutputArray(n, 6) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("September").Billing
theOutputArray(n, 7) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("October").Billing
theOutputArray(n, 8) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("November").Billing
theOutputArray(n, 9) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("December").Billing
theOutputArray(n, 10) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("January").Billing
theOutputArray(n, 11) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("February").Billing
theOutputArray(n, 12) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("March").Billing
theOutputArray(n, 13) = theConsultantColl(n).FiscalColl(theFiscalOne).MonthsColl("April").Billing
Next n
In hindsight, the nested collections might not be a good solution, but given I'm wrapping this project up, it would be great if you have a simple solution. Thanks in advance!