Passing ParamArray to Function (VBA)

neonangel · Jan 1, 2005

Hi all,

I've re-worded a previous post on this topic as it remains unresolved:
http://www.mrexcel.com/board2/viewtopic.php?t=120830

I'm writing a function that will accept a ParamArray (ie a variable number of arrays) and automatically redirect that array to be sorted by one of two sort functions based on its size.

My BubbleSort function, declared as:

Code:

Sub BubbleSort(Ascending As Boolean, ParamArray Arr() As Variant)

will correctly sort the three arrays RndNos, Names and Order, based on the order of RndNos, and I call it with:

Code:

Call BubbleSort(True, RndNos, Names, Order)

When I use the following wrapper, however:

Code:

Sub BubbleSortWrapper(Ascending As Boolean, ParamArray Arr() As Variant) 
    Dim tmpArr As Variant 
     
    tmpArr = Arr() 
    Call BubbleSort(Ascending, tmpArr) 
    Arr() = tmpArr 
End Sub

with BubbleSort now declared as:

Code:

Sub BubbleSort(Ascending As Boolean, Arr As Variant)

(where tmpArr appears to be necessary to transfer the ParamArray to the next function) I encounter the following problem:

At the end of the BubbleSortWrapper function, Arr is sorted exactly as I want it to be. But upon returning to the calling function, none of the array values have changed!

I find this result interesting as the original call to BubbleSort sorted the arrays correctly, ByRef, so somehow, converting the ParamArray to a Variant and backagain seems to have changed it to ByVal?

This one's been stumping me for some time.. any ideas?

Regards,

Alex

NateO · Jan 1, 2005

Hmmm, you're still not doing what I asked you to try:

Let Arr = BubbleSort(Ascending, tmpArr)

tusharm · Jan 1, 2005

If you don't want to create a sort function, why all the song and dance with tmpArr? Why not just pass Arr to each sort routine? And, yes, you will have to redefine each sort routine to use a paramarray argument.

neonangel · Jan 3, 2005

Thanks for your persistance NateO.. I'm still not understanding exactly what it is you want me to try..

I tried using:

Code:

Sub BubbleSortWrapper(Ascending As Boolean, ParamArray Arr() As Variant)
Dim tmpArr As Variant
    
    tmpArr = Arr()
    Let Arr = BubbleSort(Ascending, tmpArr)
End Sub

with:

Code:

Function BubbleSort(Ascending As Boolean, Arr As Variant) As Variant
' Sorts arrays Arr based on Arr1 in Asc order using the bubble sort algorithm
Dim tmpArr() As Variant
Dim i As Integer, j As Integer, k As Integer

    On Error Resume Next ' Handle optional arrays
    ReDim tmpArr(0 To UBound(Arr)) ' One temporary storage element for each array
    For i = LBound(Arr(0)) To UBound(Arr(0))
        For j = 1 To UBound(Arr(0)) - i
            If IIf(Ascending, Arr(0)(j + 1) < Arr(0)(j), Arr(0)(j + 1) > Arr(0)(j)) Then
                For k = LBound(Arr) To UBound(Arr)
                    tmpArr(k) = Arr(k)(j + 1)
                    Arr(k)(j + 1) = Arr(k)(j)
                    Arr(k)(j) = tmpArr(k)
                Next k
            End If
        Next j
    Next i
    BubbleSort = Arr
End Function

to no avail. Using:

Code:

Function BubbleSortWrapper(Ascending As Boolean, ParamArray Arr() As Variant) As Variant
Dim tmpArr As Variant
    
    tmpArr = Arr()
    BubbleSortWrapper = BubbleSort(Ascending, tmpArr)
End Function

with the calling function using:

Code:

Dim tmp As Variant
tmp = BubbleSortWrapper(True, RndNos, Names, Order)

and the BubbleSort declared as a Sub as before rather than a function DOES provide me with the sorted output correctly, but I would then have to unpack tmp into the original Arrays (RndNos, Names and Order in my test example) which would prove to be a disappointingly long task!

The current best solution is simply to decide whether to use bubble sort or quick sort at design time and call the appropriate sorting procedure based on the expected size of the arrays being sorted, rather than writing a cover function to automatically pass the arrays to the appropriate sort function.

This is the easy way out, and was always a reasonable solution, but I have a lingering disappointment at not being able to resolve the problem of passing ParamArray which I would have thought would have been an easy exercise.

C'est la vie. If there are any further suggestions I'd love to continue dialogue over this.

Regards,

Alex

NateO · Jan 11, 2005

Try the following:

Function arrSort(ByVal myArr As Variant, _
 Optional ByVal Descend As Boolean) As Variant
Dim i As Long, j As Long, LwVl As Long
For i = LBound(myArr) To UBound(myArr)
 LwVl = myArr(i)
 For j = i + 1 To UBound(myArr)
 If Choose(-CInt(Descend) + 1, _
 myArr(j) < LwVl, myArr(j) > LwVl) Then
 LwVl = myArr(j)
 myArr(j) = myArr(i)
 myArr(i) = LwVl
 End If
 Next
Next
arrSort = myArr
End Function

Sub tester()
Dim myArr As Variant, i As Long
Let myArr = [{5,22,4,1}]
Let myArr = arrSort(myArr)
For i = LBound(myArr) To UBound(myArr)
 Debug.Print myArr(i)
Next
End Sub

Notice you reassign the sorted array to the array, right here:

Let myArr = arrSort(myArr)

It doesn't really look like you need or want ParamArray, so why use it?

Passing ParamArray to Function (VBA)

neonangel

Board Regular

Excel Facts

NateO

Legend

tusharm

MrExcel MVP

neonangel

Board Regular

NateO

Legend

Similar threads

Forum statistics

Share this page

Passing ParamArray to Function (VBA)

neonangel

Board Regular

Excel Facts

NateO

Legend

tusharm

MrExcel MVP

neonangel

Board Regular

NateO

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock