Excel VBA Sort an Array List

Im not even sure where to start with this coding, perhaps someone can post me a sample code for an example. Please note, I have never worked with array's before, so feel free to speak to me in n00b language.

The facts:
I have 8 text boxes on a form (in Excel VBA) each contains a time in format hh:mm AM/PM. Not all of these times will be in order, so textbox1 might be 08:00 AM, textbox2 10:00 AM, textbox3 09:00 AM... and so on.

What I need:
I need excel to sort these for me... so the array would pull from the textboxes and list first textbox1, then textbox3, then textbox2... and so on.

I assume I also need to know how to create the array, using Dim? and how to pull each value from the array to use in a formula in another procedure?

Thanks


- Desperate!

Hi Josh,

Before we get to the nitty gritty....
Once you have these values sorted in time order, what are you planning to do with them? Put them in a worksheet? "Re-order" your textboxes?

Colin

Once I have the times in order, I will be running a timer that will pop up a message as it reaches that time. During the timer, it would be using something like Do Until Time1 = now, then go to the next sub and Do Until Time2, ... and so on.

So I will need to be able to easily recall each of those times.

Hi Josh,

Okay - I'll take your question at face value and refer you to this very good tutorial on sorting data in arrays:

http://www.xtremevbtalk.com/showthread.php?t=78889

Have a look at it to see if this is the method you want to employ!

Hope that helps!
Colin

Okay, that looks like it should work according to the website to give me the capability to sort. Now back to basics...

I can declare an array as Dim Array1() as date
How do I add values to the array ?
How do I use the values from the array (ie, If Array1(val1) = Array1(val2) then...) ?

Yes, I know... sounds like a dumb question - I thought that answer would be easy to google for - but I guess a lot of people assume people know how to handle arrays already!

MSDN has an adequate section on array handling, have a look:

Array Basics (http://msdn.microsoft.com/en-us/library/aa261364(VS.60).aspx)
Dynamic Arrays (http://msdn.microsoft.com/en-us/library/aa716275(VS.60).aspx)
Advanced Array Features (http://msdn.microsoft.com/en-us/library/aa261343(VS.60).aspx)

SO... I created an array like this...

Dim arrayTimesheet(16) As Variant
'Set array values to each textbox value
arrayTimesheet(1) = txtLogIn.Text
arrayTimesheet(2) = txtBreak1Out.Text
arrayTimesheet(3) = txtBreak1In.Text
arrayTimesheet(4) = txtLunch1Out.Text
arrayTimesheet(5) = txtLunch1In.Text
arrayTimesheet(6) = txtBreak2Out.Text
arrayTimesheet(7) = txtBreak2In.Text
arrayTimesheet(8) = txtBreak3Out.Text
arrayTimesheet(9) = txtBreak3In.Text
arrayTimesheet(10) = txtLunch2Out.Text
arrayTimesheet(11) = txtLunch2In.Text
arrayTimesheet(12) = txtLogTime1.Text
arrayTimesheet(13) = txtLogTime2.Text
arrayTimesheet(14) = txtLogTime3.Text
arrayTimesheet(15) = txtLogTime4.Text
arrayTimesheet(16) = txtLogOut.Text

I now have to sort them and would like to use the code I located... but have no idea how to transfer my arrayTimesheet(16) to the code, nor how to call the sub routine from another sub routine.

Public Sub TimesheetTestTimesheetOrderStep3(ByRef lngArray() As Long)
Dim iOuter As Long
Dim iInner As Long
Dim iLBound As Long
Dim iUBound As Long
Dim iTemp As Long

iLBound = LBound(lngArray)
iUBound = UBound(lngArray)

For iOuter = iLBound + 1 To iUBound

'Get the value to be inserted
iTemp = lngArray(iOuter)

'Move along the already sorted values shifting along
For iInner = iOuter - 1 To iLBound Step -1
'No more shifting needed, we found the right spot!
If lngArray(iInner) <= iTemp Then Exit For

lngArray(iInner + 1) = lngArray(iInner)
Next iInner

'Insert value in the slot
lngArray(iInner + 1) = iTemp
Next iOuter
End Sub

Hi Josh,

Good work so far. :)

To incorporate it in your procedure you would just call it like this:


Dim arrayTimesheet(8) As Variant
'Set array values to each textbox value
arrayTimesheet(1) = txtLogIn.Text
arrayTimesheet(2) = txtBreak1Out.Text
arrayTimesheet(3) = txtBreak1In.Text
arrayTimesheet(4) = txtLunch1Out.Text
arrayTimesheet(5) = txtLunch1In.Text
arrayTimesheet(6) = txtBreak2Out.Text
arrayTimesheet(7) = txtBreak2In.Text
'etc etc etc....


TimesheetTestTimesheetOrderStep3 arrayTimesheet


But that will give you an error. Why do you think that is?

Also, it would be really good if you could think of a way to load the values into your array using a loop. After all, if you had 100 textboxes would you want to write 100 lines of code to assign the values into your array?

HTH
Colin

One other thing to look out for: As it says in "Setting Upper and Lower Bounds (http://msdn.microsoft.com/en-us/library/aa261364(VS.60).aspx)", "the default lower bound is 0", so your declaration produces an array with an empty element arrayTimesheet(0). This is usually not a problem, but could become one when sorting the array. So, in this case, the more explicit
Dim myArray(myLowerBound To myUpperBound) As '...

declaration style is preferrable. :)

Hi Josh,

Good work so far. :)

