Application match doesn't work with long strings
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: Application match doesn't work with long strings
Evaluate is limited to 255 characters. Given the length of your string variable, I do not think it will work for you.
Regards,
Rory
Rory
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: Application match doesn't work with long strings
Then it would be useful if you could answer my question related to the solution proposed above
Code: Select all
x = Application.Evaluate("=Match(True, Index(A:A=""" & s & """, 0), 0)")
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Application match doesn't work with long strings
This doesn't work with the variable s. It only works if the value in a cell and I refer to that cell.
-
- 4StarLounger
- Posts: 584
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Application match doesn't work with long strings
Hello
Coming back to the original wanted code line that errored.
Application.Match(s, Columns(1), 0)
The problem seems to be the first argument of the Match, which does not like having a string value bigger than 255 characters.
(In fact, if the second argument was an array of values, rather than a range, it would not like it if any of the elements of that array were larger than 255 characters, even if the first argument of the Match was a string less than 256 characters. )
The best workaround I can think of so far, is along the lines of looking for just the first 255 characters.
We would need to change the second argument to be an array with elements of just the first 255 characters as well.
The Pretty Evaluate Range code line idea allows us to nicely get something like that.
( If I may, I will use a large range in column 1 rather than column 1 from now on, as I sometimes hit strange problems manipulating a whole column – probably memory issues on my older computers or something similar )
This sort of workaround seems to work for me
Alan
Coming back to the original wanted code line that errored.
Application.Match(s, Columns(1), 0)
The problem seems to be the first argument of the Match, which does not like having a string value bigger than 255 characters.
(In fact, if the second argument was an array of values, rather than a range, it would not like it if any of the elements of that array were larger than 255 characters, even if the first argument of the Match was a string less than 256 characters. )
The best workaround I can think of so far, is along the lines of looking for just the first 255 characters.
We would need to change the second argument to be an array with elements of just the first 255 characters as well.
The Pretty Evaluate Range code line idea allows us to nicely get something like that.
( If I may, I will use a large range in column 1 rather than column 1 from now on, as I sometimes hit strange problems manipulating a whole column – probably memory issues on my older computers or something similar )
This sort of workaround seems to work for me
Code: Select all
Option Explicit
Sub Workaround255s() ' https://eileenslounge.com/viewtopic.php?f=30&t=39186
Dim arrLeft255() As Variant
Let arrLeft255() = Evaluate("=IF({1},LEFT(A1:A9999, 255))")
' Or
Let arrLeft255() = Evaluate("=INDEX(LEFT(A1:A9999, 255),)")
Dim Exy As Long, Es As String
Let Es = Range("A49").Text
Let Exy = Application.Match(Left(Es, 255), arrLeft255(), 0)
' Or
Let Exy = Application.Match(Left(Es, 255), Evaluate("=IF({1},LEFT(A1:A9999, 255))"), 0)
' Or
Let Exy = Application.Match(Left(Es, 255), Evaluate("=INDEX(LEFT(A1:A9999, 255),)"), 0)
End Sub
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 4StarLounger
- Posts: 584
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Application match doesn't work with long strings
Hi
But you may occasionally see that in similar situations, and it appears to be unnecessary. But sometimes there is a reason and it may be necessary, even if it appears to you to be unnecessary.
Remember what I said here to you in another post? https://eileenslounge.com/viewtopic.php ... 44#p297644
When we do things in Evaluate(" "), we sometimes do not get the array of results we wanted. Often the results are there, somewhere, but they annoyingly are not given. ( There seems to be a parallel in the way it works to how things in a spreadsheet work or don’t, depending on whether you enter normally or do the so called CSE thing).
As far as I can tell this only occurs in office versions of 2013 and lower.
The trick I showed you before was like
IF({1},___)
, where ___ is the thing you want an array of results from, and you suspect it has, but is annoyingly not giving you. I found that empirically a few years back - ( It is possible that someone else noticed that long before I did. I never saw any examples yet, but I claim no “ownership” of it, Lol! )
Another trick that has been used by others is
Index(___ , )
These demos may not be so useful unless you can get access to an Office version of 2013 or lower.
Put any values like this in cell A1 and A2
Hello
Adeel1
If you run this macro in Excel 2013 or lower, you will get
Hell
Hell
_.__
Now do the same experiment with either of these macros and you should get
Hell
Adel
_.________________
The 2 tricks don’t always do exactly the same, but mostly they do. I won’t labour the point as it’s a bit off-topic, but its something probably to do with the fact that INDEX is one of a few worksheet things that actually return a range object, or range reference , which are similar, sort of.
If you have access to Excel 2013 or lower, and run the next demo coding, preferably in Debug step F8 mode, and set a watch on the variables, and read the 'comments , then you might get the point.
I suspect the results got on the next demo coding may be similar for newer Excel versions, but I am not sure
Alan
Ref
https://www.myonlinetraininghub.com/exc ... ment-84892
As SpeakEasy said, it is not necessary here.
But you may occasionally see that in similar situations, and it appears to be unnecessary. But sometimes there is a reason and it may be necessary, even if it appears to you to be unnecessary.
Remember what I said here to you in another post? https://eileenslounge.com/viewtopic.php ... 44#p297644
When we do things in Evaluate(" "), we sometimes do not get the array of results we wanted. Often the results are there, somewhere, but they annoyingly are not given. ( There seems to be a parallel in the way it works to how things in a spreadsheet work or don’t, depending on whether you enter normally or do the so called CSE thing).
As far as I can tell this only occurs in office versions of 2013 and lower.
The trick I showed you before was like
IF({1},___)
, where ___ is the thing you want an array of results from, and you suspect it has, but is annoyingly not giving you. I found that empirically a few years back - ( It is possible that someone else noticed that long before I did. I never saw any examples yet, but I claim no “ownership” of it, Lol! )
Another trick that has been used by others is
Index(___ , )
These demos may not be so useful unless you can get access to an Office version of 2013 or lower.
Put any values like this in cell A1 and A2
Hello
Adeel1
If you run this macro in Excel 2013 or lower, you will get
Hell
Hell
Code: Select all
Sub WotsNotIfIndex() ' https://eileenslounge.com/viewtopic.php?p=303525#p303525
Dim arrLeft() As Variant
Let Range("B1:B2").Value2 = Evaluate("=LEFT(A1:A2, 4)")
End Sub
Now do the same experiment with either of these macros and you should get
Hell
Adel
Code: Select all
Sub WotsWivIf()
Dim arrLeft() As Variant
Let Range("B1:B2").Value2 = Evaluate("=IF({1},LEFT(A1:A2, 4))")
End Sub
Sub WotsWivIndex()
Dim arrLeft() As Variant
Let Range("B1:B2").Value2 = Evaluate("=INDEX(LEFT(A1:A2, 4),)")
End Sub
The 2 tricks don’t always do exactly the same, but mostly they do. I won’t labour the point as it’s a bit off-topic, but its something probably to do with the fact that INDEX is one of a few worksheet things that actually return a range object, or range reference , which are similar, sort of.
If you have access to Excel 2013 or lower, and run the next demo coding, preferably in Debug step F8 mode, and set a watch on the variables, and read the 'comments , then you might get the point.
I suspect the results got on the next demo coding may be similar for newer Excel versions, but I am not sure
Code: Select all
Sub IndexIf1Stuff() ' https://eileenslounge.com/viewtopic.php?p=303525#p303525
Dim vTemp As Variant
' In the next two lines are OK. Evaluate returns a Range initially. But it gets changed to an Array of values
Let vTemp = Evaluate("=A1:A10")
Let vTemp = Evaluate("=INDEX(A1:A10,)")
Dim arrV() As Variant
' The next two lines will error as a range is returned initially, which arrV() does not like
Let arrV() = Evaluate("=A1:A10") ' This will error
Let arrV() = Evaluate("=INDEX(A1:A10,)") ' This will error
' These next two lines are OK. An array is returned, and arrV() is happy with that
Let arrV() = Evaluate("=A1:A10").Value
Let arrV() = Evaluate("=INDEX(A1:A10,)").Value
' The next lines will not error. The IF takes the range as argument, and returns an array.
Let arrV() = Evaluate("=IF({1},A1:A10)")
Let arrV() = Evaluate("=IF({1},INDEX(A1:A10,))")
' We can check this returning Range ( or not ) issue here....
Dim Rng As Range
' The next two lines are OK
Set Rng = Evaluate("=A1:A10")
Set Rng = Evaluate("=INDEX(A1:A10,)")
' The next two lines will error
Set Rng = Evaluate("=IF({1},A1:A10)")
Set Rng = Evaluate("=IF({1},INDEX(A1:A10,))")
End Sub
Alan
Ref
https://www.myonlinetraininghub.com/exc ... ment-84892
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: Application match doesn't work with long strings
Yes, already know that is what you are experiencing. My question was: what error are you getting?YasserKhalil wrote: ↑21 Jan 2023, 16:08This doesn't work with the variable s. It only works if the value in a cell and I refer to that cell.
And does it fail whatever the length of the string put into s?
I ask because my proposed solution for handling s as a variable rather than a reference to a range works fine here. Butr this may be due to the fact that I am using Excel 2021, and I seem to recall older versions of Excel have a 256 character limit to the formulas being passed to Worksheet.Evaluate and Application.Evaluate functions
So can we op a simple workaround for this as well? Yep!
This is my variant of a function written by someone called Mathieu Guindon on Stack Exchange
Code: Select all
Private Function vbAppEvaluate(ByVal expression As String) As Variant
Const Temp As String = "TempNameDeleteMe"
With Names.Add(Temp, expression)
vbAppEvaluate = Application.Evaluate("=" & Temp)
.Delete
End With
End Function
Code: Select all
x = Application.Evaluate("=Match(True, Index(A:A=""" & s & """, 0), 0)")
Code: Select all
x = vbAppEvaluate("=Match(True, Index(A:A=""" & s & """, 0), 0)")
(As an aside, I've been using Application. Evaluate here, following your original code - but Activesheet.Evaluate is a better choice since it is about twice as fast since Application.Evaluate internally appears to do the evaluation twice!)
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: Application match doesn't work with long strings
Yep, same on Excel 2021. Not surprisinglyDocAElstein wrote: ↑21 Jan 2023, 17:33I suspect the results got on the next demo coding may be similar for newer Excel versions, but I am not sure
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Application match doesn't work with long strings
Thank you very much for your great efforts.
I have tried the udf you offered like that
But I encountered an error [Application-defined or object-defined error] at this line
I have tried the udf you offered like that
Code: Select all
Sub Test()
Dim x, s As String
s = Range("A1000").Value
x = vbAppEvaluate("=Match(True, Index(A:A=""" & s & """, 0), 0)")
Debug.Print x
End Sub
Private Function vbAppEvaluate(ByVal expression As String) As Variant
Const Temp As String = "TempNameDeleteMe"
With Names.Add(Temp, expression)
vbAppEvaluate = Application.Evaluate("=" & Temp)
.Delete
End With
End Function
Code: Select all
With Names.Add(Temp, expression)
-
- 4StarLounger
- Posts: 584
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Application match doesn't work with long strings
That’s good to know, thanks. I couldn’t think of any reason that it shouldn’t give similar results.
_.___________________________
That UDF you offered is an interesting idea. I have come across the idea before of Evaluating a named range where the named range RefersTo a formula ... In Excel 4 macros for example. Also that idea is what led to my “UDF to change cells in other cells than the one its in “hack”” . Actually I was thinking along the ideas of that UDF you offered myself at one point today. But I could not get it to work…
I have also tried a version of your offered UDF in Excels 2003, 2007, 2010, 2013 on a few computers. In my test range I also included a string of 255 and 256 character length.
For 2007, 2010, 2013 I can only get it to work for the 255 character length. For the 256 length and for an attempt with the full length string I also hit an error at the same point as Yasser, but I get a different error. ( I did get the same error as Yasser initially sometimes, but that was cured by making sure I had the correct worksheet activated)
Code: Select all
Option Explicit
Sub NameAEvaluateExpression() ' https://eileenslounge.com/viewtopic.php?p=303575#p303575
Dim WsYSE As Worksheet: Set WsYSE = ThisWorkbook.Worksheets.Item("YasserSpeakEasy")
Dim vTemp As Variant, Es As String, Es255 As String, Es256 As String
Let Es = WsYSE.Range("A49").Text
Let Es255 = Left(Es, 255) ' I have put this text in cell A8
Let Es256 = Left(Es, 256) ' I have put this text in cell A9
'Let vTemp = Application.Evaluate("=Match(True, Index(A:A=""" & Es & """, 0), 0)")
WsYSE.Activate ' Need to do this or Index might go to wrong range
Let vTemp = vbAppEvaluate("=Match(True, Index(A:A=""" & Es255 & """, 0), 0)") ' works - returns 8
Let vTemp = vbAppEvaluate("=Match(True, Index(A:A=""" & Es256 & """, 0), 0)")
Let vTemp = vbAppEvaluate("=Match(True, Index(A:A=""" & Es & """, 0), 0)")
End Sub
Private Function vbAppEvaluate(ByVal expression As String) As Variant ' variant of a function written by someone called Mathieu Guindon on Stack Exchange
Const Temp As String = "TempNameDeleteMe"
With Names.Add(Temp, expression)
vbAppEvaluate = Application.Evaluate("=" & Temp)
.Delete
End With
End Function
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: Application match doesn't work with long strings
Well, there's clearly an inconsistency of behaviour between versions. I knew about the 256 char limit on the MATCH function, but not the additional similar limits we seem to be encountering with the alternative solutions that ought to fix the problem. Unfortunately, I only have Excel 2021 on my PC these days, and it just as clearly does not have these limits, so I can't test properly.
Think I''ll have to bow out ...
Think I''ll have to bow out ...
-
- 4StarLounger
- Posts: 550
- Joined: 27 Jun 2021, 10:46
Re: Application match doesn't work with long strings
YasserKhalil , one quick question: how are you populating your variable?
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Application match doesn't work with long strings
Originally, I take the data from a text file and then store the data in an array, so the variables are stored in a variable.
Temporary I am populating the data into column A so as to be able to do operations on it
Temporary I am populating the data into column A so as to be able to do operations on it
-
- 4StarLounger
- Posts: 584
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Application match doesn't work with long strings
Hi Yasser, did you catch this workaround along the way...
https://eileenslounge.com/viewtopic.php ... 68#p303568
https://eileenslounge.com/viewtopic.php ... 68#p303568
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Application match doesn't work with long strings
In fact, I found using arrays is better to fast and easiest to control the flow of the code. So I took your advice and used arrays and inside the loop, I used Exit For when found a match. As for the post you referred, I got errors too with long strings.
-
- 4StarLounger
- Posts: 584
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Application match doesn't work with long strings
DocAElstein wrote: ↑22 Jan 2023, 11:06Hi Yasser, did you catch this workaround along the way...
https://eileenslounge.com/viewtopic.php ... 68#p303568
HiYasserKhalil wrote: ↑22 Jan 2023, 15:20As for the post you referred, I got errors too with long strings.
That reads a bit confusing. I referred to https://eileenslounge.com/viewtopic.php ... 68#p303568
I just wanted to be sure that you saw my new post with another new workaround..
Code: Select all
x = Application.Match(Left(s, 255), Evaluate("=IF({1},LEFT(A1:A9999, 255))"), 0)
Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Application match doesn't work with long strings
Yes, this worked for only the characters 255 not more than that. The problem is still there with the long strings (That's what I meant)
By the way, I have to match the whole string not part of it.
By the way, I have to match the whole string not part of it.
-
- 4StarLounger
- Posts: 584
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Application match doesn't work with long strings
OK :)
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(