Help With Passing Parameters to Subs (Compile Error: Argument not optional)

[NeuroticIX]

Hello All,

I am new to Excel VBA and have been trying to code a merge sort algorithm. I actually have two questions. The following code has been adapted from online examples, but whenever I try to run the code I get a compile error. I believe that the way in which I either try to Call the other Subs or how I define the arguments at the start of each Sub is giving me the compile error: Argument not optional. I highlighted those lines of code in red. If anyone could give me any adivce on why I am getting this error.

The second question I have is whether Excel VBA will recognize the CopyMemory lines of code. I have tried searching for the CopyMemory function in the help files and found nothing which makes me think it might not exist anymore (I am using Excel 2010). My code is as follows:

Sub Uhh()
'Declare Variable Type
Dim CountRow As Long, CountCol As Long
Dim i As Long, j As Long
Dim TempArray() As Variant
Dim TheRange As Variant

'Count Rows and Columns of Data
CountRow = ActiveSheet.UsedRange.Rows.Count
CountCol = ActiveSheet.UsedRange.Columns.Count

'Prevent Screen Updating
Application.ScreenUpdating = False

'Redimension Temporary Array
ReDim TempArray(1 To CountRow)

'Fill Temporary Array
For i = 1 To CountRow
TempArray(i) = ActiveSheet.Cells(i, 1)
Next i

Call MergeSort(TempArray, 1, CountRow)
End Sub

Sub MergeSort(List() As Variant, First_Index As Long, Last_Index As Long)
Dim Middle As Long

If (Last_Index > First_Index) Then
'Recursuvely Sort
Middle = Application.WorksheetFunction.RoundUp((First_Index + Last_Index) / 2)
Call MergeSort(List, First_Index, Middle)
Call MergeSort(List, Middle + 1, Last_Index)

'Merge Results
Call Merge(List, First_Index, Middle, Last_Index)
End If
End Sub

Sub Merge(List() As Variant, ByVal Beginning As Long, ByVal Middle As Long, ByVal Ending As Long)
Dim Temp_Array() As Variant
Dim Temp As Integer
Dim i As Long
Dim CounterA As Long
Dim CounterB As Long
Dim CounterMain As Long

'Copy Array into Temp Array
ReDim Temp_Array(Beginning To Ending)
CopyMemory Temp_Array(Beginning), List(Beginning), (Ending - Beginning + 1) * Len(List(Beginning))
' For i = Beginning To Ending
' Temp_Array(i) = List(i)
' Next i

'Set Counters
CounterA = Beginning
CounterB = Middle + 1
CounterMain = Beginning

'Merge Sort
Do While (CounterA <= Middle) And (CounterB <= Ending)
'Find Smaller of Two Items At Front of Two Sublists
If (Temp_Array(CounterA) <= Temp_Array(CounterB)) Then
'Smaller Value is in First Half
List(CounterMain) = Temp_Array(CounterA)
CounterA = CounterA + 1
Else
'Smaller Value is in Second Half
List(CounterMain) = Temp_Array(CounterB)
CounterB = CounterB + 1
End If
CounterMain = CounterMain + 1
Loop

'Copy Any Remaining Items From First Half
If CounterA <= Middle Then
CopyMemory List(CounterMain), Temp_Array(CounterB), (Ending - CounterB + 1) * Len(List(Beginning))
End If

'Copy Any Remaining Items From Second Half
If CounterB <= Ending Then
CopyMemory List(CounterMain), Temp_Array(CounterB), (Ending - CounterB + 1) * Len(List(Beginnning))
End If
End Sub

Any help would be much appreciated. Thanks.

Kevin

 

[Worf]

Welcome to the Board, Kevin!

• The RoundUp function was missing an argument, that caused the compile error
• The CopyMemory routine requires the declaration shown below, worked for me with Excel 07
• I got run time error 28 - Out of stack space - when executing the code, hope you have better luck...

Option Explicit


Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (Destination As Any, Source As Any, _
    ByVal length As Long)


Sub MergeSort(List() As Variant, First_Index As Long, Last_Index As Long)
Dim Middle&


If (Last_Index > First_Index) Then
    'Recursively Sort
    Middle = Application.WorksheetFunction.RoundUp((First_Index + Last_Index) / 2, 0)
    MergeSort List, First_Index, Middle
    Call MergeSort(List, Middle + 1, Last_Index)
    'Merge Results
    Call Merge(List, First_Index, Middle, Last_Index)
End If
End Sub

 

[shg]

Do you particularly want to use MergeSort? I have (everyone has) a QuickSort routine.