I think i have answered this one to a certain extent myself in preparing this Question!!..
But it means i have to do a lot of work now to change a big file. So i thought i would
_a) ask if anyone thinks I have got this right
_b) ask if anyone else noticed this, or if there is anything written about it already.
_c) I thought it did no harm to make this a bit public as it might help someone else who gets caught out, or may prevent them getting caught out. Or they could use the Phenomenon to good effect..
I had a very long code with a lot of nested loops. So I tried removing a loop or two by using a .Match here and there. ( The idea being that the .Match retuned me the “position along” a column ( or row ), of where something was, rather than looping through each row ( or column ) in the column ( or row ) until I found it...
It took me ages to track down a weird error. It came down to the type of the ( Holding ) variable Elements in the Array ( Not what types necessarily are in the Elements of the Array ).
It appears I can take in a row or column which includes a very long string from the Spreadsheet cells into an Array and paste that Long string out again. No Problem
I can do that with the Element types in the Array declared as Variant or String.
However if I attempt to use that Array as the second Argument in a VBA .Match , then it becomes a problem if the long String is over 255 characters. ( The .Match errors with incompatible types )
BUT, and this is what really puzzles me, the problem only comes up if then elements are declared as Variant. I could have thought up an explanation if it was the case for the Arrays of both Types, - something like the .Match only “ allocating” 255 characters to somewhere where it puts the things ( strings ) from the Array. ( Just as it is in the case of the first argument that is limited to 255 characters )
But it makes no sense to me that it works in the case of an Array declared as String Types.
This is a real pain as I usually take all my Values in at once with “One Liner Capture” using the .Value Property, which returns a Field ( Array() ) of Variant types.
Hope that is fairly clear. I tried to Demo it a bit clearer in a short stand alone code. The code will work on the first sheet of any File: ( If ylou have time and are interested it is best to go through the code in Debug F8 mode and see how things develop:
Brief description of code.
It pastes three strings across the first row, Cells A1 to C1. The last string length in Cell C1 you can select and experiment with its length as you are asked for its length by an Input box.
Those three strings are put in two Arrays, a Variant Element type and a String Element type.
They are also pasted back out in the next two rows just to confirm that there is no problem with a long String so far.- The integrity remains after storing in either Array, even for a very long string. Taking the case when I choose 256 characters:
|1||CellA1||CellB1||My Long String is.......... 256 Characters!|
|2||CellA1||CellB1||My Long String is.......... 256 Characters!|
|3||CellA1||CellB1||My Long String is.......... 256 Characters!|
Rem 3) ( see later )
An attempt is made to find the “position along” of one of those strings in the Arrays using the .Match. As you would see in running the program if your long string is over 255 characters, then the .Match will error in the case only of the Array of Variant Element types.
( Rem 3) I just put this in for convenience, to show that using various .Index code lines are not exhibiting any strange 255 limit when its first argument Array has very long strings in it. )
So a Weird problem!!
Anyone got any comments or explanations to this. Or any known workarounds.
I know i can loop through my “Captured” Array Elements converting them to String and then pitting them into an Array of String Types. Or i can loop through the range bringing in each spreadsheet Value into an Array of String Element Types. But those both defeat the point of doing away with one loop by using the .Match.
Also I can put error handling in to catch the error, - but then I have lost any possible match to other Elements in the Array
Not a major problem, but interesting... ( I think )
No rush on Replies. I need a break after a week doing my head in trying to track down this weird problem!!
( Note: I am not talking about the first argument in .Match limit of 255 characters, which i am aware of and behaves as expected in the Demo Code lines 435 and 445
Following on from that I suppose one answer could be that in the .Match all string things are limited usually by the 255 limit anyway. Given that i cannot search using a first argument of over 255 characters. Then it is just curious how the .Match behaves when there are Elements in the second argument Array of over 255 characters, and just bad luck it does not react in the way most convenient to me ..But again interesting that it behaves differently for different Declared types inn that Array. Could be advantages in a different situation, so worth knowing about at least. )
Here is the Code which in this case will tell you that Lines 470 and 480 errored ( Those being the lines attempting the .Match using as its second argument the Array of variant types. )
Sub MatchArrayVaryArntString255Wonk() ' http://www.eileenslounge.com/viewtopic. ... 12#p175115" onclick="window.open(this.href);return false;
Application.ScreenUpdating = True
10 Rem 1 ) 'Worksheets Info Test Range. Put and Use String Values in a Spreadsheet for Demo purposes.
20 Dim ws As Worksheet 'Variable for Initial Pointer for VBA to referrence ws. ( Allows intellisense to give suggestions after . Dot for mehtods, properties etc. of Worksheets Object.
30 Set ws = ThisWorkbook.Worksheets.Item(1) 'Assign First Worksheet item ( shown as first Tab from the left ) allowing memory assignment to be completed
40 ws.Range("A1:C8").Clear 'Cllear range used in this Demo
50 Let ws.Cells(1, 1) = "CellA1": Let ws.Cells(1, 2) = "CellB1"
70 Dim myLongSpace As Long: 'Variable holding whole Number of String Character length for experimennts
80 Let myLongSpace = CLng(InputBox("Type in how long the test string should be" & vbCr & "(min 42)", "String length", "256", 0, 0)) 'InputBox with no object qualifier calls the InputBox Function
90 Dim myLonglengthString As String: Let myLonglengthString = Replace((Format(myLongSpace, "000;###")), "0", " ", 1, 1) 'just to adjust so we allways have a 3 character statement forour String length
100 Dim myLongString As String 'Initial Pigeion Hole giving Address used initially for start of this String. No length info Initially. Equivalet to = vbNullString
110 Let myLongString = "My Long String is.........." & Space(myLongSpace - 42) & myLonglengthString & " Characters!": Debug.Print Len(myLongString) 'Some arbritrary String of length of Characters = myLongSpace
120 Let ws.Cells(1, 3).Value = myLongString 'String Value assignd to cell has a character length = myLongSpace
124 Rem 2a)
126 Dim VarStr As Variant ' ' 'Variable Type is constructed to accept all info allowing its use for most other Variables. One exception is a defined String length greater than 255
128 Let VarStr = ws.Cells(1, 3).Value 'Put my long String in VarStr
130 Debug.Print "Len "; Len(VarStr); Tab; VarStr ' Ctrl + G to see in Immediate Window that this always works
140 Rem 2b)
150 Dim arrVaryArntS(1 To 3) As Variant, arrStrings(1 To 3) As String 'Arrays for Holding string Values from the Worksheet Range
160 Dim clms As Long 'Loop Bound Variable Count for columns
170 For clms = 1 To 3 'Loop to put values in Arrays, and paste out again to confirm preservation of content
180 Let arrVaryArntS(clms) = ws.Cells(1, clms).Value: Let arrStrings(clms) = ws.Cells(1, clms).Value 'Pit range values inn Arrays
190 Let ws.Cells(2, clms) = arrStrings(clms): Let ws.Cells(3, clms) = arrVaryArntS(clms) 'Paste back out in consequetive Rows
200 Next clms
220 Rem 3) ' .Index check using simple .Index and also Application.Index with Look Up Rows and Columns Arguments as VBA Arrays .... see referrences on that
230 Dim ValAtInc3r3 As String
240 Let ValAtInc3r3 = Application.Index(ws.Cells, 3, 3) 'Appears to be unaffected by long strings
250 ws.Range("C4").Value = ValAtInc3r3 'Paste for demo at next free row
260 Let ValAtInc3r3 = Application.WorksheetFunction.Index(ws.Cells, 3, 3) 'Also appears to be unaffected by long strings
270 ws.Range("C5").Value = ValAtInc3r3 'Paste for demo at next free row
280 Dim arrSptRow() As Variant 'Variable Pointing to Pigeon Hole Location capable of Holding a complex series of offsets. Will be fixed due to complexity, leading to a predominantly ByReference Calling subsequently.. ....
290 Let arrSptRow() = Application.Index(ws.Cells, Application.Transpose(Array(2, 3)), Array(1, 2, 3)) 'Technique due to complex offset referrencing ony available to Application.Index and not Application.WorksheetFunction.Index due to the above Declaration. Intercepts Made from the Transposed causing a offset by a row, "brought back" stackad at start, "If(Row__" type coerce into a " D Array. Intercepts return a full all set of columns rather than one doe too the transpose messing up the copmlex offset Storing of an Array
300 Let ws.Range("A7").Resize(UBound(arrSptRow(), 1), UBound(arrSptRow(), 2)).Value = arrSptRow() 'Paste for demo at next free row. A VBA "allowed 1 liner" can paste the 2 D Array Element Values to a Spreadsheet Range. The Range object of the Top Left Cell of where we want the values to go has the .Resize Property applied to it return a New Raage Object of the appropriately increased size
310 Let arrSptRow() = Application.Index(ws.Cells, Application.WorksheetFunction.Transpose(Array(2, 3)), Array(1, 2, 3)) 'Check for any WorksheetFunction difference in the Transpose. Result: No Difference: same Array produced as line 290
320 Let ws.Range("A7").Resize(UBound(arrSptRow(), 1), UBound(arrSptRow(), 2)).Value = arrSptRow() 'same Output as line 300
330 Let arrSptRow() = Application.Index(ws.Cells, Application.Transpose(Array(3)), Array(1, 2, 3)) 'Case to get a Single Row.
340 Let ws.Range("A6").Resize(1, UBound(arrSptRow())).Value = arrSptRow() 'Paste for demo at next free row. A VBA "allowed 1 liner" can paste the "Pseudo gesehen by VBA horizontal" 1 D Array Element Values to a Spreadsheet Range. The Range object of the Left Most Cell of where we want the values to go has the .Resize Property applied to it to return a New Range Object of the appropriately increased size
350 Let arrSptRow() = Application.Index(ws.Cells, Array(3), Array(1, 2, 3)) 'Strangely....
360 Let ws.Range("A6").Resize(1, UBound(arrSptRow())).Value = arrSptRow() '...the "disoriantated" or 1D Array in this case appers to work as if transposed...and further....
370 Let arrSptRow() = Application.Index(ws.Cells, 3, Array(1, 2, 3))
380 Let ws.Range("A6").Resize(1, UBound(arrSptRow())).Value = arrSptRow() '.... similar results for a simple indicie??
400 Rem 4)'.Match Experiments
410 Dim Mtchres As Variant 'A Long declaration would be OK, but we often do a variant to allow an Error to be returned.
420 '4a) String Elements
430 Let Mtchres = Application.Match("CellB1", arrStrings(), 0) 'Will return the position "along" of "CellB1" , in the Array arrStrings() , stipulating exact match or will return an Error if no match found ( will not error itself )
435 Let Mtchres = Application.Match(myLongString, arrStrings(), 0) 'Will return the position "along" of myLongString for up to 255 characters or will return an error for greater ( will not error )
440 Let Mtchres = Application.WorksheetFunction.Match("CellB1", arrStrings(), 0) 'As last line BUT will actually error if no match is found, but that should not occur for our Demo
'445 Let Mtchres = Application.WorksheetFunction.Match(myLongString, arrStrings(), 0) 'So for characters greater than 255 this will actually error. I comment this out as it is not so relavent to the problem being looked at in this Demo Code.
450 '4b) Variant Elements
460 On Error GoTo ErrorErl 'We use this error handler to handle the predted errors that could occur in the next few lines
470 Let Mtchres = Application.Match("CellB1", arrVaryArntS(), 0)
480 Let Mtchres = Application.WorksheetFunction.Match("CellB1", arrVaryArntS(), 0)
490 On Error GoTo 0 'At this point we are finished with the Error handler, so it is good practice to switch it off
510 Rem 5)
520 Set ws = Nothing 'Remove any leaked out remnants in Memory of the ws Object.
530 Exit Sub 'Normal Code End
540 Rem 6) Error handler section
550 ErrorErl: 'ErrorErl Handler section Spring Point. ( Can send here using line number also )
560 MsgBox prompt:="You got error """ & Err.Description & """ at Line " & (Erl()) 'Description Property of Err Object Give string descripion of error and Erl() returns Line "before it occured" .. usually.. for the .Match cases here it seems to return the actual line...
570 Resume Next 'This instructs to contune after the error line as if nothing had happened. It clears the Exceptional VBA condition of thinking that an error is being handeled ( which allows an Error handler to be used again ). It does not turn off the current Error handler so it will work again
'Errors http://excelmatters.com/2015/03/17/on-error-wtf/" onclick="window.open(this.href);return false;
'Erl() http://www.excelforum.com/the-water-coo ... ost4293426" onclick="window.open(this.href);return false;
'InputBox http://www.mrexcel.com/forum/excel-ques ... ethod.html" onclick="window.open(this.href);return false;
'InputBox http://stackoverflow.com/questions/1327 ... el-vba-why" onclick="window.open(this.href);return false;
'.Index with Array Arguments. http://excelforum.com/excel-new-users-b ... rrays.html" onclick="window.open(this.href);return false;
'.Index with Array Arguments https://usefulgyaan.wordpress.com/2013/ ... ion-index/" onclick="window.open(this.href);return false;
'Varaible Declaration http://www.mrexcel.com/forum/excel-ques ... ref-4.html" onclick="window.open(this.href);return false;
'Varaible Declaration Post #12 http://www.mrexcel.com/forum/excel-ques ... ror-2.html" onclick="window.open(this.href);return false;