Replacing Array Formulas with VBA

  • Hello Everyone,

    I'm hoping someone would help replace array formulas with VBA.

    Report 1 is a list of folks who've completed certain trainings.

    Report 2 is a list of everyone and all of their assigned trainings

    What I need is for Report 2 to display the Signoff date and instructor next to the completed trainings.

    Report 2 displays exactly how I need it to but I am having to use array formulas in columns E & F to achieve it.

    In the actual document, these array formulas number in the thousands which really slow down the WB especially since it's updated whenever a training is added and when completed.

    I'm hoping someone would be willing to replace the Array formulas with VBA.

    Also, maybe this can be accomplished in Power Query.

    Either way, the array formulas work, but they make the program almost unusable.

    The below pics come from the attached example (which is xlsm but with no scripts).

    Thanks for any help with this

    Report 1

    Training SignOff List

    Report 2 (Thanks Mumps)

  • Hi SL,

    Pretty sure that the array formulas are considered native, the problem with your workbook is having around 10000 formulae referencing a few hundred thousand cells so the calculation update takes some time... I imagine it is more than irritating to work with. If you have not already done so turn your calculation to manual and only update (using calculate sheet) when you have made all the required changes and things should run more smoothly. A VBA solution with a button press should be quicker I think, but Roy is probably correct that a pivot table or filter would probably suit your needs better.

    For example, would this do:

    it is a pivot table created with signoff date and training in rows, employee in column and count of signoff date in values. (NB: if you get the date split into months etc, right click on it and select "ungroup")


  • Hey Justin,

    Yes, you're right, I was thinking of "regular formulas" vs "array" (though powerful; in my experience take longer to calculate)

    I would've also thought that a VBA solution would be quicker, but Roy's knowledge of VBA doesn't really leave room for my assumption

    Thanks for your response and effort putting together the Pivot Table

    The issue is that I would like the report to end up looking like the PreReport so that the date and instructor are also displayed. Below is the same pic from the WB.

    Columns A:D are "refreshed" whenever a new training is added and assigned via userform (this is assigned to groups in bulk). The range on Sheet SignOffDates is also updated via userform.

    The PreReport format seems to work well when running reports powered by advanced filter. The layout also feeds a dynamic matrix for ease of viewing for employees and management.

    My mistake was setting this up prior to testing the performance of so many array formulas. Whenever a new training is added and assigned, it can take 5 to 7min to update, without the formulas (or when calculations are temporarily set to manual) it renders in 3sec. Though it will have to calculate at some point, and it is likely that management will want to add a new training then immediately run a report to notify staff of an update or what not, and the wait time makes that really tough.

    Though the PreReport format would work best for me, I believe that you and Roy have the next best solution by using a pivot table or Power pivot (since combining two ranges)

    Still very open to help.

    In the mean time I'll post updates to this thread as I discover solutions.

  • You can try this macro linked to a button on the page, I think it is working correctly but let me know if it is off anywhere.