PDA

View Full Version : Dynamic Array



dzogchen
04-02-2011, 05:38 AM
Hello,

I have 6 TextBoxes which I want to retrieve values to the "a" Array.

This is the code that I have develop so far:

Private Sub CommandButton1_Click()
Dim box, i As Integer
Dim a As Long
Dim textboxes() As TextBox
box = 0
If TextBox1 <> "" Then box = box + 1
If TextBox2 <> "" Then box = box + 1
If TextBox3 <> "" Then box = box + 1
If TextBox4 <> "" Then box = box + 1
If TextBox5 <> "" Then box = box + 1
If TextBox6 <> "" Then box = box + 1
ReDim a(0 To 6)
For i = 0 To box
a(i) = textboxes(i)
Range("a1").Value = a(i)
Next i
End Sub

The name of this post is Dynamic Array because I would like to ignore empty TextBoxes and create the Array dimension dependent of TextBoxes,

but a(i) = textboxes(i) is not working

Could someone give me a hand please?

Regards

Paul_Hossler
04-02-2011, 06:38 AM
"is not working" is a little fuzzy, but I'd guess that you need to Dim a as an array of Long, and not just a Long:


Dim a() As Long


Paul

dzogchen
04-02-2011, 07:31 AM
Thank you Paul!

Additional information "textboxes(i)" is giving me Subscript out of range (Error 9).



Regards

Bob Phillips
04-02-2011, 07:39 AM
Try



Private Sub CommandButton1_Click()
Dim box, i As Integer
Dim a() As msforms.TextBox
box = 0
If TextBox1 <> "" Then box = box + 1
If TextBox2 <> "" Then box = box + 1
If TextBox3 <> "" Then box = box + 1
If TextBox4 <> "" Then box = box + 1
If TextBox5 <> "" Then box = box + 1
If TextBox6 <> "" Then box = box + 1
ReDim a(1 To 6)
For i = 1 To box
Set a(i) = Me.Controls("Textbox" & i)
Range("a1").Value = a(i)
Next i
End Sub

mikerickson
04-02-2011, 08:27 AM
Perhaps
Private Sub CommandButton1_Click()
Dim box, i As Integer, pointer As Long
Dim a() As msforms.TextBox

ReDim a(1 to 6)
pointer = 1
For i = 1 To box
Set a(pointer) = Me.Controls("Textbox" & i)
If Me.Controls("Textbox" & i).Value <> vbNullString Then
Range("a1").Value = a(pointer).Text
pointer = pointer + 1
End If
Next i
ReDim Preserve a(1 to (pointer - 1))
End Sub

Kenneth Hobs
04-02-2011, 11:32 AM
Option Explicit
Option Base 0

Private Sub CommandButton1_Click()
Dim i As Integer, sTB() As String, c As Control
i = -1
ReDim sTB(Me.Controls.Count - 1)
For Each c In Me.Controls
If TypeName(c) = "TextBox" And i < 6 And c.Value <> Empty Then
i = i + 1
sTB(i) = c.Value
End If
Next c

ReDim Preserve sTB(i)
Range("A1").Resize(UBound(sTB) + 1).Value2 = WorksheetFunction.Transpose(sTB())
Unload Me
End Sub