VBA - Array Help

[TheNoocH]

I'm in the process of trying to teach myself arrays...
for the most part i've been using cells in a worksheet to handle what i want to do...but i want to try and avoid using the worksheet...i'm assuming arrays would be the best replacement...

here's essentially what i want to try and accomplish...

1) enter data into an input box on a form and store it based upon one of 4 buttons i press....essentially i had 4 columns on the sheet and found the last row and stored the data in 1 row offset....assume for each command button that right(commandbutton,1) = 1, 2, 3, 4 to identify which column to store the data...

2) how do i declare the multidimensioned array especially since it will be dynamic in row size...the column size will always be the same...

3) any other basic info that may help me...

thanks for any help in getting me up to speed in arrays...

 

[hatman]

Well, you start out by declaring a dimensionaless array:

dim arr() as string

The empty parentheses creates a placeholder for the array, which can be any size in any number of dimensions.

Then in your code, you establish the size:

redim arr(1 to 10, 2 to 63)

If you want to change the size of the array after initializing it, you can, but you lose all current values if you use redim... if you want to retain the existing values, then use the Preserve switch:

redim preserve arr(1 to 10, 2 to 100).

But when using the Preserve switch, you can only resize the LAST dimension... remember in arrays that the system does not thin in terms of rows versus columns, only in heirarchical orders of dimensions... so try to get out of that though pattern early. The othe rthing to remember when resizing using the Preserve switch is that you can't change the lower bound of the last dimension, only the upper bound.

 

[TheNoocH]

hatman....thanks...
i think i understand what you got there...my issue is applying it...
can you help me with code for this simple example...then hopefully i'll have a better understanding of how to apply it...

eg
1) dim arr() ...got it...this shows we'll use a dynamic array
2) everytime i click button1 i want to take the last value in the array (in col 1) and add 1 to it and put that as the new last value in the first column in the array...so if the arrary is blank then it will create the first element as 1...if the array already has 1 as 1st element then it'll place 2 as the 2nd element, etc...
3) everytime i click button2 i want to take the last value in the array (in col 2) and add 2 to it and put that as the new last value in the second coumn in the array...
4) then just create a msgbox showing the data in the array

my main issue is how to do the
redim preserve when adding new data...

and format for storing the data into the array...

any help would be great...

2) i want to add data to the array

 

[hatman]

The use of a command button to drive this adds a scope issue to the problem. We can treat that as a separate issue, if you are intent upon doing it like that. However, understand that it has the potential to distract you from the original question.

Here is some code that does what you describe. To see it work, step through in the debugger using the F8 key... make sure you have the Locals Window visible, and watch the array change:

Sub array_sub()

    Dim arr() As Long
    Dim cnt As Long
    
    ReDim arr(1 To 2, 1 To 1)
    
    For cnt = 1 To 5
    
        ReDim Preserve arr(1 To 2, 1 To UBound(arr, 2) + 1)
        
        arr(1, UBound(arr, 2)) = arr(1, UBound(arr, 2) - 1) + 2
        arr(2, UBound(arr, 2)) = arr(2, UBound(arr, 2) - 1) + 2
    
    Next cnt
    
    Range("a1:f2").Value = arr

End Sub

 

[TheNoocH]

i "THINK" i understand...i guess it's going to take a while for it to completely sink in...

one last question...say i wanted to sum the first (of the 2) rows of data...

if it was single dimension i could just do
worksheetfunction.sum(arr)
i tried that but it summed everything in the array (ie both rows)

how would i do it for just the first row or just the second row...

would i be better off using multiple single dimension arrays instead of one multidimension array?

thanks...

 

[hatman]

First: try to stop thinking of arrays in terms of Rows and Columns. They don't exist. Instead, try to think in terms of dimensions. After all, if I summed the third dimension of arr(1 to 10, 1 to 20, 1 to 30, 100), would that be a row or a column?

If I wanted to sum all of the values of arr(1,n) I would write

 for cnt = lbound(arr,2) to ubound(arr,2)
    tot = tot + arr(1,cnt)
next cnt

 

[TheNoocH]

ok...that does the trick...thanks for all your help...let me try and digest all this...i know i'm making it harder than it really is...

 

[hatman]

No, not making it harder: this is NOT an easy concept. I sometimes take it for granted since I picked it up when I was young, but I admit they are difficult to visualize... which is one reason to try to minimize the use of row/column imagery: it limits as much as it enlightens.

You've obviously been around here longer than me, so I won't insult you by telling you how this place works... but I will still encourage you to ask questions.

Wish I had an article or three to offer that would do a better job than me...

 

[TheNoocH]

hatman...thanks for all your help...

since i'm basically a beginner/intermediate programmer, i just cheat in my code by utilizing the spreadsheet since i can get around in that fairly well in vba...so i write and retrieve info to/from the spreadsheet instead of properly storing them in variables or arrays...

my goal is to try and stop using the spreadsheet as a crutch so i can port some of the stuff i did in vba into vb so i can compile it to an EXE...

i think if i can get the whole "array" concept down it'll go a long way for me...but for some reason it's taking longer to grasp then i would have thought...

i have vba and macros for microsoft excel from mr excel himself...but they only touch on arrays in there and most of it is reading data from the worksheet and storing into an array....maybe my best bet is to look at my vb docs...they may hit on it more...

thanks again for your help....

 

[hatman]

My observation, in the time I have been on this board, is that most Excellers avoid the use of arrays in general... if they know how to use them at all. For exactly the reasons you have eschewed arrays until now.

I could name a couple of gurus here who I know are comfotable using arrays, but they seldom do, because often it is the hard way within Excel. And I am sure there are a few other who know how, but I have never seem them demonstrate their ability. And I would venture to guess that all of them probably learned to program outside of Excel FIRST (as I did). Without Excel, and the ability to use sheets to hold data, arrays are the only way to hold certain types of data.

That being said, a well structured multidimensional array can do the duty of an entire relational database... which gives you a potential tool that is difficult to realize using sheets as storage places for data.

So stick with it! Sounds like you have good reasons for figuring this out, which means you WIL get it.

I'll keep my eyes open, and if I see anything that might help you, I'll post it.