VBA .Match works differently for second argument Array of El

User avatar
Doc.AElstein
BronzeLounger
Posts: 1402
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

VBA .Match works differently for second argument Array of El

Post by Doc.AElstein »

VBA .Match works differently for second argument Array of Elements Variant Types or String Types for Strings of over 255 Characters. Error inconsistencies

Hi

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.
and
_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..

So:-
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.

Rem 1)
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.

Rem 2)
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:
ABC
1CellA1CellB1My Long String is.......... 256 Characters!
2CellA1CellB1My Long String is.......... 256 Characters!
3CellA1CellB1My Long String is.......... 256 Characters!
( In the spreadsheet you will see that all 256 characters are preserved and pasted out. )

Rem 3) ( see later )

Rem 4)

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. )

Alan

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

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"
60
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
122
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
210
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??
390
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
500
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
580
End Sub




Rem Ref
'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;
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 03 Mar 2016, 13:17, edited 1 time in total.
\ -_- / :heavy: :jollyroger: There's a row going on down near my bottom

User avatar
HansV
Administrator
Posts: 71978
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA .Match works differently for second argument Array o

Post by HansV »

Thanks.

Since your arrays are basically arrays of cell values, another option would be to use the Find method of the Range object to find where a value occurs.

Code: Select all

    Dim rngFound As Range
    Dim lngCol As Long
    Set rngFound = Range("A1:C1").Find(What:="CellB1", LookAt:=xlWhole)
    If rngFound Is Nothing Then
        MsgBox "Value not found!"
    Else
        lngCol = rngFound.Column
    End If
Regards,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1402
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Match works differently for second argument Array o

Post by Doc.AElstein »

Hi Hans,

Thanks for that . I can see that would definitely be an alternative to my given examples / Demo Code

I had greatly simplified my real life situation to attempt some clarity, and to concentrate on the peculiar different results from the different Array Element Types I had been seeing.

In the Praxis I have multi Row ( currently 20 row ) headings across the sheet ( allowing for different spellings, Languages of Food Nutritions ) These extend over columns of currently around 4000, but could likely increase. ( Occasionally it is convenient to have a long String of text in a spare heading row. This lead to the errors on the .Match I described in the first Post )
HIJKLM
1KcalFettEiweiß
2GCALZFZE
3EnergieKcalTotallipidfatFATgcha cha
4KilokalorienFettProtein
5Brennwert
6EnergyENERC_KCALkcalProtein
7(kcal)ProteinPROCNTg
8KilocaloriesFatProtein
9Brennwert
10Energie:Eiweiß
11kcalFat
12Kilocalorien
13Kilocalories
14Kilokalorien
15(kcal)Could occaisionally be a long String here…
16
17
18
19
203000200
So ( for the reduced looping .Match version of my code ) , I take each column in turn, and For each column I make a 20 “row” , 1 “column” 2 D array ( Using the .Index Function in a similar way to in my Rem 3 ) ) code section – hence again I have Variant types ). This is then fed into the .Match second argument. For speed I capture the entire heading range / approx 20 x 2000 ) into an Array and use that rather than using “Spreadsheet interaction Techniques” of the type you suggest.

