ब्लॉग कोशिश कर रहा है بلاگز کی ک*Trying Blogs

[DocAElstein]

Notes for
http://www.snb-vba.eu/VBA_Arrays_en.html
http://www.excelfox.com/forum/showth...0174#post10174 https://www.excelforum.com/the-water...ml#post4613906

Sub snbsVarTypeTypeName() ' http://www.snb-vba.eu/VBA_Arrays_en.html       http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10174#post10174        https://www.excelforum.com/the-water-cooler/1174400-would-like-to-know-about-the-forum-experts-gurus-4.html#post4613906
a_sn = Array(1, 2, 3, 4)
a_sp = Split("1,2,3,45", ",")
a_sq = Range("A1:F5") ' defaluts to Range("A1:F5").Value ' .Value Property returns a Field ( Arrray ) of Variant Member Elements
Dim a_st As Shape: x = VarType(a_st) ' 9 ( Object )
Rem 2 VarType Constants    https://msdn.microsoft.com/en-us/library/office/gg251422.aspx
Dim VrTpe As Long

'2a) Variant Variables: You can determine how the data in a Variant is treated..
Dim Var As Variant: VrTpe = VarType(Var) ' 0  vbEmpty  Uninitialized (default)
Dim VarDef: Let VrTpe = VarType(VarDef) ' 0
 Let Range("A25:A26").Value2 = "Some text"                         '  .Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)                         '  .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
 Let Var = Range("A25:A26").Text                                   '  "Some text" given to Variant    attempts to coerce to a String and will fail if the underlying Variant is not coercable to a String type
 Let Range("A26").Value = "Some other text"                        '  .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
 Let Var = Range("A25:A26").Text ' The data is invalid, a Null is given to Variant
 Let VrTpe = VarType(Var) ' 1  Contains no valid data
Dim arrVar(1 To 1) As Variant
 Let VrTpe = VarType(arrVar())

'2b) Normal Variables
Dim Intr As Integer: VrTpe = VarType(Intr) ' 2
Dim Lng As Long: VrTpe = VarType(Lng) ' 3
Dim Sngle As Single: VrTpe = VarType(Sngle) ' 4
Dim Dble As Double: VrTpe = VarType(Dble) ' 5


Dim Str As String: VrTpe = VarType(Str) ' 8
Dim Obj As Object: VrTpe = VarType(Obj) ' 9

VrTpe = VarType(a_sn) ' 8204 = 8192 (array) + 12 (Variant)
VrTpe = VarType(a_sp) ' 8200 = 8192 (array) + 8 (String)
VrTpe = VarType(a_sq) ' 8204 = 8192 (array) + 12 (Variant)        ' a_sq defaults to a_sq.Value, or rather the = string refs to cells which returns a a Field ( Arrray ) of Variant Member Elements
VrTpe = VarType(a_st) '   9 (object)
Dim a_bt(1 To 1) As Byte
 Let VrTpe = VarType(a_bt()) ' 8209 = 8192 (array) + 17 (Byte)
Dim Rnga_sq As Range: Set Rnga_sq = Range("A1:F5")
 Let VrTpe = VarType(Rnga_sq) ' 8204 = 8192 (array) + 12 (Variant (used only for arrays of Variants)) !!!!  VarType  interprets a Range as an array. ???????
End Sub



Sub TextValueValue2() '    https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/        http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2
Set c = Range("A27")
c.Value = 1198.3
c.NumberFormat = "$#,##0_);($#,##0)"
MsgBox c.Value
MsgBox c.Text
MsgBox c.Value2
End Sub
Sub M_snb()
   sn = Array(String(300, "p"), String(700, "p"), Space(500))
Dim v
   MsgBox Len(sn(0)) & vbLf & Len(sn(1)) & vbLf & Len(sn(2))
End Sub

Using Excel 2007 32 bit

https://msdn.microsoft.com/en-us/lib.../gg251422.aspx
Normal Variable	0	vbEmpty Uninitialized
Array Variable	8192	Array ( Member elements Empty )
Normal Variable	1	If data is invalid, a Null is given to Variant
Array Variable	8193	Null
Normal Variable	2	Integer		6	Currency		11	Boolean
Array Variable	8192+2=8194			8198			8203
Normal Variable	3	Long		7	Date		12	-------
Array Variable	8195			8199			8204	Variant (used only for arrays of variants)
Normal Variable	4	Single		8	String		17	Byte
Array Variable	8196			8200			8146
Normal Variable	5	Double		9	Object		36	UserDefined
Array Variable	8197			8201			8228
Normal Variable	6	Currency
Array Variable	8198
Normal Variable	7	Date
Array Variable	8199
Normal Variable	8	String
Array Variable	8200
Normal Variable	9	Object
Array Variable	8201
Normal Variable	10	Error
Array Variable	8202
Normal Variable	11	Boolean
Array Variable	8203
Normal Variable	12	-------
Array Variable	8204	Variant (used only for arrays of variants)
Normal Variable	17	Byte
Array Variable	8209
Normal Variable	36	UserDefined
Array Variable	8228

Worksheet: EmptiesVariables

[DocAElstein]

This is a comment reply I tried to post to Rick Rothstein and Willy Vanhaelen here
https://excelribbon.tips.net/T010768...ZIP_Codes.html

