Excel 2003中的VBA（条件格式）工作代码在Excel 2020中不工作 [英] VBA (Conditional Formatting) working code in Excel 2003, does not work in Excel 2020

问题描述

用于将条件格式应用于范围的基本代码，当range.value小于7,2时将保持白色，而当大于8,1时将变为红色。
此代码在我的Excel 2003 Macro-Enabled Workbook文档中运行得很好，但是当我弟弟用Excel 2020在他的工作计算机中打开它时，会引发此错误

Basic code for applying a conditional format to a range, when range.value is lower than 7,2 it stays white, when higher than 8,1 it turns red. This code runs just fine in my Excel 2003 Macro-Enabled Workbook document, but when I have my brother open it in his work computer with Excel 2020, It throws this error

运行时错误'5'：
无效的过程调用或参数

Run-time error '5': Invalid procedure call or argument

Private Sub totalEPS(mySelection As Range)
    With mySelection.FormatConditions
        .Delete
        With .Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=7,2")
            .Interior.Color = 65535
            .StopIfTrue = False
        End With
        With .Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=8,1")
            .Interior.Color = 255
            .StopIfTrue = False
        End With
    End With
End Sub

当他点击Debug时，它会在行中停止

When he hits Debug it stops in the line

        With .Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=7,2")

I无法调试它，因为它不会在我的计算机上引发任何错误，只能在他的计算机上。

I cannot debug it because it does not throw any error in my computer, only on his.

代码使用该方法的次数超过50次，在我的计算机上运行了50次笔记本电脑，并在第一次运行时崩溃。
老实说我不知道​​出什么问题。

The code uses the method over 50 times, it runs just fine 50 times in my laptop, and crashes in the first run on his. I honestly don't know what's wrong.

推荐答案

我怀疑这与小数点分隔符有关-

I suspect it has to do with the decimal separator - that comma would be a dot on an en-US operating system.

尝试将此功能添加到模块中：

Try adding this function to your module:

Public Function LocalizeDecimal(ByVal value As Double) As String
    LocalizeDecimal = Replace(Str(value), ".", Application.International(xlDecimalSeparator))
End Function

然后将 Formula1 参数编辑为以下内容：

Then edit the Formula1 argument to something like this:

With .Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=" & LocalizeDecimal(7.2))

和：

With .Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=" & LocalizeDecimal(8.1))

这篇关于Excel 2003中的VBA（条件格式）工作代码在Excel 2020中不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！