Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba array logic
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba array logic
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba array logic
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? Here's a link to samples using arrays to manage data. DataForms.xls demos using sheet-based only. ColorNamesManager.xls demos using both sheet-based data and storing that data in a text file. Look for "DataArrays.zip"... https://app.box.com/s/23yqum8auvzx17h04u4f -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba array logic
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? Here's a link to samples using arrays to manage data. DataForms.xls demos using sheet-based only. ColorNamesManager.xls demos using both sheet-based data and storing that data in a text file. Look for "DataArrays.zip"... https://app.box.com/s/23yqum8auvzx17h04u4f A new version of 'DataForms.xls' has been added to the zip archive since posting the link. Follow that same link to update... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More if then logic | Excel Worksheet Functions | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
Understanding the logic and criteria of Array formulas | Excel Worksheet Functions | |||
Array Formula Using Max Match Logic | Excel Discussion (Misc queries) | |||
If Then logic not enough | Excel Discussion (Misc queries) |