(- For comparison, Your code , would be then ( “pseudo code” )

For clms 1 to 4000
Set rngFound = Range( Cells ( 1, clms ), Cells (20, clms ) ).Find(What:=___

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

But I Have become a bit narrow minded on these things
So I should not shut out exclusively the Range type options and keep an open mind about these Range interaction options , ( which I actually have less experience with than Array work as I had a 25 year absence from computing and missed the whole Visual Basic OOP stuff.).
Actually, Now that I think about it your method would allow me to look along the whole 4000 column Row 20 times.. I could not do that with my .Index to give me a 1 D 1 x 4000 “pseudo” Horizontal Array due to the WorksheetFunctions 255 “column” Limit

_ So thanks for that input. I may have another thing to try now.. I guess it is a question of what is quicker with my actual data,
_____ x 4000 ( Array( 1 to 20, 1 to 1 ) ) comparison
compared to
______ x 20 ( .Find across a 4000 cell Spreadsheet Range )

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

_ Just now I am trying a compromise where I do in fact use error handling ( which I usually avoid as much as possible as Rory said so ). Here I assume I know why an error occurs, “Turning the Error handler On” just before it occurs ( I assume it occurs because of the long string in the variant type Array. ) The Error handler then calls a Function that takes the Strings within the Variant Type ( 1 to 20, 1 to 1 ) Array and by a x 20 loop puts them in a String Type ( 1 to 20, 1 to 1) Array . This would then be fed into the .Match second Argument and avoid the error and still allow a match to be achieved as was the case in my Demo Code Lines 430 and 440 which still “worked” for the case of a String length greater than 255
_ Assuming these occurrences are minimal it may not over increase the time

_ If I have any interesting speed test comparisons I will feed them back out of interest.

Alan
\ -_- / :heavy: :jollyroger: There's a row going on down near my bottom

User avatar
Doc.AElstein
BronzeLounger
Posts: 1402
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Match works differently for second argument Array o

Post by Doc.AElstein »

Hi
_ This is a little off track, but just a brief follow up on the last couple of Posts, as it sparked my interest to try a few more variations as both a way to overcome the original problem which initiated the Thread, …….
And they are interesting on their own anyway ( or at least I think so!! )
_ Some speed test based on 4 slightly different code versions,

_Brief description of problem / Application
_ A Daily Nutrition Protocol ( Pro ) having a very detailed break down of all stuffs within a Food product has a large , but not infinite, number of Foods listed in Rows of a Worksheet in A Workbook. The names of the Food Product, such as “Apple” are in the First Column. The other columns which are ever increasing in number contain The Food Stuffs, such as Kcal, Fat towards the left, .. extending further to the right into the smallest micro organism yet ( or not yet ! ) discovered….( This main Workbook ( Pro ) also has detailed analysis tools for the Daily calculating of various aspects of a very detailed diet )
_ There are other Workbooks organized similarly, but simply containing data. They may have a row count almost infinite, or getting that way ! , stretching the limits of even a XL2007 + Worksheet! ( Typically named appropriately therefore variations of “DBlx” )
_ A Complication is that the headings, ( Heds ) , of the Food Stuffs may be in different orders in the different sheets and even spelt differently or written in different languages.. These different headings for the same Food Stuff are allowed for in all worksheets by extending the Header row from the usual 1 to ( currently 20 ). This allows flexibility and as many versions of the heading is to be encourage to be written in all sheets, so that thereis a chance , at least of getting a match!!

_ It is required to bring occasionally a Row ( a Food product ) into the Main Worksheet in correct heading order to match the master Worksheet ( Pro ) Headings.
_ Initially, I was looking to do as much as possible internally using Arrays for speed advantages.

_ Code 1 does a simple series of nested loops, “Pseudo Code”:
____ For each Heds column in Master
_________For each Heds row in Master
_______________For each Heds column in DBlx
____________________For each Heds row in the DBlx
________________________If a Heds match is found put the Stuff in an Array to finally be pasted put in the required Master ( protocol) Sheet.
( A minor but important point. ( Done in all codes ) : – once a match is found for each Pro Master I jump out of the looping. _ In the real Files the row and column match may not be too far apart, at least at the start, so this si a good idea. ( And Multiple Hed matches are not needed to be considered..) ( A further “bobge” would be to increase the Loop Bound Variable Count a bit for DBlx column, but maybe a bit risky .. ) )
It just looks for a match. It Takes the appropriate Columns of the current loops to determine the correct order

_ Code 2 was my original Attempt at removing a Loop by looking “along” the second argument Array of a .Match to find the position of a DBlx heading match rather than looping for / through each column until I found it. That failed due to columns of around 4000 being outside the typical “Worksheet Function” “column” Limit of 255. So the last loop was replaced instead, where a 2 D 1 column Array(1 to 20, 1 to 1 ) was used in the match at each DBlx column to look “along” ( ““down” .. - sort of” ) to look for the last “Row” match to find it.
_ This code suffered the weird problem which was the reason for this Thread. For now an Error handler springs in for the expected error when occasionally some text in a DBlx Hed row is over 255 characters. - A loop then creates a new Array for the .Match Search which has String Type Elements instead of Variant Types.
Again It just looks for a match in the last loop. It does not care where. It Takes the appropriate Columns of the current loops to determine the correct order

_Code 3 as a comparison to Code 2 from Han’s Suggestion, - the .Match “search “along/ down”” an Array() is replace with a .Find Method at each DBlx column applied to the 20 row, 1 column Range Object of that DBlx column.
It just looks for a match again. It does not care where a match is. Again It Takes the appropriate Columns of the current loops to determine the correct order

_Code 4 This is just doing the Code 3 in Transpose. So is what I had initially wanted to do at Code 2. With the .Find Method applied to a Spreadsheet Range Object I do not have the problem of limited 255 “column” in Array work with “Worksheet Functions”
So at each DBlx row a Range Object of 1 row x approx 4000 columns is made to which the .Find Method is applied…..
It looks for a match again. BUT uses it to determine the DBlx column. So in principle possibly a bit more efficient, ( depending on how exactly VBA goes about determining this “position “along/down”” )
_.........................................................

So Results are based on a average of about 10 runs. The First run is ignored. ( Often a first run can be a bit Longer, inconsistently. ( In these codes , for example, the Sheet Headings are only filled in once, the first time by virtue of being stored in a global Sheet Heading Variable , Global Sht Hed(). ) )


The codes are still at a development stage and all a bit messy, but just in case anyone is interested, here a few files, striped down drastically in size just to demo..
All macros in
NeuProAktuelleMakros.xlsm ___( somewhere!! )
https://app.box.com/s/vrrha0vteel125h8njezwecl0fs6rflr" onclick="window.open(this.href);return false;

Speed tests Sub Timers()
Code 1 Sub DBToPro()
Code 2 Sub DBToProMtchArrStringVaryAuntWonkFuc()
Code 3 Sub DBToProFindInColumns()
Code 4 Sub DBToProFindInRows()
( Currently the timer Code is run and Call the last Code )

Master Pro File ( Daily Diet Protocol File )
ProAktuellex8600x2.xlsm ( Edit: Some unused macros here may crash as the file has been stripped down, so best select disable macros on opening )
https://app.box.com/s/of7p2hlnezf6qrahhyl9ugf0ca75ux7d" onclick="window.open(this.href);return false;

A DBlx File
DB2IssJfürELProAbDec2014.xlsm ( Edit: Some unused macros here may crash as the file has been stripped down, so best select disable macros on opening )
https://app.box.com/s/78ce1ookpy6t6wrgsofs76zuwsl88wrq" onclick="window.open(this.href);return false;


Initial Results
Using Excel 2007
 lcPro 3482 lcDBlx 1765
Code 153.7 secs
Code 2460 secs
Code 3130 secs
Code 415 secs
Wow, the last one was a surprise. :cheers: And there I was stuck in me old Array ways ways !!!!

_ I guess an interesting variation might be a “pseudo Code”:

If RangeMaster.Find = RangeDBlx.Find Then ….
_ I guess that is not possible, .. yet.. ( actually, I guess it is.. ??

Alan

https://app.box.com/s/of7p2hlnezf6qrahhyl9ugf0ca75ux7d" onclick="window.open(this.href);return false;
https://app.box.com/s/78ce1ookpy6t6wrgsofs76zuwsl88wrq" onclick="window.open(this.href);return false;
Last edited by Doc.AElstein on 23 Feb 2016, 20:53, edited 7 times in total.
\ -_- / :heavy: :jollyroger: There's a row going on down near my bottom

User avatar
HansV
Administrator
Posts: 71978
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA .Match works differently for second argument Array o

Post by HansV »

I don't think

If RangeMaster.Find = RangeDBlx.Find Then …

would do what you want.
Regards,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1402
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Match works differently for second argument Array o

Post by Doc.AElstein »

Hi Hans
HansV wrote:I don't think
If RangeMaster.Find = RangeDBlx.Find Then …
would do what you want.
That .Find = .Find was just a vague abstract after thought ( late at night! ).. an abstract idea that I could somehow coerce VBA to look at the same time “across” both Header ranges at the same time

I believe some way with Array to do an equivalent could be theoretically possible, but very complicated.. This would involve using a matrix for the first argument . I had a go an initial go at it here:
http://www.eileenslounge.com/viewtopic.php?f=4&t=22534" onclick="window.open(this.href);return false;
I Think I can just about understand what is going on there , but have not explained too well maybe, . - Using Worksheet Functions with Array() arguments in place of the more common single values is something I have struggled with a few times now to understand…
Further routines there would probably be necessary to get the information out of a final Array and would further complicate and cause speed disadvantages again.

Coming back here..
I May be a bit progressing away from the initial problem of the Thread, but then again some of the alternative ways of doing what I want are indirectly work arounds to the original problem, as they are not affected by the problem originally initiating this Thread.
And so comparing their general performance to the other ways is appropriate. ( And it looks like the . Find idea is probably making my code just about usable now!##
I had another go with a few codes and the speed measurements. To do a better comparison I took out the Long strings and the necessary error handling for that ( Just for convenience I reduced the column size in the Code to restrict to just before the first Long string was.- So all Times are a lot quicker, but as always with these things it is the relative speeds I think that are relevant with different methods, as the actual is determined by other things like the computer etc. )
And then with reduced columns I was able to do the .Match to do the search “along” a long row at each 20 rows, ( as I am below the Array 255 “column” limit in Worksheet Functions*** ) , additionally to the code already done that did the search “along / ( down )” at each of the many columns ( here reduced to 100 ).

So there are 5 versions in Total now. Here the results

  lcPro 3482 Reduced ( 100 ) DBlx Columns
DBToPro()Simple Multi Loops7.3 secs
DBToProMtchArrShrtClms().Match search for 100 short clms56.9 secs
DBToProMtchArrLongRws().Match search for 20 long rws15.8 secs
DBToProFindInColumns().Find for 100 short clms11.3 secs
DBToProFindInRows().Find for 20 long rows3.7 secs


The results are still a bit disappointing for the .Match with Arrays versions

Considering the disappointing .Match results the “idea” at
http://www.eileenslounge.com/viewtopic.php?f=4&t=22534" onclick="window.open(this.href);return false;
I will probably not take at this stage further. Much of my code still uses Arrays, for example the row for input, is built up internally then pasted out in one go, which is almost certainly quicker than pasting out each cell for the match.

I guess this is an occasion when an Excel Method can do something in a sheet quicker than one can do “internally” with more classic Array manipulation. That must happen sometimes I guess. In this particular requirement of mine, the improvement going over to
.Find
suggested to me has probably resulted in a time improvement making my code just about practical to use!! ##

One further small thing surprised me in particular. When I changed the First argument in an index line used to generate the long Row Array used in the match from the
.Cells
to an internal Global Array ( already created in the code in the first, untimed run, - the Global DBlx Sht Headings Array)
GlobalShtHed_DBlx()
then the time increased from the 15.8 secs to 62,4 secs. I would have expected the opposite to be true. Possibly the way VBA / Excel presents such Spreadsheet Values through the implicit*** caused by the
.Cells
is of a particular efficient manner
( *** I say implicit as opposed to the Explicit, which I, as good practice, always use normally,
.Cells.Value
Will not work in the .Index for XL 2007 as the Field ( Array ) so created is as big as the sheet!!, ( and “worksheet Functions” are generally limited to a size equal to the pre XL 2007 Worksheet size (65535 rows x 255 columns I think )

Just for completeness, here a File with all Five codes, modified for reduced Columns.
DBToPro()
DBToProMtchArrShrtClms()
DBToProMtchArrLongRws()
DBToProFindInColumns()
DBToProFindInRows()

( The Speedtest code is also there
Sub Timers() )

File: ( Xl 2007 NeuProAktuelleMakros100clms.xlsm )
https://app.box.com/s/uc4mw2lkgpbagpl6saxlzuybzlv34ybq" onclick="window.open(this.href);return false;


Alan
\ -_- / :heavy: :jollyroger: There's a row going on down near my bottom

User avatar
Doc.AElstein
BronzeLounger
Posts: 1402
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Match works differently for second argument Array o

Post by Doc.AElstein »

Hi
Doc.AElstein wrote:........One further small thing surprised me in particular. When I changed the First argument in an index line used to generate the long Row Array used in the match from the
.Cells
to an internal Global Array ( already created in the code in the first, untimed run, - the Global DBlx Sht Headings Array)
GlobalShtHed_DBlx()
then the time increased from the 15.8 secs to 62,4 secs. I would have expected the opposite to be true. Possibly the way VBA / Excel presents such Spreadsheet Values through the implicit*** caused by the
.Cells
is of a particular efficient manner........
I think actually understanding a bit more about that is coming to a fundamental thing here that I may have overlooked or not known. Similarly the following can be explained for the case under consideration here
Doc.AElstein wrote:........I guess this is an occasion when an Excel Method can do something in a sheet quicker than one can do “internally” with more classic Array manipulation. ........
_............................................

OK. I have been caught out by using the .Index ( and for that matter the .Match ) as part of my so called ““internally” with more classic Array manipulation”.
It is Good to consider here that these are Worksheet Functions. Maybe therefore they have been optimized for dealing with Ranges in Spreadsheets. I expect the option to include Arrays rather than Ranges in their arguments will involve therefore some extra work for VBA.

Look at my Code using the .Match ( the one to look “along” a long Row )
Sub DBToProMtchArrLongRws()
Consider the following situations, and approx code lines, and some new speed tests form Today

Situation 1 )
slcArr() = Application.Index(GlobalShtHed_DBlx(), rDBlxHed, Evaluate("=Column(K:" & FucshgMathsVBA(lcDBlx) & ")"))
MtchHed = Application.Match(srchHed, slcArr(), 0)

Despite GlobalShtHed_DBlx() ( which is an Array ) being a global variable not considered necessarily in the speed tests, it gave the worst time, ( Today 46 secs )

Situation 2)
An Improvement was found doing this

slcArr() = Application.Index(wsDBlx.Cells, rDBlxHed, Evaluate("=Column(K:" & FucshgMathsVBA(lcDBlx) & ")"))
MtchHed = Application.Match(srchHed, slcArr(), 0)
Time Today 11.7 secs

Situation 3)
Here a new code situation, from experiments today, - a created range..

Dim srcRange As Range: Set srcRange = wsDBlx.Range("A1", wsDBlx.Cells(1, lcDBlx))

slcArr() = Application.Index(srcRange, rDBlxHed, Evaluate("=Column(K:" & FucshgMathsVBA(lcDBlx) & ")"))
MtchHed = Application.Match(srchHed, slcArr(), 0)
Despite creating an extra Range, Time is a reasonable 13 secs

Situation 4) going backwards a little

Dim srcRange As Range: Set srcRange = wsDBlx.Range("A1", wsDBlx.Cells(1, lcDBlx))

slcArr() = Application.Index(srcRange.Value, rDBlxHed, Evaluate("=Column(K:" & FucshgMathsVBA(lcDBlx) & ")"))
MtchHed = Application.Match(srchHed, slcArr(), 0)
Time is 20 secs

This is all supporting the idea of VBA doing extra work to work with a Fields of Values ( Array() ) rather than a Range.

Situation 5) Let’s see if the .Match follows similarly.

Dim rnglongSrcRow As Range
Set rnglongSrcRow = wsDBlx.Range(wsDBlx.Cells(rDBlxHed, 11), wsDBlx.Cells(rDBlxHed, lcDBlx))

MtchHed = Application.Match(srchHed, rnglongSrcRow, 0)

Time is 1.9 secs !!!

( Today for comparison, the best code so far, the ".Find comparable “Long Row”" code is taking 2.8 secs


I suggest that the bottom line in this case is to choose ( as far as the Heading Match is concerned ) use of the .Match, but concentrate on efficiently giving it Range Objects to work with. It appears to have been designed for this and is particularly good at it. I have been told this before, for example..
rory wrote:……. it's not altogether surprising that worksheet functions work well with ranges. ;)
.. but it sometimes needed to learn the hard way, never the less!


I expect VBA does with the .Match, an optimized / simplified / efficient form of the .Find followed by .Column.
As always, with Hind sight it all makes sense…

And….
Surprise, Surprise…
_a ) I can take that Range well over 255 ( the Array argument slcArr() was limted to 255 )
And
_b ) There is no longer any strange problem with Long Strings.

