Filtering array or similar

[Solaris]

Hi!
I have a small problem where I'm creating a database and a separate excel file for doing calculations with values found in the database. Since the database is in another file I want to minimize reading from that file to improve performance, so I read the database once and put everything into an array. I then have a userform where the user can put in values in comboboxes to find the desired data. What I've have been doing so far is opening the database a second time and using the AutoFilter to find the data there, but to improve performance I would like to skip this part and filter directly in the array that already has all the data in it.

Is there any way to filter directly in an array? The array is structured identically to a worksheet (it's pretty much a direct copy from the database) as array(rows,columns) if that helps clarify. Would be delighted for any help!

Regards,
Samuel

[Jakobshavn]

I have found that filtering is very fast, whether applied manually or via VBA. You might not get much performance improvement.

[Kyle123]

There are a few things you could try:

1. Reading the data not into an array, but querying the workbook directly with SQL and into a Recordset object. You'd have to play about a bit to see whether it's more effiecient to use the in-built filtering of the recordset or requery he source workbook.
2. Copy the data into the active workbook and filter that - if it's alread in an array, you're halfway there
3. Loop through your array to pull out the data you're after - looping through in memory arrays should be pretty fast
4. (My Favourite) Ditch the existing workbook and use an actual database for holding your data - this will be much, much faster and you can query it as much as you like

[Solaris]

Thank you very much for the replies!

@Jakob, it's not the filtering that is taking time, but opening the external workbook, applying the filter, reading from the modified workbook and then closing it every time I change something in a menu. The actual functionality I'm implementing is a number of coupled comboboxes, where selecting a value in one will narrow down possible selections in the others.

@Kyle, those are all alternatives. After searching around for a while I've found the WorksheetFunction.Transpose command, which seems to be a lot quicker than looping through the data to copy it into a worksheet. Is there a similar way to read from a workbook into an array?
I also like number 4, but since I have never worked with databases or access I fear that it would take a lot of time to get into, which is something I don't really have right now. Especially since the structure and code for using the other workbook as a database already is in place.

[Kyle123]

You don't ever loop to fill an array as it's really slow (unless there's a really good reason), consider the below:

Please Login or Register  to view this content.

Although I suspect option 1 would be faster

[Solaris]

Holy crap that's so much faster, thanks a bunch for helping me out! :D

[Solaris]

Sorry for double-posting, now I've run into another problem. Your way of reading and writing to/from an array works like a charm when I try it small-scale in a new workbook, but when I try to implement it into my actual code I get a "run-time error '1004': application-defined or object-defined error". Here's a copy of my code:

Please Login or Register  to view this content.

Here I have removed the irrelevant parts of the code. r is global and is the number of rows in the other workbook, and c is the number of columns. array is a global Variant as it is used in many other subs that perform calculations. wbpath is the path to the workbook. The dimensions are (r,c)=(98,69) if that is relevant.

Cheers,
Samuel

[Kyle123]

Ok, lets start with the simple stuff, change the name for your variable to something else. You shouldn't use variable names that are the same as function names - array is a function.

Put a breakpoint in, what are the values of r and c? I'm guessing that

Please Login or Register  to view this content.

Should really be:

Please Login or Register  to view this content.

Just how much data are we talking, how many rows and columns?

[Solaris]

98 rows, 69 columns at the moment, though the actual thing will be a lot larger later on. I'm guessing around 1000 rows and 100 columns. The array isn't actually named like that in the code, I just renamed it here to make things more obvious, which in hindsight may have been a bad idea. With breakpoints I can now read into the array (though I didn't need them when I tried it in another workbook - weird).

So, that solved the problem, thanks a lot for the help!

Regards,
Samuel