Array problem?
I have a user form with a textbox and two command buttons. The command
buttons are named Add and Done. When the user hits add I want the text in the textbox to be added to a variable array. When the user hits the done button I want to find each value in the array in the first column of the active worksheet and replace it with the minimum value from the array. How do I create this array? How do I pass the array to the done button click event? Private Sub Add_Click() Dim a() As Variant ReDim Preserve a(UBound(a) + 1) a(UBound(a)) = textbox1.Text End Sub This gives me a subscript out of range message. |
Array problem?
Declare the array only at the top of a module. If you only need to see it
in the Useform, then declare it at the top of the userform module. If you need to see it elsewhere in the project, then declare it at the top of a general module. Dim a() As Variant Private Sub Add_Click() On Error Resume Next ub = UBound(a) If Err.Number < 0 Then ReDim a(0 To 0) a(UBound(a)) = TextBox1.Text Else ReDim Preserve a(UBound(a) + 1) a(UBound(a)) = TextBox1.Text End If On Error goto 0 Label1.Caption = UBound(a) End Sub Private Sub Done_Click() For i = LBound(a) To UBound(a) Debug.Print i, a(i) Next End Sub worked for me. -- Regards, Tom Ogilvy "Rbp9ad" wrote in message ... I have a user form with a textbox and two command buttons. The command buttons are named Add and Done. When the user hits add I want the text in the textbox to be added to a variable array. When the user hits the done button I want to find each value in the array in the first column of the active worksheet and replace it with the minimum value from the array. How do I create this array? How do I pass the array to the done button click event? Private Sub Add_Click() Dim a() As Variant ReDim Preserve a(UBound(a) + 1) a(UBound(a)) = textbox1.Text End Sub This gives me a subscript out of range message. |
Array problem?
You can give this a try... I changed the variable to a static array of
strings, since you probably want the array to persist and a text box by default contains strings... Private Sub Add_Click() Static a() As String Dim lngArrayCounter As Integer lngArrayCounter = -1 On Error Resume Next lngArrayCounter = UBound(a) On Error GoTo 0 If lngArrayCounter = -1 Then ReDim Preserve a(0) Else ReDim Preserve a(lngArrayCounter + 1) End If a(UBound(a)) = textbox1.Text End Sub -- HTH... Jim Thomlinson "Rbp9ad" wrote: I have a user form with a textbox and two command buttons. The command buttons are named Add and Done. When the user hits add I want the text in the textbox to be added to a variable array. When the user hits the done button I want to find each value in the array in the first column of the active worksheet and replace it with the minimum value from the array. How do I create this array? How do I pass the array to the done button click event? Private Sub Add_Click() Dim a() As Variant ReDim Preserve a(UBound(a) + 1) a(UBound(a)) = textbox1.Text End Sub This gives me a subscript out of range message. |
All times are GMT +1. The time now is 08:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com