I expect this will not help me with my last “interesting idea”
http://www.eileenslounge.com/viewtopic.php?f=4&t=22534" onclick="window.open(this.href);return false;
as any outputted Array from the "Perverted .Match" there will probably be limited to 255 “columns” as I have found is the case with the .Index, but I have thanks to the participation here got a very good solution now to, my original requirement.
So The original anomaly is not cleared, but I have a very good alternative set of solutions which are not affected by this problem..

Thanks
Alan


_.....................................
 Secs
DBToProMtchArrStringVaryAuntWonkFuc490
DBToProFindInColumns130
DBToProrngMtchInColumns122
DBToPro 'Simple Only looping code52
DBToProFindInRows17.6
DBToProMtchRngLongRws3.3
DBToProMtchRngOfstLongRws3.1
Last edited by Doc.AElstein on 07 Mar 2016, 22:59, edited 1 time in total.
\ -_- / :heavy: :jollyroger: There's a row going on down near my bottom

User avatar
Doc.AElstein
BronzeLounger
Posts: 1402
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Match works differently for second argument Array o

Post by Doc.AElstein »

Hi,
This is just a remark or observation. But it may help point in the direction of an explanation to my original Problem.
If anyone has any thought on the following then great.....
If not, no problem. – The workarounds are developing fine.

