Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Working with arrays in VBA

I was just wondering about how I can work with arrays in VBA...

Two things I want to do,

Dynamically add string data to an array at runtime (I dont know how much data there will be so I would need a dynamic array size that can change at run time). Once populated sort the array.

Also I need a two dimensional array of strings which I can dynamically add to at runtime (again I will not know the size that the array will need to be). Any ideas?
0
Blowfelt82
Asked:
Blowfelt82
1 Solution
 
CluskittCommented:
You can redimension any array, as long as you don't change the number of dimensions:
Dim MyArr1 As String(3) = {"Str1","Str2","Str3"}
Dim MyArr2 As String(3,3) = {{"Str1","Str2","Str3"},{"Str4","Str5","Str6"},{"Str7","Str8","Str9"}}
Redim Preserve MyArr1(MyArr1.GetUpperBound(0)+2)
Redim Preserve MyArr2(MyArr2.GetUpperBound(0),MyArr2.GetUpperBound(1)+2)

This will redimension the arrays. In the first case, it simply adds a new "row". You need to add 2, because the GetUpperBound method is zero based, while the redim is 1 based (unless you specify 0 To... in which case it would be +1).
The second will redimension only the second dimension.
Both will keep their values due to the use of the Preserve keyword. Note that, if you Redim to a lower value, you'll lose the extra values but still keep the ones that "fit".
0
 
Blowfelt82Author Commented:
Ok that looks good, is there an easy way to sort the data? I have found stuff online for bubble sorts but these take forever! Would you recommend using a collection instead - I have dabbled with this but dont think there is an easy way to sort the data.
0
 
SteveCommented:
one of the fastest ways to sort the data is to put it into an excel worksheet and use the sort there.. then transfer back to the array.. far faster than a bubble sort.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Blowfelt82Author Commented:
Yeah I was thinking that might be an option - but was hoping VBA had access to that sorting function if possible.
0
 
CluskittCommented:
There is no easy way to sort arrays in VBA. VB.Net has an inbuilt .Sort method, but with VBA, you have to create your own function. If you need one, I can provide it. I believe the easiest way would be a dual loop and populating a new array.

I'm not too familiar with collections, but I believe that it would be a better solution for you, seeing as your array is so dynamic.
0
 
Blowfelt82Author Commented:
Yeah looking at collections it looks like the best option, just a matter of finding an easy way to sort it!!
0
 
masteripperCommented:
0
 
Blowfelt82Author Commented:
Looks interesting but I think Collections is the way forward...

All I want to do is read a files contents, order it ascending and be able to access it? It seems like Excel missed a trick by not providing any functionality to sort collections in it?
0
 
Martin LissRetired ProgrammerCommented:
Although I like them, collections are slow and I don't know any way of sorting them (which isn't to say there isn't a way). In any case here is some code which creates and sorts a Private Type array that eliminates the need for a two dimensional array. And QuickSort is, well, quick:)


Option Explicit

Private Type TwoDimensionSubstitute
    lngVar1 As Long
    strVar2 As String
End Type
Private MyArray() As TwoDimensionSubstitute


Private Sub Worksheet_Activate()
Dim lngIndex As Long

'Build test array
For lngIndex = 0 To 10
    If lngIndex > 0 Then
        ReDim Preserve MyArray(UBound(MyArray) + 1)
        MyArray(lngIndex).lngVar1 = Int(50 * Rnd + 1)
        MyArray(lngIndex).strVar2 = CStr(lngIndex)
    Else
        ReDim MyArray(0)
        MyArray(0).lngVar1 = Int(50 * Rnd + 1)
        MyArray(0).strVar2 = CStr(lngIndex)
    End If
Next
' Show the array before any sorting is done
Debug.Print "BEFORE"
For lngIndex = 0 To 10
    Debug.Print MyArray(lngIndex).lngVar1 & " " & MyArray(lngIndex).strVar2
Next

'Sort the array using QuickSort
QuickSort MyArray, LBound(MyArray), UBound(MyArray)

' Show the array after sorting
Debug.Print "AFTER"
For lngIndex = 0 To 10
    Debug.Print MyArray(lngIndex).lngVar1 & " " & MyArray(lngIndex).strVar2
Next

End Sub

Private Sub QuickSort(C() As TwoDimensionSubstitute, ByVal First As Long, ByVal Last As Long)

Dim Low As Long, High As Long
Dim MidValue As String

Low = First
High = Last
MidValue = C((First + Last) \ 2).lngVar1

Do
While C(Low).lngVar1 < MidValue
Low = Low + 1
Wend

While C(High).lngVar1 > MidValue
High = High - 1
Wend

If Low <= High Then
Swap C(Low), C(High)
Low = Low + 1
High = High - 1
End If
Loop While Low <= High

If First < High Then QuickSort C, First, High
If Low < Last Then QuickSort C, Low, Last
End Sub

Private Sub Swap(ByRef A As TwoDimensionSubstitute, ByRef B As TwoDimensionSubstitute)
Dim T As TwoDimensionSubstitute

T = A
A = B
B = T
End Sub

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now