What's equivalent to Exact function in VBA

[YasserKhalil]

Hello everyone

There is a worksheet function which is EXACT in worksheet. What's the equivalent in VBA? as I don't need to use EQUAL sign to compare two strings ..
Thanks advanced for help

[HansV]

What do you mean by "I don't need to use EQUAL sign"?

[YasserKhalil]

The two strings that I need to compare appears to be equal if I use Equal sign in comparing ...
But in fact one of the strings has a hidden characters and only the exact function reveals that the two strings are different ..

[HansV]

Please provide an example.

[YasserKhalil]

I used this line selecting A1

Code: Select all

ActiveCell = "Yasser" & ChrW(8207)

and when typing "Yasser" in B1 ..
and when i use the equal sign

Code: Select all

=B1=A1

That's too weird for me to get False as when testing on the Arabic strings I got True (although the strings are really different)
May be the language is the cause I don't know

Generally is there away to use the EXACT function ...?

[HansV]

Apparently, I don't understand the problem. Using your example:

The formula =A1=B1 returns FALSE
The formula =EXACT(A1,B1) returns FALSE
The VBA line Debug.Print Range("A1").Value = Range("B1").Value returns False.

So the result is the same...

[YasserKhalil]

If so why is EXACT function is created .. if the equal sign is the same for comparison ??

[HansV]

You'd have to ask Microsoft. Perhaps it's for users who find a formula such as =A1=B1 confusing.

[YasserKhalil]

The real problem with me happens only with the Arabic strings and that confused me a lot and only EXACT function solves that problem
Thanks a lot for great help

[Doc.AElstein]

Hello,
I get the same results as Hans, using your example.
For me all results are False

But you can also always check the length of the string ( Len( ) ) in comparisons.
Checking the length of a string can be helpful when examining and comparing strings.
( Also you can check what is in your string with a function, like WtchaGot )
YasserString.JPG : https://imgur.com/SGhZhK3" onclick="window.open(this.href);return false;

YasserString.JPG

Code: Select all

Sub test()
Rem 1
 Range("A1").Value = "Yasser" & ChrW(8207)
 Range("B1").Value = "Yasser"
Debug.Print Len(Range("A1").Value) & " " & Len(Range("B1").Value) & "   "; Range("A1").Value = Range("B1").Value
' result is   7 6   Falsch

Rem 2
Dim strA1 As String, strB1 As String
 Let strA1 = "Yasser" & ChrW(8207): strB1 = "Yasser"
Debug.Print Len(strA1) & " " & Len(strB1) & "   "; strA1 = strB1
' result is   7 6   Falsch

Rem 3
 Call WtchaGot("Yasser" & ChrW(8207))
' result is    "Yasser" & Chr(63)
End Sub

Alan

P.s. One possible explanation for strange results sometimes may be…
If you have like
Yasser & ArabicCharacter (1234)
And
Yasser & ArabicCharacter (6789)

For me in VBA, VBA is confused and it sees instead
ArabicCharacter (1234) = ? = Chr(63)
ArabicCharacter (6789) = ? = Chr(63)


So in Excel I have
Yasser & ArabicCharacter (1234) = Yasser & ArabicCharacter (6789) = False
Then but sometimes in VBA I may have for the same strings
Yasser & Chr(63) = Yasser & Chr(63) = True


But that is just a wild guess

[YasserKhalil]

Thanks a lot Mr. Alan for the explanation and it is solved using the LEN function as you suggested.
Thank you very much.

[rory]

Alan,

Your function can't handle unicode since you are using Asc rather than AscW. That's why you end up with ? for characters beyond the 255 ascii ones.

[Doc.AElstein]

Thanks for that, Mr Rory. I seem to have totally missed that, probably as I had no idea about ascii and unics

If I am not mistaken , there seems to be a .AscW Method , so simply replacing my Asc( .. )s with AscW( .. )s is mainly all I need to do…
But I have done my latest Function version a bit differently, .. I use .AscW to find out the number , but then
If it is up to 255 , I give the result as Chr( … ) ,
Else I give the result as ChrW( .. ).

Sort of like this bit i did do in my latest function which I just did done:

Code: Select all

                If AscW(Caracter) < 256 Then
                 Let WotchaGot = WotchaGot & "Chr(" & AscW(Caracter) & ")" & " & "
                Else
                 Let WotchaGot = WotchaGot & "ChrW(" & AscW(Caracter) & ")" & " & "
                End If

I don’t know yet for sure of any good reason why I should want to do that. Maybe it might help to show when “plain text” is not being used.


Latest Function ( also in .txt file attached ) : https://tinyurl.com/reu82hc" onclick="window.open(this.href);return false; , https://tinyurl.com/vuftsgp" onclick="window.open(this.href);return false;

Alan

P.S:
I suppose if I have been given a final string, built by someone using Chr( .. ) or ChrW( .. ), then, for characters 0 - 255 , I would not be able to tell whether Chr( .. ) or ChrW( .. ) had been used for those characters, 0 - 255 ???



Refs
https://www.joelonsoftware.com/2003/10/ ... o-excuses/" onclick="window.open(this.href);return false;
http://www.excelfox.com/forum/showthrea ... f-a-string" onclick="window.open(this.href);return false;

[rory]

There's no difference between Chr and ChrW (or Asc and AscW) for characters up to 255.