So maybe the answer lies somewhere in the vary basic definition of the Variant. This would be a valid definition, I think:

' ".......'Variant Variable Type is constructed to accept all info allowing its use for most other Variables. One exception is a String of defined length. ...."....

Well that is interesting.,.......

_ 1) The code I gave in post 1 shows that I have no problem putting a string of greater than 255 characters into a variant variable either as a simple Single Variable or as Variant Type Elements in an Array. ( Lines 70 to 210 )

Now I got the point somewhere along the line about how VBA “stores” strings
http://www.mrexcel.com/forum/excel-ques ... ost4413433" onclick="window.open(this.href);return false;
http://www.mrexcel.com/forum/excel-ques ... ost4416963" onclick="window.open(this.href);return false;

~~~~The end result is that at the declaration…….
~~~~~~Dim str as String
~~~~~~~~~~~~~~~~~We only set a “pigeon Hole” containing an initial start position set aside for the start of the actual value it might later be given. Later this will be given at the assignment
~~~~~~Let str = “kjgfjhasfag”
~~~~~~~~~~~~~~~~~~~~~~~~~Information about its start potion and length. Both these "arguments" are volatile, that is to say they can and likely will be changed in the program as the actual value in str itself may change.

So is this telling me that Variant has no problem then ( from it’s definition ) as str is not strictly defined as having a fixed length.
What is still not quite clear as to why in the case of a .Match a problem comes for Strings above 255 characters in the case of Variant Type Elements. My experiments showed that it will except strings over 255 when the Array Elements in the second argument of the .match are String types.
Maybe some internal VBA working when doing the .Match “freezes” the length ( by design or accident ) of Strings greater than 255. ( An explanation could be that that occurs by an “early” truncation to 255 as we know the .Match will not return a String greater than 255. ) The String Type does not mind that. The Variant Type gets rather upset as it was not in his work contract to carry Fixed strings. ( For some reason the .Index does not suffer this problem. – Maybe it only truncates “Later” at the end ?? )