But that will give you an error. Why do you think that is?

Also, it would be really good if you could think of a way to load the values into your array using a loop. After all, if you had 100 textboxes would you want to write 100 lines of code to assign the values into your array?

HTH
Colin

I think the error would be because of the Long variable declaration. Also, not sure how you can incorporate a loop on this when the text boxes are named different.

Question though -

At any one given time, four of the text boxes will ALWAYS have time values within. However, it is unlikely that all 16 will have values, more like 8 most of the time. What is the best way to handle this, since the sort puts the empty boxes first?

Im thinking if I dont declare "x" number in an array ... Dim arrayTimesheet() rather than Dim arrayTimesheet(16) it will change sizes (i.e., sometimes have 4 values, sometimes 6, sometimes 16) etc. This would allow me to use an IF/Then to add each value if the text box is not blank.

Any suggestions on this?

One other thing to look out for: As it says in "Setting Upper and Lower Bounds (http://msdn.microsoft.com/en-us/library/aa261364(VS.60).aspx)", "the default lower bound is 0", so your declaration produces an array with an empty element arrayTimesheet(0). This is usually not a problem, but could become one when sorting the array. So, in this case, the more explicit
Dim myArray(myLowerBound To myUpperBound) As '...

declaration style is preferrable. :)

Isn't this why I declare Option Base 1? So that there is no 0 value?

Josh, I'm quite impressed. Considering that you started out not knowing anything about arrays a few days ago, you seem to have acquired a very clear understanding of them by now! :cool:
Im thinking if I dont declare "x" number in an array ... Dim arrayTimesheet() rather than Dim arrayTimesheet(16) it will change sizes (i.e., sometimes have 4 values, sometimes 6, sometimes 16) etc. This would allow me to use an IF/Then to add each value if the text box is not blank.
Precisely.
Any suggestions on this?
Take a look at how the sort function you're using employs the LBound and UBound functions to read the array size. That's what you would want to do every time you find a textbox that contains a value you want to add - look at the current array size, increase it by one (remembering what it said in the "Preserving the Contents of Dynamic Arrays" section of one of the links I posted earlier) and then storing the new value in the newly created last slot.
Isn't this why I declare Option Base 1? So that there is no 0 value?
Indeed, if you're using Option Base 1, there is no problem. I always declare arrays explicitly using the To keyword, thinking of it as a good coding practice, but if you're comfortable with Option Base then stick with that by all means. Just be aware that there are some situations in which VB-internal functions will return a 0-based array independent of that setting - as long as you're handling your own arrays, though, that needn't concern you. :)

I just switched the sort array to the Bubble sort rather than the Insertion sort, because the Insertion sort caused the first value to be the one that should have actually been at the very end - although all the rest of the values seemed to be in order.

I also created temporarily a solution to the prol of having the blank values sorted since not all text boxes have values... Simply tells me which value in the array to start with, rather than eliminating it altogether.

Dim testtempval As String
Dim i As Integer

i = 1
Do While testtempval = ""
testtempval = arrayTimesheet(i)
If testtempval = "" Then i = i + 1
Loop

intTimesheetArrayStartValue = i

That loop could be tightened up a little - you don't really need the tempval or the if statement inside.

But that'd just be a beautification. I guess you're all sorted now? (pardon the pun :))

Yes, thank you. Everything seems to be working out in the sorting array.