Hi,
I posted a question 2 weeks ago to know if it was (is) possible to retrun an
array from a user defined function.
I received a satisfying answer, ... however it used a worksheet to get back
the array in a selected range.
In looking again at this solution, it call to mind another question: is the
use of a worksheet unavoidable?
Or put differently, is it possible to get the returned array in a new array
declared in a calling routine, or to read the cells of the returned array
directly in this calling routine.
Many thanks in advance.
Jean-Pierre



--
Jean-Pierre Bidon
Interstat
91 rue de Rennes
75006 Paris
Tél: 01 45 49 19 17

You can return an array as an argument OR the function can BE an array. Here
is an example of a simple sort function I use:

Dim txt() As String
ReDim txt(3)

txt(1) = "B"
txt(2) = "C"
txt(3) = "A"

txt = Sorted(txt)

Debug.Print txt(1)
Debug.Print txt(2)
Debug.Print txt(3)

Public Function Sorted(txt() As String) As String()
'
' fast "Heap" sort alogrithm from Knuth - The Art of Computer Programming
'
Dim i As Long
Dim j As Long
Dim nent As Long
Dim ist As Long
Dim lst As Long
Dim Tmp As String
Dim buf() As String
'
' need at least two entries in the array to do a sort
'
nent = UBnd(txt)
If nent = 1 Then Sorted = txt
If nent < 2 Then Exit Function
'
' set sort pointers to the midpoint and endpoint of the array (NOTE - use
the
' integer division operator!)
'
ist = nent \ 2 + 1
lst = nent
buf = txt
'
' do an ascending sort
'
110:
If ist 1 Then
ist = ist - 1
Tmp = buf(ist)
Else
Tmp = buf(lst)
buf(lst) = buf(1)
lst = lst - 1
If lst = 1 Then
buf(lst) = Tmp
Sorted = buf
Exit Function
End If
End If
'
j = ist
'
120:
i = j
j = j * 2
'
If j = lst Then
If Tmp = buf(j) Then
buf(i) = Tmp
GoTo 110
End If
buf(i) = buf(j)
GoTo 120
End If
'
If j lst Then
buf(i) = Tmp
GoTo 110
End If
'
If buf(j) < buf(j + 1) Then j = j + 1
If Tmp = buf(j) Then
buf(i) = Tmp
GoTo 110
End If
'
buf(i) = buf(j)
GoTo 120
'
End Function

-------------------------------------
P.S. The "GoTo's" may not look pretty but they are faster than other
versions of the Heap Sort I've found.



"Jean-Pierre Bidon" wrote:

Hi,
I posted a question 2 weeks ago to know if it was (is) possible to retrun an
array from a user defined function.
I received a satisfying answer, ... however it used a worksheet to get back
the array in a selected range.
In looking again at this solution, it call to mind another question: is the
use of a worksheet unavoidable?
Or put differently, is it possible to get the returned array in a new array
declared in a calling routine, or to read the cells of the returned array
directly in this calling routine.
Many thanks in advance.
Jean-Pierre



--
Jean-Pierre Bidon
Interstat
91 rue de Rennes
75006 Paris
Tél: 01 45 49 19 17

short and simple...

Function Array1()
Array1 = Array(1, 2, 3)
End Function

Function Array2()
Array2 = Array1
End Function

Function Array3() As String()
Dim v, r$(), i%
v = Array2
ReDim r(LBound(v) To UBound(v))
For i = LBound(v) To UBound(v)
r(i) = v(i)
Next
Array3 = r
End Function

since functions 1 and 2 have (implicit) variant datatype
you'll have to rework array3 to achieve a string array.

all 3 functions can be called by vba or from the worksheet.
this what you mean?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jean-Pierre Bidon wrote :

Hi,
I posted a question 2 weeks ago to know if it was (is) possible to
retrun an array from a user defined function. I received a
satisfying answer, ... however it used a worksheet to get back the
array in a selected range. In looking again at this solution, it
call to mind another question: is the use of a worksheet unavoidable?
Or put differently, is it possible to get the returned array in a new
array declared in a calling routine, or to read the cells of the
returned array directly in this calling routine. Many thanks in
advance. Jean-Pierre

Change "UBnd(txt)" to "UBound(txt)"

Quite simple indeed. Thank you.

"keepITcool" a écrit dans le message de news:
...

short and simple...

Function Array1()
Array1 = Array(1, 2, 3)
End Function

Function Array2()
Array2 = Array1
End Function

Function Array3() As String()
Dim v, r$(), i%
v = Array2
ReDim r(LBound(v) To UBound(v))
For i = LBound(v) To UBound(v)
r(i) = v(i)
Next
Array3 = r
End Function

since functions 1 and 2 have (implicit) variant datatype
you'll have to rework array3 to achieve a string array.

all 3 functions can be called by vba or from the worksheet.
this what you mean?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jean-Pierre Bidon wrote :

Hi,
I posted a question 2 weeks ago to know if it was (is) possible to
retrun an array from a user defined function. I received a
satisfying answer, ... however it used a worksheet to get back the
array in a selected range. In looking again at this solution, it
call to mind another question: is the use of a worksheet unavoidable?
Or put differently, is it possible to get the returned array in a new
array declared in a calling routine, or to read the cells of the
returned array directly in this calling routine. Many thanks in
advance. Jean-Pierre