1

Topic: Function returning an array - how to declare

Hello!
Started to rewrite under VBA the code from OOBasic and rested - function which will filter an array of lines on a condition is necessary, and to return only the necessary. It would be desirable that it returned their array, instead of in the line.
I can not understand as it to make on VBA - in OOBasic simply you designate function As Array and all works.
Here swears, though the resultant array to be under construction normally.
In the original function declaration on OOBasic:

Function aFilterArrayByCondition (sInputCSV As String, sCondition As String, sRowSeparator As String) As Array

Problem also that on OOBasic there is Pitonjak, and on VBA all that I find it in core Excel which is not necessary to me.
At me the task - to consider the data from the text file and to import to document Word
Function more low:

Function aFilterArrayByCondition (sInputCSV As String, sCondition As String, sRowSeparator As String) As Variant ()
Dim iRows As Integer
Dim iRowIndex As Integer
Dim aInputArray () As String
Dim sFilterArrayByCondition As String
Dim sTransformString As String
Dim sResultString As String
Dim aResultArray () As String
aInputArray () = Split (sInputCSV, sRowSeparator)
iRows = UBound (aInputArray ())
For iRowIndex = 0 To iRows
sTransformString = aInputArray (iRowIndex)
If InStr (1, sTransformString, sCondition) And sResultString = "" Then
sResultString = sTransformString
ElseIf InStr (1, sTransformString, sCondition) And sResultString <> "" Then
sResultString = sResultString AND vbNewLine AND sTransformString
End If
Next iRowIndex
aResultArray () = Split (sResultString, sRowSeparator)
aFilterArrayByCondition = aResultArray ()
End Function

In advance thanks!

2

Re: Function returning an array - how to declare

Andrey A. Markine;
So it is declared already: Function aFilterArrayByCondition (sInputCSV As String, sCondition As String, sRowSeparator As String) As Variant ()
To deduce result (array) on sheet in Excel, it is necessary to enter function as the array formula , see Help.

3

Re: Function returning an array - how to declare

But function returns an one-dimensional array, for Ekselja it is a horizontal array to deduce it in a column - it it is necessary to transpose.
If to do for Ekselja - it is possible to form at once in function a two-dimensional vertical array then additional transformations will be not necessary.
But since here Ekesel generally at anything it is not necessary - I do not understand in what a problem with the Word?

4

Re: Function returning an array - how to declare

Thanks for answers, but generally I fairly wrote at once that Excel is not necessary to me never.
I anywhere do not deduce the data - all code on VBA works for me, only the final report is deduced in Word.
Already solved a problem, a working variant if for whom it is necessary:

Function aFilterArrayByCondition (sInputCSV As String, sCondition As String, sRowSeparator As String) As String ()
Dim iRows As Integer
Dim iRowIndex As Integer
Dim aInputArray () As String
Dim sFilterArrayByCondition As String
Dim sTransformString As String
Dim sResultString As String
Dim aResultArray () As String
aInputArray () = Split (sInputCSV, sRowSeparator)
iRows = UBound (aInputArray ())
For iRowIndex = 0 To iRows
sTransformString = aInputArray (iRowIndex)
If InStr (1, sTransformString, sCondition) And sResultString = "" Then
sResultString = sTransformString
ElseIf InStr (1, sTransformString, sCondition) And sResultString <> "" Then
sResultString = sResultString AND vbNewLine AND sTransformString
End If
Next iRowIndex
aResultArray () = Split (sResultString, sRowSeparator)
aFilterArrayByCondition = aResultArray
End Function

The problem was in () after aResultArray and type mismatch of arrays.
I thought that judging by description Variant () should accept easy any value, and it appears is not present.

5

Re: Function returning an array - how to declare

Andrey A. Markine;
Function in such type certainly returns an array, but only if sRowSeparator=vbNewLine .
Because sResultString gathers through a separator vbNewLine , and shares through sRowSeparator . Otherwise there will be an array, but from one element.
Generally, that you want as I understood, becomes single line:

Function aFilterArrayByCondition (sInputCSV As String, sCondition As String, sRowSeparator As String) As String ()
aFilterArrayByCondition = Filter (Split (sInputCSV, sRowSeparator), sCondition)
End Function

6

Re: Function returning an array - how to declare

Andrey A. Markine wrote:

I thought that judging by description Variant () should accept easy any value, and it appears is not present.

Variant "under the description" accepts any type.
I.e. if you defined returned value As Variant - it quite would "accept" a string array.
And the variable accepting result then too should be declared as Variant.
Determination of returned value as variant () means that you were going to return an array, which each element variant.
The arrays containing elements of different types are incompatible on assignment.
I.e. it is impossible for a type variable variant () to appropriate variable value of type string () and on the contrary - types of elements of these arrays different and variable, able to accept everyone the array appear are incompatible as an accepted array.

Andrey A. Markine wrote:

Already solved a problem, a working variant if for whom it is necessary:

It is not similar to the working. I.e., likely the worker, but for a unique case when the parameter sRowSeparator matters vbNewLine
Here to you "more working":
[spoiler]

Function aFilterArrayByCondition (sInputCSV As String, sCondition As String, sRowSeparator As String) As String ()
Dim aInputArray () As String
aInputArray () = Split (sInputCSV, sRowSeparator)
Dim i_filtered As Long
i_filtered = -1&
Dim iRows As Long
iRows = UBound (aInputArray ())
If iRows> = 0 Then
ReDim aResultArray (0 To iRows) As String
Dim iRowIndex As Long
For iRowIndex = 0 To iRows
If InStr (1, aInputArray (iRowIndex), sCondition) Then
i_filtered = i_filtered + 1
aResultArray (i_filtered) = aInputArray (iRowIndex)
End If
Next iRowIndex
End If
If i_filtered> = 0 Then
ReDim Preserve aResultArray (0 To i_filtered) As String
Else
aResultArray = Split (vbNullString, sRowSeparator)
End If
aFilterArrayByCondition = aResultArray
End Function

[/spoiler]
If for you is not basic - procedure it or function,
I would recommend to you to transform it to appropriate procedure for the purpose of disposal of a line

aFilterArrayByCondition = aResultArray

7

Re: Function returning an array - how to declare

wrote:

... Becomes one :...
[/code]

As, it appears, I already forgot all...