Application match doesn't work with long strings

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Application match doesn't work with long strings

Post by rory »

Evaluate is limited to 255 characters. Given the length of your string variable, I do not think it will work for you.
Regards,
Rory

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Application match doesn't work with long strings

Post by SpeakEasy »

Then it would be useful if you could answer my question related to the

Code: Select all

x = Application.Evaluate("=Match(True, Index(A:A=""" & s & """, 0), 0)")
solution proposed above

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Application match doesn't work with long strings

Post by YasserKhalil »

This doesn't work with the variable s. It only works if the value in a cell and I refer to that cell.

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Application match doesn't work with long strings

Post by DocAElstein »

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

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
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, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Application match doesn't work with long strings

Post by DocAElstein »

Hi
adeel1 wrote:
20 Jan 2023, 10:19
Hi, sorry for interruption .......what is advantage of using index
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, :(

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Application match doesn't work with long strings

Post by SpeakEasy »

YasserKhalil wrote:
21 Jan 2023, 16:08
This doesn't work with the variable s. It only works if the value in a cell and I refer to that cell.
Yes, already know that is what you are experiencing. My question was: what error are you getting?

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
Then, instead of

Code: Select all

x = Application.Evaluate("=Match(True, Index(A:A=""" & s & """, 0), 0)")
use

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

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Application match doesn't work with long strings

Post by SpeakEasy »

DocAElstein wrote:
21 Jan 2023, 17:33
I suspect the results got on the next demo coding may be similar for newer Excel versions, but I am not sure
Yep, same on Excel 2021. Not surprisingly

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Application match doesn't work with long strings

Post by YasserKhalil »

Thank you very much for your great efforts.
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
But I encountered an error [Application-defined or object-defined error] at this line

Code: Select all

With Names.Add(Temp, expression)

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Application match doesn't work with long strings

Post by DocAElstein »

SpeakEasy wrote:
21 Jan 2023, 19:56
Yep, same on Excel 2021. Not surprisingly
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)
ErrorForLengthMoreThan255.JPG

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
VBA Evaluate Range 2023.xls https://app.box.com/s/grvuuvvk9w7vb2hz85ecrkr0t8106i3a
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, :(

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Application match doesn't work with long strings

Post by SpeakEasy »

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

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Application match doesn't work with long strings

Post by SpeakEasy »

YasserKhalil , one quick question: how are you populating your variable?

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Application match doesn't work with long strings

Post by YasserKhalil »

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

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Application match doesn't work with long strings

Post by DocAElstein »

Hi Yasser, did you catch this workaround along the way...
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, :(

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Application match doesn't work with long strings

Post by YasserKhalil »

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.

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Application match doesn't work with long strings

Post by DocAElstein »

DocAElstein wrote:
22 Jan 2023, 11:06
Hi Yasser, did you catch this workaround along the way...
https://eileenslounge.com/viewtopic.php ... 68#p303568
YasserKhalil wrote:
22 Jan 2023, 15:20
As for the post you referred, I got errors too with long strings.
Hi
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..
Image

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, :(

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Application match doesn't work with long strings

Post by YasserKhalil »

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.

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Application match doesn't work with long strings

Post by DocAElstein »

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, :(