Load Array by Looping Through Range Object in a Specific Order

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
I have a bubble sort function. The code works inside the sort function sub. I can see that the array UnsortedArray is populated and the bubble sort works using the code:
Debug.Print LBound(UnsortedArray, 1) & "/" & UBound(UnsortedArray, 1) - 1
Immediate Window = "1/9"

However after exiting the sort function sub, the code fails with a Type Mismatch Error (13) - SortedArray is "Empty" on the line of code below:
Debug.Print LBound(SortedArray, 1) & "/" & UBound(SortedArray, 1) - 1

What this tells me is that I am not transferring the sorted array arising from the sort function sub properly in the line of code:
SortedArray = BubbleSort(MonthDayArray, sortcolumn)

Why is this?

*************
Option Base 1
Sub Test()
Dim MonthDayArray As Variant
Dim SortedArray As Variant
Dim sortcolumn As Integer
Dim x as Integer

'Code to fill MonthDayArray

'Send unsorted array to sort function
sortcolumn = 4
SortedArray = BubbleSort(MonthDayArray, sortcolumn)

'Test SortedArray has content
Debug.Print LBound(SortedArray, 1) & "/" & UBound(SortedArray, 1) - 1

End Sub

*****************
Public Function BubbleSort(UnsortedArray As Variant, keyColumn As Integer, Optional SortDescending As Boolean) As Variant
Dim i As Integer, j As Integer
Dim v As Integer
Dim t As Integer

For i = LBound(UnsortedArray, 1) To UBound(UnsortedArray, 1) - 1
For j = i + 1 To UBound(UnsortedArray, 1)
If UnsortedArray(j, keyColumn) > UnsortedArray(i, keyColumn) Then
t = UnsortedArray(i, 1)
v = UnsortedArray(i, keyColumn)
UnsortedArray(i, 1) = UnsortedArray(j, 1)
UnsortedArray(i, keyColumn) = UnsortedArray(j, keyColumn)
UnsortedArray(j, 1) = t
UnsortedArray(j, keyColumn) = v
End If
Next j
Next i
'Test Contents of UnsortedArray
Debug.Print LBound(UnsortedArray, 1) & "/" & UBound(UnsortedArray, 1) - 1

End Function
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sorry this post has the wrong title!

It should say "Bubble Sort Function not working. Can the forum Moderator change this for me please. I cannot seem to change it.

Regards

RK
 
Upvote 0
Your function needs to assign a value to BubbleSort
Try adding the line BubbleSort = UnsortedArray at the end of your function.
 
Upvote 0
You're not assigning a value to BubbleSort. When you define a function, you need to assign the result of the function before exiting, something like:

BubbleSort = UnsortedArray

You should also be aware that since you're changing the values of UnsortedArray in your function, the values of UnsortedArray will also be changed in the calling procedure. If you don't want that, change the Function line to:

Rich (BB code):
Public Function BubbleSort(ByVal UnsortedArray As Variant, ByVal keyColumn As Integer, Optional ByVal SortDescending As Boolean) As Variant

This will just pass the values. ByRef (by reference) is the default.
 
Upvote 0
Thanks all.

Eric, can you elaborate further on your point about values changing. I am not sure I understand it. The purpose of the function is to sort the data within the array. I am not trying to change the values themselves. Why are the values changing in the routine?

Thanks
 
Upvote 0
Consider this simple example:

VBA Code:
Sub test1()

    x = 1
    y = 2
    
    Z = myfunc(x, y)
    
    Debug.Print x, y, Z
End Sub
Function myfunc(ByRef a, ByRef b, Optional ByRef c)

    a = a + 1
    b = b + 1
    
    myfunc = a + b
End Function

You're passing 2 (or 3) variables to the function. But you are passing them by reference. So when you call the function (or possibly a procedure), VBA does not create a new instance of the variable, it just points to the original variable. So a becomes an alias of x, and whatever is done to a in myfunc will be done to x in test1. Step through test1 and watch the variables and see what happens.

However, if you change the ByRef to ByVal, VBA makes another temporary variable with the same value as the passed variable. This is used for the duration of the function, and when the function ends, the temporary variable is gone, and the original variable is unchanged. Make the change and step through test1 again.

There are legitimate reasons to do it both ways depending on circumstances.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top