1

Topic: EXCEL function Determination an one-dimensional array of lines

Hello world!
If I want to create function which will return a range, I can specify Type Range after the parameter list.
For example so:

Function F_FindCells (WSheet As Worksheet, FindText As String) As Range
' F-tsija returns a cell containing the given word
' WSeet - for sheet on which we search
' FindText - the required text
' the Cell containing (that we search for that) FindText
Set F_FindCells = WSheet. UsedRange. Find (What: = FindText, LookIn: = xlValues, LookAt: = xlWhole)
End Function

But how to be if f-tsija should return an one-dimensional array of lines.
How correctly to specify this type and whether it is necessary to specify it generally?

2

Re: EXCEL function Determination an one-dimensional array of lines

Tried to make so:

Function F_ColName (WSheet As Worksheet, ByVal iColMax As Integer, iColMin As Integer)
' F-tsija returns an array with alphabetic titles of columns,  under their numbers
' WSheet - sheet for which we settle an invoice. It was possible to use current sheet, but decided strictly it to set
' iColMin - the First column from interesting
' iColMax - the Last column from interesting
Dim i As Integer
i = 0
' the Array which will contain titles of columns,  under their numbers
Dim ColNameToFormulas () As String
' iColMax - iColMin Define the size of a processed array
ReDim ColNameToFormulas (0 To iColMax - iColMin)
For i = 0 To iColMax - iColMin
ColNameToFormulas (i) = CStr (WSheet. Cells (1, iColMin + i).EntireColumn. Address) ' $TQ: $ TQ
ColNameToFormulas (i) = Replace (ColNameToFormulas (i), "$", "") ' TQ:TQ
ColNameToFormulas (i) = Left (ColNameToFormulas (i), InStr (ColNameToFormulas (i), ":") - 1) ' TQ
Next
Set F_ColName = ColNameToFormulas ()
End Function

But I receive such error:

3

Re: EXCEL function Determination an one-dimensional array of lines

If to remove selected Set, function fulfills without an error.
Prompt when it is necessary to specify the operator Set?

4

Re: EXCEL function Determination an one-dimensional array of lines

When there is an object reference assignment.
In this case the variable of type Variant appropriates an array, this admissible assignment

5

Re: EXCEL function Determination an one-dimensional array of lines

Shocker. Pro;
And how to get rid from variant, and to set function of the type necessary to me (an array of lines)

6

Re: EXCEL function Determination an one-dimensional array of lines

Yagrus2 wrote:

And how to get rid from variant

what for? Just Variant also it is used for array reset (if I correctly remember)

7

Re: EXCEL function Determination an one-dimensional array of lines

Shocker. Pro;
For clearness and monotony of the code. Or it basically is not possible?

8

Re: EXCEL function Determination an one-dimensional array of lines

Yagrus2;
...
Probably, certainly. Function has a type of returned value.
Time you do not declare it explicitly, it appears Variant.
Take into consideration what to answer on such not to eat well.
Than itself justified.
And as waiting and were ready to read such answers - solve.

9

Re: EXCEL function Determination an one-dimensional array of lines

Yagrus2,

Function F_FindCells (WSheet As Worksheet, FindText As String) As String ()

10

Re: EXCEL function Determination an one-dimensional array of lines

The Kazan;
Thanks!)

11

Re: EXCEL function Determination an one-dimensional array of lines

booby wrote:

Yagrus2;
...
Probably, certainly. Function has a type of returned value.
Time you do not declare it explicitly, it appears Variant.
Take into consideration what to answer on such not to eat well.
Than itself justified.

, but I could not  why at you such response.
Or you consider, what it is not necessary  with the declaration of returned type of value, at the function description?
Explain please...

12

Re: EXCEL function Determination an one-dimensional array of lines

Yagrus2;
In the given specific case all is normal. But generally there is an underside: even if numerical values get to an array - they will be transformed to the text. And generally any values will be transformed to the text.
If declare function As Long, and inside in an array try to write down the text - receive an error. It should be considered.

13

Re: EXCEL function Determination an one-dimensional array of lines

Thought of that that is appropriated and a little ...
As then to explain such manoeuvre

