VBA Arrays: Loading with Range vs Loop and selecting data given Criteria

Please Login or Register  to view this content.

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Hello cmore,

If you need to apply or check formats, or access properties or methods of a Range then use a loop.

If you only need to copy the values of the range then use an array.

Hello cmore,

The restriction of using the array is you can only read or write to the Range cells. You can not change the cell properties, methods, or formatting.

All Range cells are Variant typed by default. The array also needs to be a Variant type. What ever the cell contains, the same cell in the array will be of the same Type.

Please Login or Register  to view this content.

The variable arr will be automatically dimensioned to match the Range. Ranges are always 2-Dimensional and 1 Based. That is the subscripts always start with one.

THANK YOU!! This has clarified so much of what I've been reading and mucking up. So if I evaluate the formula in a Named range, this won't technically be a Range object. I guess my second question then would be, how do I and is it possible to load specific elements into a new array based on criteria?

I've seen autofilter as the primary option, but seems inflexible given the header requirement. If I have array(0 to 9, 0 to 9), can I pull out items within Column 2 that meet certain criteria? The way I'm thinking now is, use a formula in a named range, get values then I just need a way to get rid of blanks/falses. But I'm not sure if there a better way of doing that, assuming I haven't moved in the world of collections

Data attached

Hello cmore,

There are 3 key Excel functions that you can use on your arrays: Transpose, Index, Match. There are a few more, but this are the most flexible.

Rather than use the WorksheetFunction class, I generally use the Application class. The Application class calls will not stop VBA if an error occurs. It is important to check the returned valued to make sure it is not an error before you proceed. This is easily done with Variants by using the VBA IsError(<variant>) method or by checking the Variant Type of the returned value using VarType(<variant>). If it is 10 then it is an error.

TRANSPOSE will "flip" an array. The columns become rows and rows become the columns. This is all done internally with no looping code needed.

INDEX allows you to return an entire row or entire column from an array. The down side is you can not use it to write a row or column of data. You have to use a loop.

MATCH allows you match an element in array and use the wild cards * and ?. Case is ignored. The relative position of the matched cell in the array is returned.

Please Login or Register  to view this content.

Hello cmore,

This post addresses the sorting issue. I created this routine a little over 3 years ago. It uses a bubble sort and the Text Compare function to sort the data. This macro is very fast because rather than move the data during the sort, it moves the variable pointers to the data. You will need to select the proper API call for your version of Windows. Only one call can remain in the macro.

Please Login or Register  to view this content.

Hello cmore,

Thanks for posting the new workbook. This is quite large. It will take me some time to digest what you are doing and what you want to improve.

Originally Posted by Leith Ross

MATCH allows you match an element in array and use the wild cards * and ?. Case is ignored. The relative position of the matched cell in the array is returned.

Hi Leith Ross, can you post an array match example ?

Originally Posted by Leith Ross

Hello cmore,

Thanks for posting the new workbook. This is quite large. It will take me some time to digest what you are doing and what you want to improve.

Sorry for the confusion, so ultimately, want to build a tool that reports testing data and the key driver seems to be extracting data and summarizing it, charting it, or using it in a some kind of activex-control to drive further analysis. Initially was using array formulas to do this, but at 5000+ rows it became a problem. So now I'm trying to find a smart way to reduce the named ranges and develop method to extract data and ultimately learn VBA/coding really.

So I was really wondering, if you look on the contents tab. I have an array worksheet formula vs. a macro tied to a controlbutton.

The control is loaded with named range that holds a formula operation that is then placed into an array with all non-blank strings. Based on what you're saying I should be able to use VB to cut the data against criteria, without naming the range? and/or Are there a other approaches I should consider?

Please Login or Register  to view this content.

Hello cmore,

You really have fallen in love with named ranges. So much so, I really can not follow the logic of what you are trying to do here. There are just too many levels of indirection for me to follow and I don't have the time to map them. If you can talk me through the process then I can offer you some guidance.