How can I wrap this to one function?

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?

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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Threads
1,127,177
Messages
5,623,199
Members
415,957
Latest member
Newguy1924

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top