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...

 

[Norie]

TheNooch

What are you actually trying to do?

Are trying to substitute reading/writing/manipulating data using worksheets with manipulating arrays that are populated from a worksheet?

 

[TheNoocH]

hey norie...

no i want to avoid writing to the worksheet at all....

my goal is to have a stand alone app...right now i created something with VBA and would like to port it to VB...the only way i think i could do that is by replacing my code that reads and writes to worksheet to have it read and write to a 2-dimension array...

i'll explain what i have:
i have a userform where someone enters data in a text box presses one of 4 command buttons to determine which column i'll store the data...i find the last row for that column and add the data to end of that column...
then i would subtract the sum of the column from a value stored in a public variable...

so i guess i essentially want to replace sections of my worksheet where i am storing my data with an array...so seems fairly basic...but i just don't "GET IT" yet...and the "IT" being the particulars of declaring, reading, writing, etc to the array dimensions and elements....hatman was very helpful and he helped me grasp some of the basics...but for some reason i'm having issues once i get past the basics...i'm sure it's just lack of exposure to using them...

 

[Norie]

TheNooch

Looks as though I got the wrong end of the stick.

A lot of the array stuff you see on here is when people want to manipulate data without having to reference the worksheet.

eg fill array from worksheet, muck about with data, put data back on worksheet

A simple example.

Sub Mult2()
Dim arrValues
Dim I As Long
    
    arrValues = Range("A1:A10")
    
    For I = LBound(arrValues) To UBound(arrValues)
        arrValues(I, 1) = arrValues(I, 1) * 2
    Next I
    
    Range("A1:A10") = arrValues
    
End Sub

 

[TheNoocH]

norie,
thanks anyway...

 

[hatman]

BTW: I know I moved the topic away from the Form driven environment where you eventually want to employ this array... I hope I didn't confuse things by for you by focussing on the array in general (in an environment other than your initial vision).

Anyway, the issue of scope becomes where to put code so the data is persistant between command button clicks. Another challenge is how best to initialize the array. Of equal import is deciding when to expan the size of the second dimension of the array, and when to simply populate an empty location. Here are changes I would make to my original code to expand it to the scope you are looking for. I assumed that you woul dlike th eintial values of the array to be zero, which creates two conditions in the If..Then statement. If you initialize the array arr(1,1) = 1 and arr(2,1) = 2, then the first prt of the 2 If..then statements disappear.

standard module:

Global arr() As Long

Sub arr_help()

    ReDim arr(1 To 2, 1 To 1)
    
    UserForm1.CommandButton1.Caption = "Press to add 1"
    UserForm1.CommandButton2.Caption = "Press to add 2"
    UserForm1.CommandButton3.Caption = "Press to see data"
    
    UserForm1.Show
    

End Sub

in a userform with 3 command buttons:

Private Sub CommandButton1_Click()

    For cnt = LBound(arr, 2) To UBound(arr, 2)
    
        If arr(1, cnt) = 0 And cnt = LBound(arr, 2) Then
        
            arr(1, cnt) = 1
            
            Exit Sub
    
        ElseIf arr(1, cnt) = 0 Then
            
            Exit For
            
        End If
    
    Next cnt
    
    If cnt > UBound(arr, 2) Then
        
        ReDim Preserve arr(1 To 2, 1 To cnt)
        
    End If
    
    arr(1, cnt) = arr(1, cnt - 1) + 1

End Sub

Private Sub CommandButton2_Click()

    For cnt = LBound(arr, 2) To UBound(arr, 2)
    
        If arr(2, cnt) = 0 And cnt = LBound(arr, 2) Then
        
            arr(2, cnt) = 2
            
            Exit Sub
    
        ElseIf arr(2, cnt) = 0 Then
            
            Exit For
            
        End If
    
    Next cnt
    
    If cnt > UBound(arr, 2) Then
        
        ReDim Preserve arr(1 To 2, 1 To cnt)
        
    End If
    
    arr(2, cnt) = arr(2, cnt - 1) + 2

End Sub

Private Sub CommandButton3_Click()

    For cnt1 = LBound(arr, 2) To UBound(arr, 2)
    
        For cnt2 = LBound(arr, 1) To UBound(arr, 1)
        
            output = output & arr(cnt2, cnt1) & " "
            
        Next cnt2
        
        output = output & vbCrLf
        
    Next cnt1
    
    MsgBox output
    
End Sub

 

[TheNoocH]

