Read directly a returned array in the calling routine

[Jean-Pierre Bidon]

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

 

[Charlie]

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

 

[keepITcool]

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 :

 

[Charlie]

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

 

[Jean-Pierre Bidon]

Quite simple indeed. Thank you.

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 :