VBA - 代码不会将变量视为相等，即使它们是

Question

在我的一个宏中，我有一段代码(实际上有几个，在不同的地方)，它从字符串中删除任何特殊符号，然后比较数组中的值并查找重复项。它看起来像这样:

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

但是，代码看不到相等的值，即使它们是相等的。这是示例:

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

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

VBA 代码认为这两个值不相等。我试图将它们复制到工作表并在它们上面放一个简单的 IF - 它说这些值实际上是相等的。两个变量都被声明为字符串。任何人都知道这里可能出了什么问题？

编辑:
我试图比较完整的字符串 - 两者都给我 Len = 854，我肉眼看不出任何区别。我修剪并清理了它们，使用了 StrComp，但代码仍然告诉我它们不相等。在这里你可以看到两个字符串:

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

Answer 1

VBA 中的默认文本比较是二进制模式。这通常不是我们想要的，无论这是否会导致您的特定问题。

安全比较字符串的更好方法，使用 StrComp(str1, str2, vbTextCompare) .如果这给出 0 , 那么这两个字符串相等。

更多细节here .

首先检查字符串是否相等，如果它们不完全避免它们的比较，也许很有用。请注意，尽管 VBA 字符串可以包含大约 20 亿个 Unicode 字符，但存在某些相关的其他限制(例如，公式不能超过 255 个字符，另见 here)。

我将这个答案保留在这里以供将来引用，因为它可能对有类似问题的人有所帮助，尽管它似乎不能解决 OP 的问题。