Does Excel Like Arrays?
Posted
Tue, Sep 27 2011 20:36
by
Nate Oliver
Good evening, I hope this finds everyone well!
Tonight we'll look at something a little eccentric - does Excel like Arrays? Most power-users know the answer is yes! But, let's use an example.
Let's say you've created a Userform, and want to store the values of a dynamic amount of Textbox values in a Variable? How is one to approach this problem? Enter the Array. I have written some potentially relevant code to this quandary, as follows:
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim strArr() As String
Dim i As Long, j As Long
ReDim strArr(1 To 500, 1 To 500)
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox Then
Let i = i + 1
Let strArr(i, 1) = ctrl.Name
Let strArr(i, 2) = ctrl.Text
End If
Next
'For j = 1 To i
' Debug.Print Me.Controls(strArr(j, 1)).Text, _
' strArr(j, 1), strArr(j, 2)
'Next
MsgBox WorksheetFunction.VLookup( _
strArr(2, 1), strArr, 2, 0)
Me.Hide: Unload Me
End Sub
I've commented-out my 'for j' loop, as that was a test, and I hardcoded the Lookup value (this is only left to the imagination). I'm attaching a file demonstration of what this could look like, you should find yourself with TextBox2's value.
I'm not 100% sure why you would do this, but that's not what we're here for, the point is more that you can do this. And I can almost see why one would do this, with an emphasis on almost, if you had to process this information over and over, again.
What is this doing? It's looking through the Userform for Textboxes, then burns their information to a 2-D Array. Excel can crunch well-structured Arrays like few pieces of software can. Which is exactly what VLookup() is doing, it's looking at the first vector, and moves to the second, in this case, a sequential search.
Have a great night!
-Nate