Can not get bubble sort to run in excel's developer mode-CodePudding

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.

CodePudding user response：

Bubble Sort a Single-Column Range

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

CodePudding user response：

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