Transformation to a Function or Pseudo Function Sub routine
The transformation to a Function we can do easily in two simple steps.
Step 1 The routine is changed so that it takes the array to be sorted into it
The calling routine and main routine here http://www.excelfox.com/forum/showth...0987#post10987 are almost the same as the previous. The only difference is that the array to be sorted, is now outside the main routine and is taken into the main routine at the main routine signature line
Sub SimpleArraySort4(ByRef arrTS() As Variant, Optional ByVal GlLl As Long)
( A few unused 'commented out lines have also been removed )
Because we are using ByRef , the previous testieing Calling routine can also use the original supplied array, arrTS() , after the main procedure Call , provided that the array taken in at the signature line is that sorted. If this is the case, then , in effect , the array taken in and the array in the routine are the same array, and changes done to the array in the function will be reflected in the array outside.
We discussed this previously. Just to recap:
Pseudo code ByRef ' ( Usually default option )
varMyArray = x
_ Call ReferToIt(varMyArray)
Sub ReferToIt(ByRef arr)
_ arr=y ' This is similar to saying varMyArray = y
End
varMyArray is now = y ' because effectively varMyArray was in arr
Often in learn material the basic difference between a simple Function procedure and a Sub routine procedure is said to be that a Function returns a value. In fact , as we see from above, the use of ByRef actually allows us effectively to return a value from a simple Sub procedure.
In the version Sub TestieSimpleArraySort4b() and Sub SimpleArraySort4b(__) here , http://www.excelfox.com/forum/showth...0988#post10988 we are using the effect of ByRef to give us a pseudo Function : To achieve this pseudo Function status, all the array references within the routine are changed to that variable taken into the routine at the signature line.
( This variable name can be anything, I will use arsRef() from now on for all references in the main routine). Doing this allows us to remove all the demo range code lines from the procedure: They can be placed outside and then we can use the array which we declare initially outside the routine, ( arrTS() ) , both before and after the procedure Call: Before it is used to feed the data to be sorted, and After the Call it had been filled with the sorted results , so can be used to paste out the sorted results alongside the original range to compare for demo purposes. To all intents and purposes, arrTS() can be considered as arsRef() and visa versa. This characteristic has been achieved by the use ByRef: Any use of arsRef() is simultaneously Referring to
Step 2 Function instead of Sub ???
I am doing this for completeness. But because I am doing things ByRef I don't need to do this. I may not use it finally
Often in learn material the basic difference between a simple Function procedure and a Sub routine procedure is said to be that a Function returns a value.
This can be explained as follows.
The signature line differs from a normal Sub routine in that it has the form similar to a Dim declaration code line. Consider such a simple Dim line:
_ Dim __ MyThing __ As Variant
A signature line for a Function could look like
Function MyThing(Arg) _ As Variant
In both cases you effectively have a variable of the specified type. The way you fill the variable is slightly different. In the simple case you fill it with something
__ MyThing = "42"
For the function you need to Call it to fill it, and unless the Argument is optional, then you must give it
_ MyThing_ "42"
or
_ Call MyThing("42")
Because the function usually ( you can decide, it does not have to !!!!! ) returns a value in a variable of the type you specify , the most typical use would look like
MyOtherThing = MyThing("42")
The returned value is then put in the variable MyOtherThing
At the end of the day, the main reason for using a function is given as that of returning a value of a specific type.
( By the way, once you have such coding written in a File , then in addition to using it in VBA,,then you could sometimes use it in a cell: Writing = MyThing("42") would often work to return you a value in the cell , resulting from what your function MyThing did with "42" )
If we declared our function as type Variant at the signature line, then that would allow us to return our sorted array, since a Variant variable can hold an array.
The reason why I may not finally use this is that by virtue of using ByRef, my Sub routine will also effectively return that array.
Within the Function itself, the Function MyThing, is like an unfilled variable , in this case a Variant variable. As it is unfilled it will not return anything. !!!!! So within the function, typically towards the end once we have finished doing all we wanted to do, we would have a code line like
Function MyThing(Byref ArrToSort() , ByVal aArg, Optional ByVal GlLl As Long) As Variant
' arrOut() = ArrToSort()
' Do stuff like sort the array
_ MyThing = arrOut()
End Function
The function is then "filled" with what it would return when called
Transformation of pseudo Function Sub SimpleArraySort4b(ByRef arsRef() As Variant, Optional ByVal GlLl As Long) to Function SimpleArraySort5(ByRef arsRef() As Variant, Optional ByVal GlLl As Long) As Variant
Let us note once more that this final step is not necessary: The last procedure fulfils all are current requirements of taking an array and sorting that array, and effectively giving it back to us. It does this by virtue of the array being taken ByRef
The true Function way is more conventionally used so I am doing it just for convenience
The convention way to use the Function would be to arrange that our sorted array is returned by the function. As our current routine stands we only need to add one code line to do this. This code line, placed towards the end of the Function would be
__SimpleArraySort5 = arsRef()
To explain:
The signature line .._
Function SimpleArraySort5(_____) As Variant
_.. Has a very similar effect to this line:
_ Dim _ SimpleArraySort5 _ As Variant
So SimpleArraySort5 could be thought of , to a first approximation , as a pseudo variable
The only difference is that when VBA ( or Excel following a = , like in = SimpleArraySort5 , in a cell ) "sees" SimpleArraySort5 , it will go off and do whatever is inside the function. At the End of the function, the value inside the pseudo variable will be empty still. If we do nothing else then the Function can be Called in exactly the same way that a normal sub routine can
_ Call SimpleArraySort5(___)
However, filling the pseudo variable , for example via code line SimpleArraySort5 = arsRef() , allows us the extra possibility of a code lines like
_____ = = SimpleArraySort5(____)
_ MySortedArray= SimpleArraySort5(MyUnsortedArray)
The type chosen in the declaring signature line must be compatible with what you are doing. In our case, we want to return an array. The only variable type that can hold/ pass an array is a Variant. Hence we chose Variant in this case
( because VBA works generally backwards when reading a code line, we could, in our case , use the same variable such
_ MyUnsortedSortedArray= SimpleArraySort5( MyUnsortedSortedArray )
_ arrTS() = SimpleArraySort5( arrTS() ) )
In the routines
Sub TestieSimpleArraySort5() and Function SimpleArraySort5(______) As Variant
http://www.excelfox.com/forum/showth...0989#post10989 , the main difference over the previous routines is the extra As Variant at the signature line, and finally a code line just before End Function of SimpleArraySort5 = arsRef()
In the testieing routine, we use codes line of this form in the conventional way in which a function is typically used.
_ arrTS() = SimpleArraySort5(arrTS(), _ 0 _ )
But we note that by virtue of using ByRef a simple call would surfice
_Call SimpleArraySort5(arrTS(), _ 0 _ )
Note: we have added an extra testing code section '2b)
In this extra section we fill a new array, arrDesc() , with the sorted array in Descending order. We use for demo purposes a typical function using code line
_ arrDesc() = SimpleArraySort5(arrTS(), 2136)
Correspondingly we have a demo output giving code line
_ RngToSort.Offset(0, RngToSort.Columns.Count * 3).Value = arrDesc()
We note further, that this is somewhat redundant. This is because the code part SimpleArraySort5(arrTS(), 2136) has the effect of re filling arrTS() with the newly sorted array by virtue of the use of ByRef in the signature line of the Function
We could therefore simply use a code line like _..
_Call SimpleArraySort5(arrTS(), 357)
_.. followed by an demo output giving line of
_ RngToSort.Offset(0, RngToSort.Columns.Count * 3).Value = arrTS()
We mow have a simple Array Bubble sort function
The next posts will develop ideas to allow extending the function to allow for sorting further using values from other columns for rows where duplicate values were in the column used initially
I will likely start from the "pseudo" Function
Sub SimpleArraySort4b(ByRef arsRef() ______)
Reply With Quote
Bookmarks