CopyMemory Function

[James_B]

Hi All. I'm not convinced this is quite the right place for me to post. But I put this question in an Excel forum and no-one replied, so hopefully someone here will be able to help me out.

(By way of background, I've had to learn to use VBA as part of my job recently. I've therefore got about 6 months VBA experience and am not quite sure what VB is to be honest. So, if you could put things in idiot language, it would be much appreicated).

Anyway, I recently came across this article and, stupidly, thought I'd try to incorporate it into a sorting algorithm I've written. I can get it to work with strings like this:

Option Explicit
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
            (lpDest As Any, lpSource As Any, ByVal cbCopy As Long)
Sub Trial()
   Dim s() As String
   ReDim s(0 To 2) As String
   s(0) = "Jack": s(1) = "Jill": s(2) = "John"
   Swap s, 0, 2
End Sub
Sub Swap(ByRef s() As String, a As Long, b As Long)
   Dim t As Long
   CopyMemory t, ByVal VarPtr(s(a)), 4
   CopyMemory ByVal VarPtr(s(a)), ByVal VarPtr(s(b)), 4
   CopyMemory ByVal VarPtr(s(b)), t, 4
End Sub

But I'd like to get it to work with variant arrays as well so I can manipulate data I've imported from Excel sheets. I tried it like this (I heard somewhere that you needed to use a 16 rather than a 4 for variants):

Option Explicit
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
            (lpDest As Any, lpSource As Any, ByVal cbCopy As Long)
Sub Trial()
   Dim v() As Variant
   v = Array("Jack", "Jill", "John")
   Swap v, 0, 2
End Sub
Sub Swap(ByRef v() As Variant, a As Long, b As Long)
   Dim t As Long
   CopyMemory t, ByVal VarPtr(v(a)), 16
   CopyMemory ByVal VarPtr(v(a)), ByVal VarPtr(v(b)), 16
   CopyMemory ByVal VarPtr(v(b)), t, 16
End Sub

but to no avail. The Swap routine swaps the variables alright, but when returning to the main routine VBA tells me I've used a bad dll convention.