Just throwing the above in, in case something might “click” with anyone in the know to help shed a bit of light on this strange problem.
As I said The workarounds are developing fine, so I do not have a big problem with this now. But what I just wrote might enable someone in the know to see immediately what the explanation is.

Thanks for Watching again

Alan
\ -_- / :heavy: :jollyroger: There's a row going on down near my bottom

User avatar
Doc.AElstein
BronzeLounger
Posts: 1402
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Match works differently for second argument Array o

Post by Doc.AElstein »

Hallo,
This is just to add a solution and recap in preparation of a follow up I am preparing. No reply is particularly wanted to this Post
, but then in the next Post someone may wish to comment on an interesting phenomena that I will be describing. The relevance will be more apparent once I have prepared and posted the next Post, may be..
_....

Recap – what is all this about

A work around to the Original problem of this Thread was to use a code line of the Form, ( Pseudo Code ):

IntegerPositionAlongOfHeadingMatch = Application.Match( SrchHeding , LongRowRangeObject , 0 )

In fact it was found to have many advantages and be better anyway than the original

IntegerPositionAlongOfHeadingMatch = Application.Match( SrchHeding , arr() , 0 )
Where arr()=LongRowRangeObject.Value

And in fact was quickest of other ways attempted along the way.

So I should have guessed that Sod’s Law would say that this , of all ways, would have in some application a fatal problem ( Excel Bug ) when all other ways are OK. It did !! , and discussing that will be the crux of my next post. I am wanting to do that partly to make it aware for anyone using the results of this Thread, and maybe to invite any comments on it.
_........................

I did a lot of ways previously , as summarized for example in the table at the end of this Post.
http://www.eileenslounge.com/viewtopic. ... 56#p175343" onclick="window.open(this.href);return false;

I know a few People , not just me have used that Table for reference. So I felt it was appropriate to post a “Warning” of an interesting ( at least academically ) Bug in Excel VBA
Application.Match( , LongRowrangeObject , )

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

I wanted to supplement that table with the results of just one more experiment before getting on to the main Point in the next Thread. So I will explain that briefly, then give a new table where I repeat all the previous experiments along with doing the new one. As mentioned previously it is important to do that as only the relative times are important for a comparison, as the actual times will vary from day to day, with different Computers , different Software etc. … etc.

New Way
Sub DBToProWSTFMtchRngOfstLongRws() ' .WorkSheeTFunction.Match
This is nothing special but just a way that is good to have for discussing the problem ( Excel Bug ) in the next Post. It is the direct equivalent of the Application.Match code section discussed above, but using a Application.WorksheetFunction.Match instead. I want this particularly for comparison as initial experiments are suggesting that it does not have the same Bug.

