2

In one of my macros, I have a piece of code (several actually, in different places) which deletes any special signs from a string and then compares the values in the array and looks for duplicates. It looks like this:

For m = LBound(tablica) To UBound(tablica)
    For i = LBound(tablica) To UBound(tablica)
        tab1 = Replace(Replace(Replace(Replace(LCase(tablica(i)), " ", ""), "™", ""), "®", ""), "©", "")
        tab1 = Application.WorksheetFunction.Clean(tab1)
        tab2 = Replace(Replace(Replace(Replace(LCase(tablica(m)), " ", ""), "™", ""), "®", ""), "©", "")
        tab2 = Application.WorksheetFunction.Clean(tab2)
        If tab1 = tab2 And i <> m Then
            MsgBox "Duplicated note"
            CheckDuplicates = True
            Exit Function
        End If
    Next
Next

However, the code does not see equal values, even if they are. Here's the example:

tab1 : "foruseonlyinareaswhereepafinaltier4/eustageivisrequired.turbocharged,chargeaircooledwetsleevecylinderlinersprogrammableauto-idleandauto-shutdownselectedidleadjustmentfrom900-1250rpmstart"

tab2 : "foruseonlyinareaswhereepafinaltier4/eustageivisrequired.turbocharged,chargeaircooledwetsleevecylinderlinersprogrammableauto-idleandauto-shutdownselectedidleadjustmentfrom900-1250rpmstart"

These two values are seen by VBA code as not equal. I tried to copy them to the worksheet and put a simple IF on them - it said that the values ARE actually equal. Both variables are declared as strings. Anyone has an idea of what might be wrong here?

EDIT: I tried to compare full strings - both give me Len = 854, I can't see any difference with bare eye. I trimmed and cleaned them, used StrComp, still the code tells me they are not equal. Here you can see both strings:

For use only in areas where EPA Final Tier 4/EU Stage IV is required. Turbocharged, Charge Air Cooled Wet Sleeve Cylinder Liners Programmable Auto-Idle and Auto-Shutdown Selected Idle Adjustment from 900-1250 RPM Starter Protection 4 Valves / Cylinder Cooled Exhaust Gas Recirculation Automatic Derating for Exceeded System Temperatures Electronically Controlled HPCR Fuel Delivery System, B20 Biodiesel Compatible Electrical Fuel Priming System Serpentine Drive Belt with Automatic Tensioner Under Hood Dual Element Air Cleaner with Restriction Indicator Under Hood Exhaust Filter and Catalysts with Curved Exhaust Stack Automatic Exhaust Filter Regeneration Dual-Stage Fuel Filter and Water Separator 500 Hour Vertical Spin-on Oil Filter Oil crankcase filter, Lifetime Engine Compartment Light Remote Jump Starting Lugs Automatic Engine Cool-down Timer

