Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 178
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
More if then logic DJ Excel Worksheet Functions 2 October 8th 09 05:37 PM
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) Keith R[_2_] Excel Programming 3 November 13th 07 05:08 PM
Understanding the logic and criteria of Array formulas Starguy Excel Worksheet Functions 2 April 29th 07 02:34 AM
Array Formula Using Max Match Logic JR573PUTT Excel Discussion (Misc queries) 4 February 26th 06 06:09 AM
If Then logic not enough workerbeeVAB Excel Discussion (Misc queries) 4 January 5th 06 06:24 PM


All times are GMT +1. The time now is 11:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"