Function F_ColName (WSheet As Worksheet, iColMax As Integer, iColMin As Integer) As String ()
' F-tsija returns an array with alphabetic titles of columns,  under their numbers
' WSheet - sheet for which we settle an invoice. It was possible to use current sheet, but decided strictly it to set
' iColMin - the First column from interesting
' iColMax - the Last column from interesting
Dim i As Integer
' the Array which will contain the titles of columns calculated under their numbers
Dim ColNameToFormulas () As String
' iColMax - iColMin Define the size of a processed array
ReDim ColNameToFormulas (0 To iColMax - iColMin)
For i = 0 To iColMax - iColMin
ColNameToFormulas (i) = CStr (WSheet. Cells (1, iColMin + i).EntireColumn. Address) ' $TQ: $ TQ
ColNameToFormulas (i) = Replace (ColNameToFormulas (i), "$", "") ' TQ:TQ
ColNameToFormulas (i) = Left (ColNameToFormulas (i), InStr (ColNameToFormulas (i), ":") - 1) ' TQ
Next
F_ColName = ColNameToFormulas ()
End Function
Sub M ()
Dim WBook As Workbook
Set WBook = Workbooks ("the Summary report on  and .xlsb")
Dim WSheet As Worksheet
Set WSheet = WBook. Worksheets ("Data")
Dim iColMax As Integer, iColMin As Integer
iColMax = 100
iColMin = 90
Dim i As Integer
' the Array containing titles of columns
Dim ArrColName () As String
ArrColName () = F_ColName (WSheet, iColMax, iColMin)
End Sub

Before performance of the last line of procedure M (), array ArrColName has no size.
Probably it has only an address. If not brackets before equal
ArrColName () = F_ColName (WSheet, iColMax, iColMin) it would be logical;
That there is an assignment of the link (pointer/address) on an array returned by function F_ColName
Probably I , but assignment without a cycle of one array to another for me am not clear.
If you does not complicate, I ask to make your observations, on my question.

14

Re: EXCEL function Determination an one-dimensional array of lines

Yagrus2 wrote:

... But I could not  why at you such response.
...

There is at me no response.
You asked an easy question on acquaintance to syntax.
For its decision it is enough to open help on syntax of determination of function.
[spoiler] https://msdn.microsoft.com/en-us/vba/la … -statement [/spoiler]
Well, at least, for decency to make it. And then to ask a question if could not understand something.

Yagrus2 wrote:

Before performance of the last line of procedure M (), array ArrColName has no size.
Probably it has only an address.