For use only in areas where EPA Final Tier 4/EU Stage IV is required. Turbocharged, Charge Air Cooled Wet Sleeve Cylinder Liners Programmable Auto-Idle and Auto-Shutdown Selected Idle Adjustment from 900-1250 RPM Starter Protection 4 Valves / Cylinder Cooled Exhaust Gas Recirculation Automatic Derating for Exceeded System Temperatures Electronically Controlled MEUI Fuel Delivery System, B20 Biodiesel Compatible Electrical Fuel Priming System Serpentine Drive Belt with Automatic Tensioner Under Hood Dual Element Air Cleaner with Restriction Indicator Under Hood Exhaust Filter and Catalysts with Curved Exhaust Stack Automatic Exhaust Filter Regeneration Dual-Stage Fuel Filter and Water Separator 500 Hour Vertical Spin-on Oil Filter Oil crankcase filter, Lifetime Engine Compartment Light Remote Jump Starting Lugs Automatic Engine Cool-down Timer
kshaq
  • 25
  • 6
  • Do your vba say that they are not equal even if you assign that directly to 2 different strings? Are you sure that i <> m ? – exSnake Apr 11 '17 at 09:20
  • Have you checked that `i<>m` (no offence, but that is the sort of thing I would miss having spent hours staring at the two strings). – SJR Apr 11 '17 at 09:20
  • @exSnake yup, that's exactly it. SJR of course :P in the table these values are on the 4th and 5th position. – kshaq Apr 11 '17 at 09:22
  • 1
    If I place those two strings into an array and run the code, it displays a `MsgBox` saying that they are duplicates. – YowE3K Apr 11 '17 at 09:23
  • Same for me as for @YowE3K - perhaps you have some hidden characters which are not picked up by Clean. – SJR Apr 11 '17 at 09:27
  • FWIW - Amongst other things to improve the efficiency of the code, you should change the inner loop to be `For i = LBound(tablica) To m - 1` - otherwise you are doing every comparison twice. (And then you could get rid of the `And i <> m`, because you wouldn't be checking a value against itself.) And move the calculation of `tab2` before the `For i` loop - it's only dependent on `m`, not `i`. – YowE3K Apr 11 '17 at 09:27
  • Any idea why the same code does not work for me like for you then? I tried StrComp and it gave me 1 too. – kshaq Apr 11 '17 at 09:30
  • Where do these strings come from? – SJR Apr 11 '17 at 09:34
  • Put your original strings into some text editor that shows hex values - there is probably a non-displayable character of some sort in it. – YowE3K Apr 11 '17 at 09:34
  • @SJR The code compares two reports, it's from one of them. Yeah, I thought about this, that's why I put the "Clean" function in there. Maybe that's not enough here. – kshaq Apr 11 '17 at 09:37
  • Your two strings ARE different. The first one has `HPCR Fuel Delivery` and the second one has `MEUI Fuel Delivery`. These are located somewhere in the middle. – A.S.H Apr 11 '17 at 11:08
  • Oh, I see now. The question is then - why the code does not see it? – kshaq Apr 11 '17 at 11:13
  • In your edit you said *"the code tells me they are not equal"* ??? Also my test using your code says they are not equal. What is the problem exactly? – A.S.H Apr 11 '17 at 11:15
  • Ok, I got confused right now because of it, sorry. I guess It's connected to the other place in the code. Is there a way to "suspend" a question until further issues here or just close it? I know it's a stupid question, but I just started to use the site. – kshaq Apr 11 '17 at 11:21
  • That's not a big deal, you can probably accept the answer of Ioannis as it can provide a good hint in some similar situations. That will close the issue. – A.S.H Apr 11 '17 at 11:27
  • 2
    Yeah, thanks. I guess the thing that got me confused was the value of each of the variables in the locals, as I didn't know that it does not show the full string and they seemed equal there. – kshaq Apr 11 '17 at 11:30

2 Answers2

1

The default text comparison in VBA is in Binary mode. This is typically not what we want, regardless of whether this causes your particular problem.

A better way to compare strings safely, use StrComp(str1, str2, vbTextCompare). If this gives 0, then the two strings are equal.

Some more details here.

Perhaps it is useful to first check if the strings are equal, and if they are not avoid their comparison altogether. Note that although a VBA string can contain about 2 billion Unicode characters, certain other limitations exist that are relevant (for example, a formula cannot exceed 255 characters, see also here).

I keep this answer here for future reference as it could be helpful to someone with a similar problem, although it seems it does not resolve the OP's problem.

Community
  • 1
  • 1
Ioannis
  • 5,238
  • 2
  • 19
  • 31
  • It gives 1 for me, no idea why, especially that the code works for @YowE3K up in the comments. – kshaq Apr 11 '17 at 09:29
  • 1
    @kshaq - the two strings you pasted into the question give a `0` for me when using `StrComp(tablica(1), tablica(2), vbTextCompare)`. It sounds as if the strings you have are not the same as the ones we get when we copy/paste them out of the question. – YowE3K Apr 11 '17 at 09:33
  • Well, actually I copied them straight from the locals window and pasted here. Maybe one of them contains some invisible signs, which VBA cannot get rid of? – kshaq Apr 11 '17 at 09:35
  • Can you try placing the `StrComp` right under their definition and see its result with debugging? – Ioannis Apr 11 '17 at 09:36
  • 1
    @kshaq Try executing `?Len("one of the strings")` in the Immediate Window and see whether it shows 186 - that's how many characters we are seeing – YowE3K Apr 11 '17 at 09:37
  • OP used `LCase(tablica(..))` before the comparison, so the `vbTextCompare` mode is not the answer. – A.S.H Apr 11 '17 at 09:40
  • @A.S.H it is `LCase` for both `tab1` and `tab2`. `Strcmp` does not solve the problem but it is better practice compare to using `=` for strings. – Ioannis Apr 11 '17 at 09:41
  • Yes, both had been `LCase` d before the comparison (in the OP's code), unless I missed something. – A.S.H Apr 11 '17 at 09:43
  • @kshaq - Wait - you said you copied them from the locals window - that usually truncates strings that are too long. It could be that it only shows the first 186 characters. (I can't remember the exact number.) In the immediate window, do a `?Mid(tab1, 1, 100)`, then a `?Mid(tab1, 101, 100)` then `?Mid(tab1, 201, 100)` etc up to `?Mid(tab1, 700, 100)`. Then do the same for tab2. – YowE3K Apr 11 '17 at 09:46
  • @kshaq Having 728 characters is certainly a problem. As YowE3K noticed, each string is 186 characters long. Can you see where this discrepancy comes from? – Ioannis Apr 11 '17 at 09:46
  • @kshaq - If the difference in the strings isn't obvious from the output I asked you to produce in the previous comment, paste those results into the question and hopefully someone can spot it. (I have to go to bed now, but hopefully someone else will be able to assist - you seem to have quite a few people intrigued with this question!) – YowE3K Apr 11 '17 at 09:51
  • Not at all at the moment. I'll try it out with Mid and then let you know if it gives me any decent results. – kshaq Apr 11 '17 at 09:51
  • @A.S.H I appreciate that the answer does not address the main problem, but I wouldn't say it is not useful (which is what downvoting is for). It suggests what is best practice when it comes to string comparison in VBA. Hence, I think downvoting it is a bit on the harsh side. – Ioannis Apr 11 '17 at 09:53
  • @Ioannis you have a point. The system wont let me undo the downvote until the question is edited (which I will do with pleasure). You may take this occasion to clarify that your answer is about good practice but not a direct solution to the question. :) – A.S.H Apr 11 '17 at 09:56
  • 1
    By the way, I dont think that a downvote is anything "harsh" or personal, it is just part of the game and should be taken with some fairplay. It concerns the relevance of the answer, not the poster of the answer. I also believe that good-practice recommendations are better suited for the comments section. – A.S.H Apr 11 '17 at 10:05
  • 1
    @A.S.H I can see where you are coming from, and it is a fair point indeed. I was too fast to answer in this case, and in hindsight I would had made a comment instead. Personally, I reserve downvotes for bad quality answers, such as those who fail beyond the specific example case, but of course this varies from user to user (without making it personal of course, as you mentioned) – Ioannis Apr 11 '17 at 10:11
  • I added full strings in the questions, however they give me same results. – kshaq Apr 11 '17 at 10:59
  • @kshaq Can you try the following: (i) define two byte arrays (ii) store the strings in those byte arrays (iii) loop through each character of the first byte array and compare it to the corresponding character of the second byte array (iv) if any pair is not equal, debug.print the pair index and the content of the first and the second array. You will find some help on how to do this [here](http://stackoverflow.com/a/30198059/2280567). – Ioannis Apr 11 '17 at 11:12
  • 1
    "Note that a VBA string has a max length of 255 characters" - the example you linked to is referring not to the maximum length of a string, but the maximum length of the string that can be passed in the `Procedure` parameter of `Application.Ontime`. A `String` variable is not itself limited to 255 characters, e.g. the OP has strings which have 854 characters in them. – YowE3K Apr 11 '17 at 16:48
  • @YowE3K Thanks for taking note of this - I edited it now so that it is correct. – Ioannis Apr 12 '17 at 06:46
0

Place the following two lines of code before you IF statement, run the code and analyse the result in the Immediate Window.

Debug.Print "Tab1 equals Tab2 = "; Tab1 = Tab2
Debug.Print "m = "; m, "i = "; i, "i <> m = "; i <> m

Check your declarations. Make sure that none of your variables are Variants. Both Tabs should be Strings and both m and I should be Longs or Integer. Do give preference to strComp(Tab1, Tab2, vbTextCompare) over the simple =. The equivalent for numbers is not to use Double type numbers because of their difficulty with zero.

Variatus
  • 14,293
  • 2
  • 14
  • 30
  • Declarations are fine, StrComp does not change the output unfortunately. – kshaq Apr 11 '17 at 10:58
  • The printed results of my test code above will pinpoint which of the two comparisons is different from the expected. If it turns out, as you suspect, that it is the string comparisons the next question will be the length of those strings: `Debug.Print Len(Tab1), Len(Tab2)` – Variatus Apr 11 '17 at 11:25