1

I'm trying to run what is supposed to be a simple bubblesort algo in excel. Every time I've tried to run bubble sort, I get an error stating, "Compile error sub or function is not defined." I am using the code my professor gave me. Please help.

Sub BubbleSort()

'   Sorts an array using bubble sort algorithm

For i = 1 To 20
    For j = 1 To 20 - i
            If Cells(j, 1) > Cells(j + 1, 1) Then
            Temp = Cells(j, 1)
            Sleep 10
            Cells(j, 1) = Cells(j + 1, 1)
            Cells(j + 1, 1) = Temp
            
            
        Application.Wait (Now + TimeValue("0:00:001"))
            
            
        End If
    
    Next
Next




End Sub

I have tried using a vb sytax checker. But quite frankly, I have no experience with vb and do not know where to start.

Fernando Toranzo
  • 11
  • 2
  • You might be interested in the following link https://www.vbforums.com/showthread.php?473677-VB6-Sorting-algorithms-%28sort-array-sorting-arrays%29 – freeflow Jan 29 '23 at 09:02
  • Your problem seems to be that the `Sub` `Sleep` is not defined. Try removing the line `Sleep 10` from your code as even if you declare `Sleep` it will only wait about 15 milliseconds so it won't make any conceivable difference. Also, you use `TimeValue("0:00:001")` but `Application.Wait` can only wait full seconds, e.g. `TimeValue("00:00:01")`. If you want to wait a fraction of a second every iteration I suggest you take a look at [this post](https://stackoverflow.com/a/74387976/12287457) about how to pause vba code execution. – GWD Jan 29 '23 at 10:52

2 Answers2

0

Bubble Sort a Single-Column Range

enter image description here

A Quick Fix

Option Explicit

' Sorts the range A1:A20 using the bubble sort algorithm
Sub BubbleSort()

    Const FIRST_ROW As Long = 1
    Const LAST_ROW As Long = 20

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve
 
    Dim Temp, i As Long, j As Long

    For i = FIRST_ROW To LAST_ROW - 1
        For j = i + 1 To LAST_ROW
            If ws.Cells(i, "A").Value > ws.Cells(j, "A").Value Then
                Temp = ws.Cells(i, "A").Value
                ws.Cells(i, "A").Value = ws.Cells(j, "A").Value
                ws.Cells(j, "A").Value = Temp
            End If
        Next j
    Next i

    MsgBox "Column range sorted.", vbInformation

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thank you so much. I used this code and it worked. My class is supposed to focus on python 3 but I guess I have to take a vb course on udemy to supplement my university classes. Thank you again – Fernando Toranzo Jan 29 '23 at 17:42
  • You need to be careful because this is just an illustrative code. You would never ever do this in this way. First of all, Excel has a `Sort` method and in Microsoft 365 it got the `SORT` and `SORTBY` functions all of them using the by far superior so-called stable sort which is also many times faster. Secondly, if you would want to bubble-sort the values from a range, you would first write the values to an array and sort the data in the array and then write the sorted data back to the range. Note that there are faster algorithms like the quick-sort or the merge-sort algorithms. – VBasic2008 Jan 29 '23 at 18:31
  • Thank you VBasic2008. I'll keep this in mind and will heed your warning. I am genuinely grateful for your help and the help others provided. – Fernando Toranzo Jan 29 '23 at 19:01
0

Sleep is a windows function and not a VBA Function, but you can still use this function in VBA code by calling the windows Sleep API after declaring it

eg:

#If VBA7 Then
 Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
 Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds as Long) 'For 32 Bit Systems
#End If

Better alternative is to use Application.Wait

Here is the working code to wait for 10 seconds

Sub BubbleSort() '   Sorts an array using bubble sort algorithm

For i = 1 To 20
    For j = 1 To 20 - i
            If Cells(j, 1) > Cells(j + 1, 1) Then
            Temp = Cells(j, 1)
            Application.Wait ("00:00:10")

            Cells(j, 1) = Cells(j + 1, 1)
            Cells(j + 1, 1) = Temp
            
            
        Application.Wait (Now + TimeValue("0:00:001"))
            
            
        End If
    
    Next

Next End Sub

Extreme_Tough
  • 82
  • 8
  • I want to thank you for your help, I really appreciate it. Unfortunately, I just can not get it to work. I tried the corrected code above but it just hangs. – Fernando Toranzo Jan 29 '23 at 17:41
  • I looked at my excel sheet again and noticed there was an extra cell that did not need to be there. I removed it and it worked. Thank you so much! – Fernando Toranzo Jan 29 '23 at 18:59