Arrays or Collections for data manipulation?

[kdr1066]

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.

 

[Fazza]

Welcome. Due mainly to the volume of data - though also the type of tasks being done and likely multitude of various report required - I suggest that the task be done not in Excel, rather in Access. Perhaps retain some use of Excel for users to define the selections they want - users prefer the familiarity of Excel - but get that data transferred into Access for working with costs, components & all the multi-dimensional levels of data.

 

[kdr1066]

Thanks Fazza, I agree, but unfortunately the client doesn't, it has to be Excel, my hands are tied. Migration to a database solution is on the way, but they still need an interim solution.

I do have a working version which I'll probably end up sticking with, but I can't help thinking there has to be a more elegant solution. Distributing bulky workbooks that are rather slow at times and will need updating with the latest data is less than ideal.

A component class, loaded from a central source, and used in collections sounds better, but I'm a newbie and it's taken me a while to get this far, so before I invest more time in learning about classes etc... I'd like some reassurance that the approach will work.

For example, if I create a class for the components and load all the cost data into it (or rather the instances of it), can I then create collections to hold groups of components (the higher level work units), including additional values such as the total cost of all the components in the unit, i.e. the summary level cost of the unit?

My requirements probably aren't that complex, I just need someone to point me in the right direction and I'll work my way through. You're suggestion of using Access is no doubt a better way to go, but given the constraint I'm working under could the use of classes and collections be viable?

 

[Fazza]

I suspect that a reasonable solution would be to not use arrays or collections but to set up like a database. Excel files can query other Excel files (like a database) - they can query themselves too - and run queries (like databases): having queries, and perhaps pivot tables, avoids needing ANY formulas in the workbooks. Reason being if you have large amounts of data and then many formulas the files will be slow. Formulas can really decrease efficiency.

You could have one large/central/master file with tables of costs/whatever and then the individual files could query that table for the specific data required - so the individual files would not need the full tables, they would just query the codes/lines they use.

As it is an interim solution, I wouldn't get super-fancy wrt the individual files and some sort of version control. If these files need to be revised in time, just re-issue a totally new file and have users copy their data from the old file to the new. (It gets a bit trickier to make an add-in file separate from the user data files.)

HTH

 

[Fazza]

Another thought, the large/central/master file could be an mdb file instead. No-on need to have Access installed. Just store the main data in an mdb file and the Excel files could query it. No real reason to use mdb file but it does give more rigour to the data - I'm thinking data types (long, string, boolean) - and also scalability as the system grows.

Also with an mdb file there could be relationships defined between tables and queries. The Excel files could then query these mdb file queries to start to get pretty efficient.

Basically do as much as possible in the mdb file for efficiency.

 

[kdr1066]

Thanks again Fazza, sounds like a good approach, but as I've never written any SQL within a VBA Sub it's another learning curve for me to climb

Before I get started, can I just check a few points...

1. I should be able to create a mdb file to hold multiple data tables, with keys to link them?
2. I then write SQL queries in VBA to interact with the mdb to do things such as display the data on a worksheet, e.g. show all the records matching these criteria?
3. I should be able to minimise the amount of processing Excel has to do in terms of functions/calculations by writing queries to do the work in the mdb file?
4. I only need a single mdb file on a shared drive that all the users have access to?
5. The Excel workbook the users use communicates with the central mdb file to display the required data, i.e. query results, but doesn't actually need to hold the full dataset?
6. Presumably a user could save a copy of the resulting estimate (the work breakdown we're aiming to produce), which would contain just the subset of the data required?

I've found a few helpful resources to get me started by creating the mdb file and how to get data in and out, but now I need to turn the examples into the real solution.

Thanks again.

 

[Fazza]

Sounds good. Some links, of various relevance

http://www.xtremevbtalk.com/showthread.php?t=217783

http://support.microsoft.com/default.aspx?scid=kb;en-us;247412

http://www.excel-spreadsheet.com/vba/dao_ado.htm

http://datapigtechnologies.com/blog...xcel-1-filling-a-combobox-with-unique-values/

<TABLE style="WIDTH: 527pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=703 border=0 x:str><COLGROUP><COL style="WIDTH: 527pt; mso-width-source: userset; mso-width-alt: 25709" width=703><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 527pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=703 height=17>http://www.erlandsendata.no/english/index.php?t=envbadac</TD></TR></TBODY></TABLE>

 

[Fazza]

another link

http://www.mrexcel.com/forum/showthread.php?t=427548

 

[kdr1066]

Thanks for the links, I'm starting to make progress, having managed to create a mdb file with a recordset I can query from another Excel workbook. So far so good, but I now need to turn practice into the real solution, and have a couple more questions...

1. What is the relationship between tables and worksheets? Should I be holding my source data in a workbook with each table on an individual worksheet? Or could/should I use named ranges on a single worksheet instead?

2. How do the tables get stored? When I open the mdb file with Excel I can see the single table I've managed to create so far. Will/should additional tables be stored on additional worksheets? I'm asking because the worksheet name is the database name, not the table name, which suggests that the whole database goes on a single sheet. It could of course just be the code I'm attempting to use/modify, but it would useful to know what I should be aiming towards.

Thanks.

 

[Fazza]

Q1. Whatever suits you. If there are small tables you might have more than one on a worksheet, though generally one table per worksheet. Depending on specifics, you might have (some) worksheets very hidden - tables that end users don't need to see.

Q2. I'm not clear on the question. And it may require a good understanding of the whole task for me to comment with any authority. The general idea is have an mdb file that stores the large tables of data (data that does not need to be in the individual Excel files), and use the mdb file to define relationships between tables and other normal database functions, and, in the Excel file obtain specific inputs from users and then run queries to get the result. These queries might be run in the mdb file though also the Excel file. By using queries, particularly if there are very large amounts of data, will be more efficient than Excel worksheets containing many thousands of formulas. Ideally, the Excel file will have no formulas.

Exactly how you implement the general idea is up to you. As it is an interim solution, it shouldn't matter too much which way you decide to go on specific details.

hth