No. And array [i address [/i] too is not present. It is equal to zero.
There is a variable in which will be the array is stored afterwards it is already known what type;
But still it is not known, what size.
It is called as the dynamic array declaration.
Yes, it is finite at this variable there is an address.
But it not that address of which you thought.

Yagrus2 wrote:

If not brackets before equal
ArrColName () = F_ColName (WSheet, iColMax, iColMin);

Brackets before equal here are optional.

Yagrus2 wrote:

that would be logical;
That there is an assignment of the link (pointer/address) on an array returned by function F_ColName

VBA works not so.
Assignment of value to an array by equality sign usage
Leads to creation of a new copy of an array of the appropriate size and copying
In it of values from an array in the left part of expression.
Thus, in your code two copyings of the same array.
The first time in line

F_ColName = ColNameToFormulas ()

And the second time in line

ArrColName () = F_ColName (WSheet, iColMax, iColMin)

Using procedure instead of function you creation of two copies of an array could avoid.
Example of operation with dynamic arrays:
[spoiler]

Function f1 () As Long ()
ReDim ff (0 To 2) As Long
ff (0) = 1&
ff (1) = 2&
ff (2) = 3&
f1 = ff
End Function
Sub fs (ByRef f () As Long)
ReDim f (0 To 2) As Long
f (0) = 4&
f (1) = 5&
f (2) = 6&
End Sub
Sub test ()
Dim i As Long
Dim m1 () As Long
Dim m2 () As Long
Dim m3 () As Long
' we use brackets at the left
m1 () = f1
' too most without brackets
m2 = m1
' we change value in one element
' if in line m2 = m1 there was an address assignment would change
' appropriate elements in both arrays
m2 (2) = 22&
' procedure allows to do without superfluous  an array
fs m3
' we look that turned out
For i = 0 To 2
Debug. Print "m1 (" AND i AND ") =" AND m1 (i); "m2 (" AND i AND ") =" AND m2 (i); "m3 (" AND i AND ") =" AND m3 (i)
Next
End Sub

[/spoiler]
PS
I easily understand   , but from   I suggest to get rid.

15

Re: EXCEL function Determination an one-dimensional array of lines

booby;

wrote:

from an array in the left part of expression.

It is a misprint
From an array in right expression parts, certainly.

16

Re: EXCEL function Determination an one-dimensional array of lines

booby wrote:

Thus, in your code two copyings of the same array.
The first time in line

F_ColName = ColNameToFormulas ()

And the second time in line

ArrColName () = F_ColName (WSheet, iColMax, iColMin)

Here is not present!
Deliver break point at line F_ColName = ColNameToFormulas () and look in Locals. To this command F_ColName has type String (), i.e. a non-initialized array, and ColNameToFormulas has type String (0 To 10) - an array containing values. Define in Immediate the address of a contained first array cell

? strptr (ColNameToFormulas (0))
121290740

execute a command - F8. Now F_ColName has type String (0 To 10), and ColNameToFormulas has type String ()! That is at assignment the link to structure SAFEARRAY, instead of all row buffer has been rewritten.
Now once again F8 - we are returned in procedure and we define the address of contained first array cell ArrColName

? strptr (ArrColName (0))
121290740

the same Address! Means, the link has been again appropriated, and the buffer remained on a place.
VBA it is finite not the most progressive programming language, but something in it is made correctly:-Q

17

Re: EXCEL function Determination an one-dimensional array of lines

The Kazan;
ok. Reset from functions is optimized.
Copyings are not present.
I was mistaken in the storage.

18

Re: EXCEL function Determination an one-dimensional array of lines

booby wrote:

Kazan;
ok. Reset from functions is optimized.
Copyings are not present.
I was mistaken in the storage.

That too a lie as the general-purpose statement.
(By the way, strptr here it can not be used as the proof at all.
From an invariance of the link to a line in an array cell does not follow that the array was not copied)
Correctly it turns out somehow so - array reset is optimized, if it entirely forms in function
(The returned array has not been transferred as an input parameter).
On to be played:
[spoiler]

Function f1 () ' As Long ()
ReDim ff (0 To 2) As Long
ff (0) = 1&
ff (1) = 2&
ff (2) = 3&
Debug. Print "&ff (0) =" AND VarPtr (ff (0))
f1 = ff
' Debug. Print "&f1 (0) =" AND VarPtr (f1 (0))
End Function
Function f2 (ByRef ff () As Long) ' As Long ()
ReDim ff (0 To 2) As Long
ff (0) = 1&
ff (1) = 2&
ff (2) = 3&
Debug. Print "&ff (0) =" AND VarPtr (ff (0))
f2 = ff
' Debug. Print "&f1 (0) =" AND VarPtr (f1 (0))
End Function
Sub fs (ByRef f () As Long)
ReDim f (0 To 2) As Long
f (0) = 4&
f (1) = 5&
f (2) = 6&
End Sub
Sub test ()
Dim i As Long
Dim m0 () As Long
Dim m1 () As Long
Dim m2 () As Long
Dim m3 () As Long
' we use brackets at the left
m1 () = f2 (m0)
Debug. Print "&m0 (0) =" AND VarPtr (m0 (0))
Debug. Print "&m1 (0) =" AND VarPtr (m1 (0))
' too most without brackets
m2 = m1
Debug. Print "&m2 (0) =" AND VarPtr (m2 (0))
' we change value in one element
' if in line m2 = m1 there was an address assignment would change
' appropriate elements in both arrays
m2 (2) = 22&
' procedure allows to do without superfluous array copying
fs m3
' we look that turned out
For i = 0 To 2
Debug. Print "m0 (" AND i AND ") =" AND m0 (i); "m1 (" AND i AND ") =" AND m1 (i); "m2 (" AND i AND ") =" AND m2 (i); "m3 (" AND i AND ") =" AND m3 (i)
Next
End Sub

[/spoiler]

19

Re: EXCEL function Determination an one-dimensional array of lines

booby wrote:

that too a lie as the general-purpose statement.
(By the way, strptr here it can not be used as the proof at all.
From an invariance of the link to a line in an array cell does not follow that the array was not copied)

Leading a row of experiments found out that for string array slot f-tsii VarPtr and StrPtr give different values of the address.
For normal string variable a situation similar.

Sub Proc ()
ReDim f (0 To 2) As String
f (0) = 4&
f (1) = 5&
f (2) = 6&
Debug. Print "VarPtr &f (0) =" AND VarPtr (f (0)); "StrPtr &f (0) =" AND StrPtr (f (0))
Dim sVar As String
sVar = "Hello world!"
Debug. Print "VarPtr &sVar=" AND VarPtr (sVar); "StrPtr &sVar=" AND StrPtr (sVar)
End Sub

VarPtr (), StrPtr ()
VarPtr &f (0 =109391576, StrPtr &f (0 =108808580
VarPtr &sVar=3075664, StrPtr &sVar=108809260
For me it is not clear strangeness NUMBER 1

booby wrote:

correctly it turns out somehow so - array reset is optimized, if it entirely forms in function
(The returned array has not been transferred as an input parameter).
On to be played:

Working with your scripts noted:
Let the array forms in function, and then is appropriated the function. Type f-tsii and an array coincide
After function fulfilled, the result of its operation is appropriated to an array without the size in exterior procedure.

Function f1 () As Long ()
ReDim ff (0 To 2) As Long
ff (0) = 1&
ff (1) = 2&
ff (2) = 3&
Debug. Print "VarPtr &ff (0) =" AND VarPtr (ff (0))
f1 = ff
End Function
Sub test ()
Dim i As Long
Dim m1 () As Long
' we use brackets at the left
m1 () = f1
Debug. Print "VarPtr &m1 (0) =" AND VarPtr (m1 (0))
' we look that turned out
For i = 0 To 2
Debug. Print "m1 (" AND i AND ") =" AND m1 (i)
Next
End Sub

Before performance of a line

f1 = ff

Expression, Type
f1, Long ()
ff, Long (0 to 2)
And after it Expression, Type
f1, Long (0 to 2)
ff, Long ()
For me the exchange of types is a riddle. The strangeness NUMBER 2 If was possibility to receive the address on everyone
Stage of performance of the code then it would be possible to argue further.
To receive f-tsii f1 address neither before its initialization, nor after it is impossible to me.
As to assignment

m1 () = f1

that here too is not obviously possible for checking up, how there is a transformation
Array (change of its size and filling). Though ff addresses and m1 coincide.

booby wrote:

I Will agree with you that the variant with procedure unambiguously does not do additional operations.
And still my reason concerning your script: as arrays m0, m1, m2 are declared
In exterior procedure, and m0 receives the address inside f-tsii f2 - all of them it turns out normal copying. And as any "the exchange of types" does not happen

20

Re: EXCEL function Determination an one-dimensional array of lines

Yagrus2;
VarPtr (sVar) - the variable address sVar
StrPtr (sVar) - the address of the beginning of the line of a line.
There is a string variable sVar. Its address shows VarPtr
The address on which the line is physically allocated is stored in this variable.
This address shows strptr
2) it not "an exchange of types". So generally speak  to any situation in VBA.
And we will not be while artificially to invent new senses.
Array - a special construction.
It is completely defined by type of a storable element in a cell, an amount of dimensionalities,
Instructions of number of an initial element - the account of elements from zero or from unit and
The maximum value of an index on each dimensionality - 1 (a number of elements in dimensionality).
The static array is completely specified at the moment of the declaration.
The dynamic array can be declared only by instructions of type of a storable element.
Also should in the sense that the type is considered mandatory to the declaration.
In VBA all types not specified explicitly appear Variant.
In Variant it is possible to push value of any type, including both object and an array.
For an array m ()
VarPtr (m) - the variable address m
To this address the structure describing an array - type of its element, a dimensionality amount, etc. and .. above is stored.
In that structure there is also an address of the beginning of area of the data selected under array cells.
But.
VarPtr (m (5)) is an address of an array cell with an index 5.
Also it is possible to tell the following:
If VarPtr (m (5) = VarPtr (n (5)) that, undoubtedly, the fifth elements of both arrays lie in the same area of storage;
Begin with the same address.
And if it is necessary to receive the address of the beginning of area of the storage selected under an array,
That to request it it is necessary so:
VarPtr (m (0))
If lines the address of the beginning of an appropriate line, actually, is stored in each array slot are stored in an array.
And the address of area of storage since which characters of this line lie turns out so
StrPtr (m (5))
Function.
About functions it is possible to know the following:
The function name is simply variable in its code, free for the arbitrary usage

Function F_LongTests (p_val As Long) As Long
Debug. Print p_val
F_LongTests = p_val + 1
F_LongTests = F_LongTests + F_LongTests
F_LongTests = F_LongTests + 5
F_LongTests = 7 * F_LongTests
Debug. Print F_LongTests
End Function

But. Is big but for the functions returning an array.
As reversal to an array happens the help of parentheses;
And the recursive call of function with parameters too happens to usage of parentheses;
Designers of language forbade a direct manipulation with function name as to its internal variable;
In a case when function returns an array.
As regards assignment of arrays through equality sign.
Both for lines and for arrays, if m and n - variables,
m = n as assignment expression always means that there is a copying.
Line or an array.
I forgot that in a case, when n - function, and the object created in it, an array in a considered case;
Had no and cannot have other variables outside of the code of this function, storing the link to it;
That copyings can be avoided safely. That VBA also does.
And to receive copying, it is possible not to transfer at all in function argument of type of an array.
There should be enough, for example, manipulations with globally certain array.
Then its reset is obliged, on common sense, to lead to copying.

wrote:

then it would be possible to argue further

Stop stop.
That it would not mean, I did not promise and I continue not to promise something to argue.

21

Re: EXCEL function Determination an one-dimensional array of lines

booby wrote:

for an array m ()
VarPtr (m) - the variable address m
To this address the structure describing an array is stored....

Here too a certain inaccuracy.
To this address it is stored [i] [/i address of the structure describing an array...
And further under the text.