Follow @dporton

Learn VBA : Sort an Array

I quite often, to get ideas for articles look at the things people search for on the site. And recently I did just that and noted that someone looked up how to do a VBA array sort. If that was you , then this quick article is for you.

First off lets define the array and populate it.

Dim oArray[10]
    oArray[0] = "J"
    oArray[1] = "I"
    oArray[2] = "H"
    oArray[3] = "D"
    oArray[4] = "F"
    oArray[5] = "G"
    oArray[6] = "E"
    oArray[7] = "B"
    oArray[8] = "C"
    oArray[9] = "A"

In this example we are going to do a bubble sort, but there are lots of different types. Bubble is by far the easiest at the cost of speed, it is by no means the fastest sort.

Dim iFirst,iLast,iCount,iNestCount
Dim sTemp,sList
    iFirst = LBound(oArray) 
    iLast = UBound(oArray) 
    For iCount = iFirst To iLast - 1 
        For iNestCount = iCount + 1 To iLast 
            If oArray[iCount] > oArray[iNestCount] Then 
                sTemp = oArray[iNestCount] 
                oArray[iNestCount] = oArray[iCount]
                oArray[iCount] = sTemp 
            End If 

So what does our code do, well we first of we find the first record in our array and then we find the last , and then we begin to loop our array. Next we start to loop the array again , starting with the element we are on and checking to see if any of the following elements are greater than the one we currently have selected in the first loop , if it is greater then we swap them around and then continue on , so to help you visualise this, this is what happens

iCount = 0,  Array[iCount] = J / iNestCount = 1,  Array[iNestCount] = I
SWAP,  Array[iCount] = I / Array[iNestCount] = J
iCount = 0,  Array[iCount] = I / iNestCount = 2,  Array[iNestCount] = H
SWAP,  Array[iCount] = H / Array[iNestCount] = I
iCount = 0,  Array[iCount] = H / iNestCount = 3,  Array[iNestCount] = D
SWAP,  Array[iCount] = D / Array[iNestCount] = H
iCount = 0,  Array[iCount] = D / iNestCount = 4,  Array[iNestCount] = F
iCount = 0,  Array[iCount] = D / iNestCount = 5,  Array[iNestCount] = G
iCount = 0,  Array[iCount] = D / iNestCount = 6,  Array[iNestCount] = E
iCount = 0,  Array[iCount] = D / iNestCount = 7,  Array[iNestCount] = B
SWAP,  Array[iCount] = B / Array[iNestCount] = D
iCount = 0,  Array[iCount] = B / iNestCount = 8,  Array[iNestCount] = C
iCount = 0,  Array[iCount] = B / iNestCount = 9,  Array[iNestCount] = A

and this keeps on going until we have looped round all the items in the array. Are final bit of code just lets us display the results.

    For iCount = 1 To UBound(oArray) 
        sList = sList + oArray[iCount] 

So there you have it some code to sort an array. I hope you find it useful. Please don’t forget if you cannot find what you want , leave a comment on the “ASK US post” and I will respond to you with an answer if I can.

Learn VBA : String Manipulation

The next article in our line of learn VBA will teach you how to use some of the most common string manipulation commands. These commands are easy to learn and are actually quite common among most programming languages. The commands we will look at are Len for “length”,Mid for “middle” , left & Right and then finally Replace and Split.

Read more

Learn VBA : If Statements

Now that we understand how we can declare our variables we need to start to learn the basic VBA code that we will use in our surveys. By now you should know how to .ask() or .show() your questions , so this article takes you to the next level and shows you how to apply logic to your surveys so that your respondents only get to see the questions that are relevant to them. In this article we talk about IF statements.

Read more

%d bloggers like this: