VBA Arrays-A few useful functions

Why I am creating this thread:
Firstly I should make clear that I am not the best qualified to write this thread (I only started learning arrays less than a month ago). I am creating this thread because I recently had an urgent need to rapidly learn how to use arrays and I was greatly helped by Excel Forum users so I wanted to give back in some small way. Hence this thread.

What this thread is:
A list of functions to allow array newbies (such as myself) to create/manipulate/write 1D & 2D arrays without requiring expert knowledge on how to work with arrays.

Disclaimer:
As I am new to arrays, I have no doubt that this code greatly needs improvement.
As such, I take no responsibility if the code doesn't do what you expect - in other words, use at your own risk

If you are experienced with arrays and can see anything that should change, please post in this thread so I can update the code. Thank you.

I will try to update this thread soon to include some brief instructions on what the functions do and how best to apply them.


Arrays Function Module No 1 (This deals with reading/writing arrays to/from Active/Closed/New workbook)

Please Login or Register  to view this content.

Arrays Function Module No 2 (This mostly deals with manipulating arrays e.g. sorting/rebuilding etc.)
Part 1 of 2

Please Login or Register  to view this content.

Arrays Function Module No 2 - Part 2 of 2 (Copy & Paste into the same module as the Part 1)

Please Login or Register  to view this content.

Moderators - I can't edit post #1 any more...

Have now split the functions into 4 modules and am making many changes to the code (it needed it!). I only have time to upload the first module (more coming next week). It has a more consistent naming approach to arguments + explanations on the use/logic of the arguments.

N.B. This code is incompatible with post #1s version. If you have used code from post#1, you will need to manually correct the arguments from all calling subs

UPDATE: I have renamed the functions again (I thought "create" was a better choice then "build". Build implies creating in steps - like looping).

Please Login or Register  to view this content.

Originally Posted by AB33

Personally, I would prefer to use arrays on procedures for code efficiency.

what does that mean? there is no difference between functions and subs other than the fact that functions (typically) return a value-neither is more efficient

Originally Posted by AB33

I normally use arrays in a loop, so instead of looping through a range,it is much faster to dump the range in to an array.

We agree on this. (My first module creates arrays from reading a range. I only use loops in arrays when I wish to create arrays from existing arrays).

Originally Posted by nilem

Hi mc84excel
as an option your first function (Build2DArrayFromWSInActWB but not Build1Dor2DArrayFromWSInActWB)

Thanks for contributing this code. I prefer to have the option to return a 1D array from the same function however. (In some cases I wish to return a 1D array from a worksheet range)

Here is the updated part #4. (Updated Parts #2 & #3 in more recent posts)

Please Login or Register  to view this content.

And the updated part #3 (updated part #2 to come). This needs tidying up. I am sure the BubbleSort function (2nd column part) could be written to be more efficient.

Please Login or Register  to view this content.

Reworked. Latest version of arrays in attached workbook.

Here is the latest version. It will probably be the last. If anyone wishes to take over and upload their own versions to this thread, be my guest. (Please provide brief explanations of the new functions/reason for changing existing functions, thanks)