I give the two comparison sections with no ‘ Comments here for clarity. In the uploaded ( Linked ) file with the macros in it, the lines are extensively explained in the comments scrolling off to the right

Code: Select all

Sub DBToProMtchRngOfstLongRws()


                Dim MtchHed As Variant
                Dim rnglongSrcRow As Range
                 Set rnglongSrcRow = rnglongSrcRow1.Offset(rDBlxHed - 1, 0)
                 Let MtchHed = Application.Match(srchHed, rnglongSrcRow, 0)
                    If IsError(MtchHed) Then
                    Else
                    Dim cDBlxHed As Long
                     Let cDBlxHed = (CLng(MtchHed) + 10)

Code: Select all

        Sub DBToProWSTFMtchRngOfstLongRws()

                        Dim MtchHed As Long
                        Dim rnglongSrcRow As Range
                         Set rnglongSrcRow = rnglongSrcRow1.Offset(rDBlxHed - 1, 0)
                         On Error Resume Next
                         Let MtchHed = Application.WorksheetFunction.Match(srchHed, rnglongSrcRow, 0)
                            If Err.Description <> "" Then
                             'Err.Clear
                             On Error GoTo 0
                            Else
                             On Error GoTo 0
                            Dim cDBlxHed As Long
                             Let cDBlxHed = (MtchHed + 10)
These are the main new aspects of the code, summarized. They are necessary as WorksheetFunction.Match in the latter actually errors if no match is found. ( .Match in the former code returns an actual error when no match is found )
http://www.excelforum.com/excel-new-use ... ost4017211" onclick="window.open(this.href);return false;

On Error Resume Next
This is a user defined Error handler which overrides the default error handler. It actually suppresses an error situation being raised, and instructs the program to continue just after the line which would have raised an “Error Situation”. An “Error Situation” , sometimes called an Exception, or Error exception , or an Erected Exceptional VBA state, etc..etc.. is a situation in which VBA is “thinking” something is wrong. It behaves in such a situation differently. For example it ignores any further attempts by you to define a new Error Handler. It also ignores your attempts to “switch off” or “switch back” to the default error handler. In the case of using On Error Resume Next Error handler Statement, VBA has been prevented from being aroused to its Erected exceptional state of being. Because of this suppression, you will often see the Error Handler Statement On Error Resume Next being accompanied close by a further Error Handling Statement, On Error GoTo 0( One might use also an On Error GoTo some_label/line_number user defined error handler instead here, assigning a different user defined error handler. - Again this is possible because VBA has been prevented from being aroused to its Erected exceptional state of being. )
On Error GoTo 0
This Error handler Statement “goes” nowhere. It “switches off” the current user defined or “switches back” to the default error handler. It would normally only work if no Error had been encountered. But in the case of using On Error Resume Next it will work. This is because, as noted, On Error Resume Next has surprised the Arousal
On Error GoTo some_label/line_number
This Error handling Statement does “go somewhere!”. It “goes” to the code section labeled in such a way some_label: ( One can also replace the label with a code line, if you have that code line in your code ). In this case there is no suppression of the aroused Erected Exceptional VBA state. VBA is now “thinking” something is wrong. It behaves in such a situation differently. For example it ignores any further attempts by you to define a new Error Handler. It also ignores your attempts to “switch off” or “switch back” to the default error handler. However, this error handler allows the code to continue, ( rather than stopping as is the case by the default error handler ) , at the point the code “goes to”. Typically things are done there that should be done on the case of an error.
Object Err
There is an Object Err available to us. This has a couple of Methods and some Properties. These Properties are strings of information about the last type of error that was encountered. So this can be used, for example, as an indication of if an error occurred after using On Error Resume Next . Note however, If one is wanting to utilize this information in the case of a On Error Resume Next, then one should do that before an On Error GoTo some_label/line_number or a On Error GoTo 0 . This is because, these two Error Handling Statements Clear that information. ( This clearing can also be done by a code line of Err.Clear )


Results
( Using Computer: Aspire 7535G 32Bit 4GB RAM
Excel version: 32 Bit XL 2010 )

Using Excel 2007
Sub Routine NameTime ( Secs )
DBToProMtchArrStringVaryAuntWonkFuc533.4
DBToProFindInColumns174.9
DBToProrngMtchInColumns99.7
DBToPro 'Simple Only looping code94
DBToProFindInRows24.2
DBToProMtchRngLongRws2.7
DBToProMtchRngOfstLongRws2.4
DBToProWSTFMtchRngOfstLongRws2.3
|< < > >|_Head_/___//

' Files needed for these experiments
' Reduced data test Files ( Important: Do not change the Active Cells )
' https://app.box.com/s/mer754vp2zsnjps2gid5icxa24d06rws" onclick="window.open(this.href);return false; ( .xlsm but macros do not need to be enabled ) )
' https://app.box.com/s/wj88zbgvdyn1kk498xq8bveizzn4vxvs" onclick="window.open(this.href);return false; ( .xlsm but macros do not need to be enabled )

'
' File with the Codes in it
' https://app.box.com/s/uexpbzuytxsln5sb6tmxkvzfml14utqh" onclick="window.open(this.href);return false; ( .xlsm, macros need to be enabled )
' ( To do the experiments you run
[/color]
Sub Timers()
After un commenting in two places in that code the Call to the Code you want to test. ( and possibly adjust the MaxIteration to a number you are happy with as the Timer will Loop and average the results )

Alan

