Excel VBA Function...Can someone explain this function to me?

danyochou · #1 (**permalink**) 10-21-09, 00:02

Hello,
I am taking a class...and I am having trouble on explaining this function. Can someone briefly explain what this function does? I believe you might have to assume some functions exist.

Sub My_test(My_line, ByVal Low As Long, ByVal Hi As Long, SortCol As Integer)
Dim MidValue, i As Long, j As Long, k As Long, Temp
If Hi <= Low Then Exit Sub
MidValue = My_line(SortCol, (Low + Hi) \ 2)
i = Low
j = Hi
Do While i <= j
If CSng(My_line(SortCol, i)) >= CSng(MidValue) And
CSng(My_line(SortCol, j)) <= CSng(MidValue) Then
If CSng(My_line(SortCol, i)) > CSng(My_line(SortCol, j)) Then
k = 1
For k = 1 To UBound(My_line, 1)
Temp = My_line(k, i)
My_line(k, i) = My_line(k, j)
My_line(k, j) = Temp
Next k
End If
i = i + 1
j = j - 1
Else
If CSng(My_line(SortCol, i)) < CSng(MidValue) Then i = i +
1
If CSng(My_line(SortCol, j)) > CSng(MidValue) Then j = j -
1
End If
Loop
My_test My_line, Low, j, SortCol
My_test My_line, i, Hi, SortCol
End Sub

pootle flump · #2 (**permalink**) 10-21-09, 03:12

Hi

Please could you wrap this code up in code tags? [ code] [/ code] without the spaces. This should also be indented correctly. It is very difficult to read code in variable width fonts without indents.

Also, since this is a class - we would expect you to at least have a go at explaining what you think this code is doing.

MikeTheBike · #3 (**permalink**) 10-21-09, 03:37

Quote:

Originally Posted by danyochou

Hello,
I am taking a class...and I am having trouble on explaining this function. Can someone briefly explain what this function does? I believe you might have to assume some functions exist.

Sub My_test(My_line, ByVal Low As Long, ByVal Hi As Long, SortCol As Integer)
Dim MidValue, i As Long, j As Long, k As Long, Temp
If Hi <= Low Then Exit Sub
MidValue = My_line(SortCol, (Low + Hi) \ 2)
i = Low
j = Hi
Do While i <= j
If CSng(My_line(SortCol, i)) >= CSng(MidValue) And
CSng(My_line(SortCol, j)) <= CSng(MidValue) Then
If CSng(My_line(SortCol, i)) > CSng(My_line(SortCol, j)) Then
k = 1
For k = 1 To UBound(My_line, 1)
Temp = My_line(k, i)
My_line(k, i) = My_line(k, j)
My_line(k, j) = Temp
Next k
End If
i = i + 1
j = j - 1
Else
If CSng(My_line(SortCol, i)) < CSng(MidValue) Then i = i +
1
If CSng(My_line(SortCol, j)) > CSng(MidValue) Then j = j -
1
End If
Loop
My_test My_line, Low, j, SortCol
My_test My_line, i, Hi, SortCol
End Sub

Hi

Without spending too much time on it, it seems to be a recursive routine for sorting an element of a two dimensional array (some kind of bubble sort).

To run the procedure it is called something like this

Code:

Sub Test()
    Dim ML(1 To 6, 2 To 7)
    
    My_test ML, LBound(ML, 2), UBound(ML, 2), 3
    
End Sub

This of course does not have any data to sort. I assume the array would be populated with data from a range in a worksheet (and then rewritten out again after sorting?).

If you need anything more specific, then will need to investigate further.

HTH

MTB

Internet Services

Web Development

Digital Marketing

Consumer Tech