Excel 2003中的VBA（条件格式）工作代码在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中不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！