' Rem Ref http://excelmatters.com/2015/03/17/on-error-wtf/" onclick="window.open(this.href);return false;
' https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo" onclick="window.open(this.href);return false;
\ -_- / :heavy: :jollyroger: There's a row going on down near my bottom

User avatar
Doc.AElstein
BronzeLounger
Posts: 1402
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Match works differently for second argument Array o

Post by Doc.AElstein »

Hello,
I was referencing this Thread recently . In particular I was checking again the speed test comparisons tests of the different ways I was comparing in the complex “Multi Heading compare” routines discussed.
I realized I had missed an obvious extra variation. So I did that. :)

So I am just adding a solution for later reference in this post , :)
In brief I am adding the “Multi Row Find solution”.
I stupidly missed that. _…
To explain:-
_.. Somewhere back I had come to the conclusion that Using a “Match in ( Long ) Range rows” was the best solution as a work a round to my original problem, ( of which the subject of this Thread is ) .
That solution involved
_ looking for a heading match across a long row using the

___ .Match( SerchHead , LongRangeRow(j) , 0 )
_ Then repeating that down about 20 ( J = 1 to 20 ) heading rows. ( I had multiple Heading options, so 20 Heading rows to allow for different spellings , etc.. )

If followed on from that, that one alternative was to do virtually the same using “Find in rows” ( amongst others I also did .Match and .Find in columns, - each column being a 20 row, single column, Heading Range ). In the “Find in rows” I did like
___ LongRangeRow(j).Find.Column
_ Then repeating that down the 20 heading rows.

Of course that made a nice comparison, but stupidly I did not realize that .Find works on any Range, so obviously I can / should try a code version that uses the_..
___ LongRange20Rows.Find.Column
_.. and then while I am at it , experiment with the search arguments, in particular going along xlbyColumns and xlByRows.
So I did that. That is what this post is about :)

It was not too difficult to do the code using the existing ones:- I just take the code “Find in rows” , remove the looping down the 20 heading rows, and replace the dynamic long row Range, LongRangeRow(j), with a fixed Range extending over the 20 heading row, LongRange20Rows.Find.Column

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

As always with these things, only relative time measurements are useful , as a comparison, ( at least with my old slow computers…) , so here is the latest set of results , ( Updated Files at Links as at end of the post: http://www.eileenslounge.com/viewtopic. ... 12#p192781" onclick="window.open(this.href);return false; )
Done on 4 computers:
Acer Aspire 4820TZG 32Bit 4GB RAM Vista
32 Bit XL 2007
____ Acer Aspire X3200 32Bit 4GB RAM Vista
____ 32 Bit XL 2007
____¬¬¬¬¬¬¬¬____________ Dell 64 Bit Windows 7
________________________ ______ 32 Bit XL 2007
_____________ Aspire 7535G 32Bit 4GB RAM Vista
_________________ _____________ 32 Bit XL 2010

MatchFindVarArr.JPG http://imgur.com/E48Bf2R" onclick="window.open(this.href);return false;
MatchFindVarArr.JPG
Just some conclusions and remarks while I am here…
Previously I had been a great fan of exclusively doing captured Variant Array analysis for these sort of thing. Various limitations forced me into considering the Worksheet Functions of Match and Find. I was surprised and impressed of the improved performance of considering a Find or Match as an alternative to finding a match “along a long row”. I have had some interesting results along the way.., for example:
_ that looping down the 20 rows in 20 separate long single row Finds was slightly quicker than a single Find for a 20 row range
_ there is little difference using Find with arguments xlByRows or xlByColumns for the 20 row single range. ( This despite that, as expected , doing a Match of Find for the 20 rows in a loop for the many columns is very slow. – I was expecting therefore an increased time for xlByColumns ? ) ( I also find that if I had multiple cells as a valid to be found, then the argument of xlByRows or xlByColumns makes no difference and the cell found first is that as if a search was done across the first row, then next row down.. etc.. ). I wonder if that is a bug.. , or I have not understood what xlByRows or xlByColumns means ?? )
_ I note a strange slowness of WorksheetsFunction.Match compared to Application.Match for XL 2007 and not for XL 2010

In any case the Match across a long row was by far the best to the point of , on my somewhat older computers and XL versions, being the only practical way for a close to instant merge of one row from one workbook to the other..


I do note that I am doing a comparison of Find in a situation to compare exactly with Match. I would expect Match has been optimized to do this limited version of what Find can do.
So it could be said that the comparison I did was of limited worth....... but...
BUT, I am very glad I did: Here is a very important point: Unless you have a fairly fast 64 Bit machine with a recent XL version, you can forget doing any such Match operation of this sort for more than a handful of loops: Excel has a Weird Memory of memory problem for Range objects used in Match and that soon freezes up Excel , and if you are unlucky , your whole computer. Then either a Excel restart or pulling the plug to do a computer reboot is necessary to recover the situation
https://www.excelforum.com/excel-progra ... lease.html" onclick="window.open(this.href);return false;

Find does not appear to have these limitations.
https://www.excelforum.com/excel-progra ... bject.html" onclick="window.open(this.href);return false;
https://www.excelforum.com/development- ... ost4497483" onclick="window.open(this.href);return false;


Alan


https://fastexcel.wordpress.com/2011/10 ... mment-4175" onclick="window.open(this.href);return false;
You do not have the required permissions to view the files attached to this post.
\ -_- / :heavy: :jollyroger: There's a row going on down near my bottom

