I’m stuck, please help. I have a small dynamic length array (6:20 x 2 text/numeric) and I want to automatically create a sorted copy so I can plot a monatonic scatter graph. To recalculate automatically, I was thinking Function. I plagiarised QuickSortArray, which works for the sorting as a Sub.
Q1: I can’t manage to call QuickSortArray from a Function even though I can successfully call it from another Sub. The working Sub call is below. I changed Sub QuickSortArray to Function QuickSortArray invoked from the spreadsheet, but could not get it to work. As it does work from a Sub, can I trick CallSub into running when the data changes? That makes the next two questions moot.
Sub CallSub()
temparray = Range("weekdays")
Call QuickSortArray(temparray, , , 2)
Dim MyRange As String
MyRange = "daysout"
Range(MyRange) = temparray
End Sub
Q2: I notice that QuickSortArray is recursive (calls itself): can this work within a function? The VBA I used is the top code here: Sorting a multidimensionnal array in VBA . At the bottom it has these recursive calls
If (lngMin < j) Then Call QuickSortArray(SortArray, lngMin, j, lngColumn)
If (i < lngMax) Then Call QuickSortArray(SortArray, i, lngMax, lngColumn)
Q3: The code below is the only working way I have found to pass an array and retrieve it. Weekdays is a named range on the spreadsheet. However, Fn SortDays does not automatically recalculate, either as a single cell function, or (as I want to) when using Array arithmetic to retrieve the whole array? The function Test below it recalculates automatically and returns the results into an array of {=test($L$20)} in the spreadsheet ($L$20 = x). So it is not recalculation settings.
Function SortDays() As Variant
Dim InputArray As Variant
InputArray = Range("weekdays")
SortDays = InputArray
End Function
Function Test(x As Integer) As Variant
Dim V() As Variant
Dim N As Long
Dim R As Long
Dim C As Long
ReDim V(1 To 3, 1 To 4)
For R = 1 To 3
For C = 1 To 4
N = N * x + 1
V(R, C) = N
Next C
Next R
Test = V
End Function
