Button click event in vb

Question

I am newbie in VB. I want to store the values in an array when I am clicking the first button and show the result when I am clicking the second button. I am successfully stored the values in an array. But i cant access the same array in the second button click event..

Dim i As Integer
Dim ag(0 To 7000) As String
Dim bg(0 To 7000) As String

Private CommandButton1_Click()
  i = 0

  Sheets("New").Select
  Range("B2").Select

  While Not IsEmpty(ActiveCell)
    ag(i) = ActiveCell.Value
    i = i + 1
    ActiveCell.Offset(1, 0).Select
  Wend

  i = 0

  Sheets("New").Select
  Range("D2").Select

  While Not IsEmpty(ActiveCell)
    bg(i) = ActiveCell.Value
    i = i + 1
    ActiveCell.Offset(1, 0).Select
  Wend
End Sub

Private CommandButton2_Click()
  UserForm1.Hide
End Sub

Private Sub Cell_Click()
End Sub

Private Sub CommandButton1_Click()
End Sub

Private Sub CommandButton2_Click()
End Sub

Any one can help me please.

Answer 1

Nimmy

My post is not about answering your main question :) If you look at Ken's and Cody's comment then you will automatically realize what the answer is as ;)

I couldn't help comment when I saw your code and your statement that you are a newbie. I remember my days when I was learning coding and forums like SO actually helped me enhance my coding skills. So you can consider this as a payback :-D

1) In your case it is ok that you have dimmed i as integer but what happens when you are dealing with rows which are much bigger for example 32768 rows. It's safe to dim i as long when working in VBA Excel.

2) .Select are a major cause of errors when working in VBA and not to mention that they slow down your code. The same code can also be written as the code given below. I am assuming that there is no blank values in between the 1st row and the last row.

Dim i As Long
Dim ag(0 To 7000) As String
Dim bg(0 To 7000) As String
Dim ws As Worksheet

Private CommandButton1_Click()
    Set ws = Sheets("New")

    With ws
        For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
            ag(i) = .Range("B" & i).Value
        Next
        For i = 2 To .Range("D" & .Rows.Count).End(xlUp).Row
            bg(i) = .Range("D" & i).Value
        Next
    End With
End Sub

HTH and yes, Happy Coding ;)

Sid