What's equivalent to Exact function in VBA
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
What's equivalent to Exact function in VBA
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
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
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: What's equivalent to Exact function in VBA
What do you mean by "I don't need to use EQUAL sign"?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: What's equivalent to Exact function in VBA
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 ..
But in fact one of the strings has a hidden characters and only the exact function reveals that the two strings are different ..
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: What's equivalent to Exact function in VBA
I used this line selecting A1
and when typing "Yasser" in B1 ..
and when i use the equal sign
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 ...?
Code: Select all
ActiveCell = "Yasser" & ChrW(8207)
and when i use the equal sign
Code: Select all
=B1=A1
May be the language is the cause I don't know
Generally is there away to use the EXACT function ...?
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: What's equivalent to Exact function in VBA
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...
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...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: What's equivalent to Exact function in VBA
If so why is EXACT function is created .. if the equal sign is the same for comparison ??
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: What's equivalent to Exact function in VBA
You'd have to ask Microsoft. Perhaps it's for users who find a formula such as =A1=B1 confusing.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: What's equivalent to Exact function in VBA
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
Thanks a lot for great help
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: What's equivalent to Exact function in VBA
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;
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
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;
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
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 30 Dec 2019, 13:22, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: What's equivalent to Exact function in VBA
Thanks a lot Mr. Alan for the explanation and it is solved using the LEN function as you suggested.
Thank you very much.
Thank you very much.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: What's equivalent to Exact function in VBA
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.
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.
Regards,
Rory
Rory
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Unics
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:
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;
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
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;
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: What's equivalent to Exact function in VBA
There's no difference between Chr and ChrW (or Asc and AscW) for characters up to 255.
Regards,
Rory
Rory