so i'm trying to teach myself how to do data manipulations in arrays
instead of in the excel cells directly. currently, i'm simply
importing myrange into one large array, but i don't think this is the
best way about it since myrange includes multiple data types (dates,
$$$, strings, numbers/integers, etc). Should i be using a unique
array for each unique data type? ex - insteda of myarray() i would
have datearray(), balancearray(), acctnumarray(), etc... so instead
of one large two dimensional array i would have multiple one
dimensional arrays. does this sound right?
I load sheet data into a variant so it doesn't matter what data type is
in the cells...
Dim vData
vData = ActiveSheet.UsedRange
...which gives me a 1-based 2D array matching the sheet cols/rows. Now I
can access any part of the array as desired. For example, say account
data is in col 5 and I want to look through it for specific account
IDs/names (I use names because they're more descriptive than
numbers)...
Dim vTemp
vTemp = Application.Index(vData, 0, 5)
...so now the entire accounts info is in its own 1-based 2D array. You
can also just work that col of the array with loading it into a
separate array. You can put vTemp back into vData after you're done
with it...
Application.Index(vData, 0, 5) = vTemp
...and then 'dump' the array back into the sheet when done 'working' the
data like this...
Cells(1, 1).Resize(UBound(vData), UBound(vData, 2)) = vData
The only time you would want to put a 2D array into a 1D array is (IMO)
if you write the data to a file. Otherwise, trying to work with 1D/2D
at the same time will inevitably cause confusion and
incorrect/unexpected results.
Optionally, you can also use ADODB and work with recordsets so you can
specify criteria for the contents.
As it happens, I'm in the midst of building an example roject that
demos managing data with arrays. It shows a userform with individual
textboxes for each field (good for few fields) and a userform using a
listview control. I'll post a download link shortly as it's nearly
complete (just waiting for me to add 'readme' stuff).
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