User avatar
Doc.AElstein
BronzeLounger
Posts: 1402
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: VBA .Match works differently for second argument Array o

Post by Doc.AElstein »

Hello,
Some problems and Bugs similar to the ones associated with Match discussed here in this Thread, have been continually causing me grief, to the point of me wanting to avoid worksheet functions, especially Match, like the plague, and go back to trusted well predictable simple VBA Array codes..

A spin off to a workaround to another problem I had is worth taking on to the measurements here, I think..

This thread was left at the point of a good solution to a complicated sorting code using Match, but with the important note that doing it too often in a code was dangerous as some weird leakage Bug ends up crashing Excel or your computer..
A Find alternative did not seem to have the Bug but was slower to the point of making it a bit impractical for “real time” / “instant” use.

I had a failed attempt at a solution to use Match that would not exhibit the Bug, ( In other words despite my hope to the contrary it still did exhibit the Bug (https://www.excelforum.com/excel-progra ... ost4674054" onclick="window.open(this.href);return false; ) :(
But the spin off was that the Find version of the code was only a bit slower than the Match version code. So I thought it was worth a look back here..
I have a couple of codes therefore to add to the collection done for this Thread:
Sub DBToProMtchRngOfstLongRwsFirst()
Sub DBToProFindRngOfstLongRwsFirst()

The main difference to all the previous codes is that the main outer loop is now the long Range row. So the main Loop is done 20 times.
I found that minimizing how often changing which Range is used might sometimes give different and improved results. In other words the same final amount of range references are made, but the difference with the new codes is that you do all you want to do with one range, then go on to the next range, and so on.
Hope that makes sense: It probably doesn’t, so again to clarify what I mean:
Previous codes pseudo like: Use ranges in any mixed up order: rng1 , rng3, rng1, rng 2, rng1 , rng3 , rng1 , rng9, rng1 ….. etc.
New codes like: Use ranges in groups: rng3, rng3, rng3, rng1 , rng1, rng1, rng1 , rng1 , rng2 , rng2 .. etc.

This change requires quite a messy fiddling around and the rearranged logic to the sorting needs to be considered carefully I think to do this efficiently: You need to be careful not to do a lot of unnecessary or repeated looping
Maybe then a slightly different approach/ extra thing is needed_.. that is to say,
'_- something new is added, and
'_- the things are looped in a different order, and
'_- some of the previous bit from in the Center of the looping which fills the output Array, is taken out

'_- 'Array whose indicia is main Pro column, and members are column where to get from in the data file
_..Introduce a new 1D Array, DBlxInd() , (size 8 To last_column_lcPro,_in_main_workbook_File,_Pro). So the Array indice is “where to go” / “where to put in” ( column number ) in main workbook, Pro. It will be filled with an indicia of “where to get from column ” in the data workbook, DBlx.
Then the actual filling in is done later outside the Looping for matching headings in the two Files

'_- 'The main outer loop is now done 20 times for each Set long range row==Looping start
__For the long range, looping to 20 times for data workbook DBlx headings. So you are at a long row Range object.
____For every heading column , cProHed, of Pro, 8 To lcPro
______If Array DBlxInd(cProHed) = Empty Then
________For ever heading row, rProHed, of pro 1 To 20
__________If the Pro heading is not empty (and check a few other valid data criteria)
____________Center of Loops: search to find a match in long row Range object of the Pro(rProHed, cProHed) heading and if get it then put that matched column indicia in the current column in DBlxInd(), DBlxInd(cProHed)=MtchHed , and Exit the For row, rProHed

So that all starts again for a Next of in total the 20 for data workbook DBlx headings ranges.==Looping End

'_- 'Fill output Array ( Previously this was in the middle of the looping )
When that’s all finished we should have an Array, DBlxInd(), which is then used to go through and fill an Array to be pasted out, arrOut(). ( It is made from taking an average of If there may have already been some entries in the row to be filled in. The row to be filled in is brought in initially into an array, arrIn(), so that the user can paste that back in as a “restore” as it were.. )

Here, the last two measurements are the new code results.
MatchFindSpdTstiesJune2017.JPG http://imgur.com/LHK984y" onclick="window.open(this.href);return false;
MatchFindSpdTstiesJune2017.JPG

For the Match code we have the best results so far. ( But even in these test measurements the Match Bug reared its putrid head occasionally , and I found I had to restart Excel before and after any Match to be sure as to get consistent results. )
The new Find code is still behind the Match in performance, but is getting close to an acceptable real time value. It is 4 – 5 times quicker than the previous Find codes at doing the same thing.

At the end of the Day, it is a lot of mucking about to try and get the logic to suit best how ( if !! ) the Excel functions work. The Find appears up until now fairly well behaved ( although I did recently discover a quirk / Bug with that also. - Usually that is hidden when you use Range.Find followed by Range.FindNext. – Maybe the programmers put a bucket under .Find to catch the leak from it and used what spilled out to make Range.FindNext work ).
Quite frustrating sometimes. :(

Alan

( The latest Beautiful codes are in the uploaded updated Files – see bottom of this post http://www.eileenslounge.com/viewtopic. ... 12#p192781" onclick="window.open(this.href);return false; :) )
You do not have the required permissions to view the files attached to this post.
\ -_- / :heavy: :jollyroger: There's a row going on down near my bottom