How is CopyMemory implemented

[get2noesks]

Code is not recognising the highlighted line where CopyMemory is called. How do we implement it?

Public Sub Merge(list() As String, ByVal beginning As Integer, ByVal middle As Integer, ByVal ending As Integer)
Dim temp_array() As String
Dim temp As Integer
Dim counterA As Integer
Dim counterB As Integer
Dim counterMain As Integer


    ' Copy the array into a temporary array.
    ReDim temp_array(beginning To ending)

    CopyMemory temp_array(beginning), list(beginning), (ending - beginning + 1) * Len(list(beginning))

    ' counterA and counterB mark the next item to save
    ' in the first and second halves of the list.
    counterA = beginning
    counterB = middle + 1

    ' counterMain is the index where we will put the
    ' next item in the merged list.
    counterMain = beginning
    Do While (counterA <= middle) And (counterB <= ending)
        ' Find the smaller of the two items at the front
        ' of the two sublists.
        If (temp_array(counterA) <= temp_array(counterB)) _
            Then
            ' The smaller value is in the first half.
            list(counterMain) = temp_array(counterA)
            counterA = counterA + 1
        Else
            ' The smaller value is in the second half.
            list(counterMain) = temp_array(counterB)
            counterB = counterB + 1
        End If
        counterMain = counterMain + 1
    Loop

    ' Copy any remaining items from the first half.
    If counterA <= middle Then
        CopyMemory list(counterMain), temp_array(counterA), (middle - counterA + 1) * Len(list(beginning))
    End If

    ' Copy any remaining items from the second half.
    If counterB <= ending Then
        CopyMemory list(counterMain), temp_array(counterB), (ending - counterB + 1) * Len(list(beginning))
    End If
End Sub

 

[RickXL]

Hi,

Try this:

Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias _
    "RtlMoveMemory" (destination As Any, source As Any, _
    ByVal length As Long)
    
Public Sub Merge(list() As String, ByVal beginning As Integer, ByVal middle As Integer, ByVal ending As Integer)
    Dim temp_array() As String
    Dim temp As Integer
    Dim counterA As Integer
    Dim counterB As Integer
    Dim counterMain As Integer


    ' Copy the array into a temporary array.
    ReDim temp_array(beginning To ending)

    CopyMemory temp_array(beginning), list(beginning), (ending - beginning + 1) * Len(list(beginning))

    ' counterA and counterB mark the next item to save
    ' in the first and second halves of the list.
    counterA = beginning
    counterB = middle + 1

    ' counterMain is the index where we will put the
    ' next item in the merged list.
    counterMain = beginning
    Do While (counterA <= middle) And (counterB <= ending)
        ' Find the smaller of the two items at the front
        ' of the two sublists.
        If (temp_array(counterA) <= temp_array(counterB)) _
            Then
            ' The smaller value is in the first half.
            list(counterMain) = temp_array(counterA)
            counterA = counterA + 1
        Else
            ' The smaller value is in the second half.
            list(counterMain) = temp_array(counterB)
            counterB = counterB + 1
        End If
        counterMain = counterMain + 1
    Loop

    ' Copy any remaining items from the first half.
    If counterA <= middle Then
        CopyMemory list(counterMain), temp_array(counterA), (middle - counterA + 1) * Len(list(beginning))
    End If

    ' Copy any remaining items from the second half.
    If counterB <= ending Then
        CopyMemory list(counterMain), temp_array(counterB), (ending - counterB + 1) * Len(list(beginning))
    End If
End Sub

CopyMemory is a Windows function so needs to be declared as such so that Excel can use it.

 

[get2noesks]

Hi Rick,

I am getting an error while using the line

Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias _
    "RtlMoveMemory" (destination As Any, source As Any, _
    ByVal length As Long)

Could you please specify any other requisite because there are other sub routines in the page and the error says 'Only comments may appear after End Sub, End Function.....'

Regards
Saurabh

 

[RickXL]

That declaration needs to go before the very first Sub or Function in that Module.

By the way, there are other ways to sort an array, if that is what you are trying to do.

 

[get2noesks]

Hi Rick,

I am sorting multiple columns using merge sort. These columns will be identified at run time. While merging I am using this code to merge the array.

When I declared on top of the module, it is showing an error 'Constants, fixed-length string, arrays, user defined types and Declare statements not allowed as Public members of object modules.'

Please advise.

Regards
Saurabh

 

[GTO]

Hi Rick,

I am sorting multiple columns using merge sort. These columns will be identified at run time. While merging I am using this code to merge the array.

When I declared on top of the module, it is showing an error 'Constants, fixed-length string, arrays, user defined types and Declare statements not allowed as Public members of object modules.'

Please advise.

Regards
Saurabh

The error is telling you that the declared function must be declared as Private in an object module. I would guess that you plunked the code into a worksheet's module?

 

[get2noesks]

That's right Rick. I have it in 'Workbook module'

 

[RickXL]

As GTO says, you need to put the code into a standard Module, not a WorkBook Module and not a WorkSheet Module.