Hi,
I have developed a workbook for estimating project costs based on selected Labour, Material, and Plant components, but I've recently dicovered classes and collections and was wondering whether they could be used to develop a more efficient solution.
The data is multi-dimensional, i.e. Components are grouped into Units, which in turn are grouped into Templates. The costs are driven by the Type of Work and the Geographical Area it will be done.
The current solution simply lists all the components on a worksheet in a Unit/Component hierarchy, with the available costs for each component held on separate worksheets. The user selects Units (or a predefined selection by picking a Template) and copies them into another worksheet to calculate the estimate. Cost data is copied into the estimate from the cost sheets based on the work type and area, i.e. the applicable contractor rates.
The solution works, but is slow and means each user copy of the workbook stores the full dataset, which is pushing 10Mb. I'm sure there must be a more efficient approach, hence looking at classes etc...
Could/should I create a Class to hold the Components, possibly in a single workbook that sits centrally, and then load the data into an array or collection in another workbook each time a user wants to create an estimate? From the limited experience I have with classes, getting data in and out is much quicker than filtering/copying from one sheet to another.
Your advice would be appreciated.
Thanks in advance.
I have developed a workbook for estimating project costs based on selected Labour, Material, and Plant components, but I've recently dicovered classes and collections and was wondering whether they could be used to develop a more efficient solution.
The data is multi-dimensional, i.e. Components are grouped into Units, which in turn are grouped into Templates. The costs are driven by the Type of Work and the Geographical Area it will be done.
The current solution simply lists all the components on a worksheet in a Unit/Component hierarchy, with the available costs for each component held on separate worksheets. The user selects Units (or a predefined selection by picking a Template) and copies them into another worksheet to calculate the estimate. Cost data is copied into the estimate from the cost sheets based on the work type and area, i.e. the applicable contractor rates.
The solution works, but is slow and means each user copy of the workbook stores the full dataset, which is pushing 10Mb. I'm sure there must be a more efficient approach, hence looking at classes etc...
Could/should I create a Class to hold the Components, possibly in a single workbook that sits centrally, and then load the data into an array or collection in another workbook each time a user wants to create an estimate? From the limited experience I have with classes, getting data in and out is much quicker than filtering/copying from one sheet to another.
Your advice would be appreciated.
Thanks in advance.