hatman...thanks for the code...i think that's closer to what i'm looking for...i was having issues when the # of data points weren't the same size...at quick scan it looks like your code should handle that...i printed this out and will play with it tonight (if i get a chance) or tomorrow...i'll let you know how i make out...

ps...i created class modules for my command buttons...so the code is actually being run from modules not from behind the form...

pss...you didn't confuse me...programming confuses me...haha...i'm still a hack...but learn a little at a time...

 

[hatman]

My friend, just about all of us are hacks, myself included

Hang in there! I expect there are a couple of landmines in what I gave you, since I didn't test it too rigorously, so be sure to ask if soemthing looks odd.

Whether it's in a Class Module or behind a form, the structure should be the same... if the vehicle confuses you, take a stab at moving the code over, and I'll try to help smooth it out if you have difficulties. And remember to keep your scope in mind... don't be afraid to declare your Array as Static if you need to... but if you do, be sure to either release the memory when you are done, or re-initialize properly the next go-around.

 

[TheNoocH]

i think i'm starting to follow what you put in the code...trying to mark up my code to see where i have to change...

BUT...one thing i noticed is that my example may have caused some confusion...note: your code did what my example asked for...

i don't really want to add 1 or 2 when button clicked but rather add what is in an input box...and sometimes that could be a 0...

but in reality 0 is a valid entry that can be in the data...so how can we adjust for that? sorry...if we can figure that out i'll try and put into my code and cross my fingers...ha...

 

[hatman]

Somthing more like this, I would think:

Sub arr_help()

    ReDim arr(1 To 2, 0 To 1)
    arr(1, 0) = 1
    arr(2, 0) = 1
    
    UserForm1.CommandButton1.Caption = "Press to add 1"
    UserForm1.CommandButton2.Caption = "Press to add 2"
    UserForm1.CommandButton3.Caption = "Press to see data"
    
    UserForm1.Show
    

End Sub

Private Sub CommandButton1_Click()

    adder = InputBox("Enter value")
    
    If Not IsNumeric(adder) Then
    
        Exit Sub
        
    End If

    arr(1, 0) = arr(1, 0) + 1
    
    If arr(1, 0) > UBound(arr, 2) Then
        
        ReDim Preserve arr(1 To 2, 0 To arr(1, 0))
        
    End If
    
    arr(1, arr(1, 0)) = arr(1, arr(1, 0) - 1) + adder

End Sub

Private Sub CommandButton2_Click()

    adder = InputBox("Enter value")
    
    If Not IsNumeric(adder) Then
    
        Exit Sub
        
    End If
    
    arr(2, 0) = arr(2, 0) + 1
    
    If arr(2, 0) > UBound(arr, 2) Then
        
        ReDim Preserve arr(1 To 2, 0 To arr(2, 0))
        
    End If
    
    arr(2, arr(2, 0)) = arr(2, arr(2, 0) - 1) + adder

End Sub

Private Sub CommandButton3_Click()

    For cnt1 = 1 To UBound(arr, 2)
    
        For cnt2 = LBound(arr, 1) To UBound(arr, 1)
        
            output = output & arr(cnt2, cnt1) & " "
            
        Next cnt2
        
        output = output & vbCrLf
        
    Next cnt1
    
    MsgBox output
    
End Sub

 

[hatman]

And if you follow that, the better way of handling this for code re-use would be:

Private Sub CommandButton1_Click()

    Call add_to_arr(1)

End Sub

Private Sub CommandButton2_Click()

    Call add_to_arr(2)
    
End Sub

Private Sub CommandButton3_Click()

    For cnt1 = 1 To UBound(arr, 2)
    
        For cnt2 = LBound(arr, 1) To UBound(arr, 1)
        
            output = output & arr(cnt2, cnt1) & " "
            
        Next cnt2
        
        output = output & vbCrLf
        
    Next cnt1
    
    MsgBox output
    
End Sub

Private Sub add_to_arr(dmsn As Long)

    adder = InputBox("Enter value")
    
    If Not IsNumeric(adder) Then
    
        Exit Sub
        
    End If
    
    arr(dmsn, 0) = arr(dmsn, 0) + 1
    
    If arr(dmsn, 0) > UBound(arr, 2) Then
        
        ReDim Preserve arr(1 To 2, 0 To arr(dmsn, 0))
        
    End If
    
    arr(dmsn, arr(dmsn, 0)) = arr(dmsn, arr(dmsn, 0) - 1) + adder

End Sub