Possibly some text annoyed the Firewall?
( Also posted here https://www.excelforum.com/developme...ml#post4629001
http://www.excelfox.com/forum/showth...0201#post10201
and in this Text File which is also atttatched https://app.box.com/s/oz2oga0ym6bue2kqf8assy8uq8qdod0e )
_.________________________________________________ _______


Hi Rick,
Thanks very much for popping by. It is really great to hear your “take” on these things. I am continually frustrated that Microsoft, for example, do not give precise information about these things. It appears they are not sure themselves. People like you seem to be the best authorities on this.
To some extent it appears anyone may have an opinion, as no definitive documentation or understanding exists.
I am very glad you seem to think the If({1}, ___ ) is OK: I had been a bit nervous about suggesting it to Willy, as I had not seen it before. But it seemed logical based on all that I had learnt from these sort of things so far.

I have followed your arguments below. You explained very well and I follow your logic and reasoning. Thanks for clarifying a few things.
I came to some general conclusions about what is going on with multi value formula / array analysis and Evaluate. I think I am more or less in agreement with your appraisal. It’s great to hear it. Most people just accept what happens and hack out some of these interesting “coercing” as they often refer to them, as a way to ”get” array analysis to work. Most seem to have no idea how their solutions work.

This is my take, and the reasoning to the
If({1}, ___ )
working as an alternative to the
If(Row(),___ )
( Until recently I only used or had seen the If(Row(), ___ ) or Index( ____ , 0, 0 ) )

I possibly see things slightly the other way around, but probably in end effect the same ...

I was thinking that one of the reasoning’s why CSE is needed is the following:
It seems that single breadth things ( single column or single row arrays or ranges ) , as well as a single range reference works very efficiently due to some mechanism involving an effective help matrix the size of a worksheet which for a single cell reference effectively has that value in the entire cells. For a single breadth range, that matrix is filled in the “other” dimension across or down the effective matrix over the “length” of the range, effectively duplicating the values in the range. This all explains why a single reference like =A1 works very quickly, and also why single breadth things work so quickly, and also explains Implicit intersection which results in a single breadth reference like =A1:A4 or =A1:D1 not giving an error in some cells in a worksheet.
http://www.excelfox.com/forum/showth...on-and-VLookUp

The problem comes with a multi dimensional range reference like =A1:B2. That will error in any cell. What is required is to somehow do some Controlled Shifting of these effective matrices before the doing for each value effectively a single Evaluation on Enter. Maybe that is why they chose the key combination of Ctrl + Shift + Enter.

In a way, the CSE could be thought of “removing” the direct default connection to the worksheet which results in =A1 giving a result in any cell ( except A1 of course ) and = A1:A2 giving a result in some cells.

My argument then would be that Evaluate does not necessarily do array analysis as such, - It simply does not need the CSE to stop it from erroring. Further my argument is that for any =A1:B2, the array analyses does take place , the values are there, but it messes up in a single spreadsheet cell without CSE.

( Evaluate(“=A1:B2”) works to return an Array as by default Excel holds, I believe such references as Range objects, which then have the default Property of .Value applied in many cases to return the single value or values in an Array as appropriate. That can be proved by setting Evaluate(“=A1:B2”) to a Range object variable. It will return the Range object. Equating Evaluate(“=A1:B2”) to a Variant will result in it defaulting to a value or array of values. )

Coming back to Evaluate and multi value ( array ) analysis. I think Evaluate just evaluates.

In the case under consideration, the range is “there” such as A1:A10 is there in Left(A1:A10,5). This will not error in Evaluate, as it does in all but rows 1 to 10 in a spreadsheet. But Left( ) is not programmed to return an Array. Possibly this is what you are saying by “not array aware”.

But, as you suggest if you embed that text string function within another function that is “array aware”, you can “trick” Left into giving all its available values. My argument there is that once embedded into function that is programmed to do array analysis, then in most cases *** , the following happens:
Excel will evaluate as long and as far and in the dimensions of any arrays “available” . In the case of If(Row(), __ ) or If({1}, __ ) this will mean for our example that it “does” for over the range A1:A10.

The If(Row(), __ ) I have seen many times, first in a Forum answer you gave.
The If({1}, __ ) I guessed, following those arguments should do the same and it did. I had not seen that before but I expect someone else stumbled over it as well a long time before me.


A few post down I suggested what happens when you do like_...

Evaluate("IF(Row(A1:A11),LEFT(A1:A10,5))")

The same reasoning suggest this will be “done” over A1:A11. The last evaluation returns an error, as expected.

The fact that the code below does not produce errors in a second “column” in the Array is explained as follows. Excel holds a single breadth range reference like A1:A10 , as I suggested above , in an effective help matrix extending across or down the “other” dimension
So the returned Array duplicates the results in the second “column”
In a way it is what happens in CSE type 2 array entry in which you extend the area you select ( before adding your formula and doing CSE ) to a spreadsheet area beyond the range of values you are expecting back.

Sub ExtendedAcrossSingleBreadthRefPseudoCSEType2Entry()    '
Dim Arr() As Variant
 Let Arr() = Evaluate("=IF(Row(A1:A10)*Column(A:B),LEFT(A1:A10,5))") ' Run code in Debug, F8 mode, stop at End Sub. - Highlight either Arr -  Hit Shift+F9 -  OK   You will see in Watch window that Arr is a (1 to 10, 1 to 2) array. The "row" values are duplicated across the "columns"
End Sub

https://usefulgyaan.wordpress.com/20...e/#comment-739
https://www.mrexcel.com/forum/excel-...ml#post4375354

_...................................

That is all not a perfect answer, as it does not explain all situations*** such as The T(If(1,____)) stuff _...
https://excelxor.com/2014/09/05/inde.../#comment-2514
_... which recently gave me more hours of frustration than any of your one liners, which I am happy to say I think I mostly understand now.
It was healthy frustration, in that I learned much from you.

Alan