带有变量运算符的 Excel VBA 数学类型公式不适用于十进制数

Question

我有一个小脚本，旨在执行简单的数学运算，其中运算符被定义为变量。唯一的问题是它仅适用于整数，而不适用于十进制数。有人知道为什么吗？我试图将运算符和数字分成两个变量，但问题仍然存在。

Sub CellMath2()
Call CreateSheetBackup
Dim rng As Range
Dim expression As Variant
expression = InputBox("Enter operator (* / + - ^) and number (e.g. ""*1000"").", "Operator and number")
If StrPtr(expression) = 0 Then Exit Sub
For Each rng In Selection
If WorksheetFunction.IsNumber(rng) Then
    rng.Value = "=" & rng & expression
    rng.Value = rng.Value
Else
End If
Next rng
End Sub

Answer 1

您正在寻找的是Evaluate方法。

用法如下：

For Each rng In Selection
    If WorksheetFunction.IsNumber(rng) Then
        rng.Value = Evaluate(Replace(rng & expression, ",", "."))
    End If
Next rng

Evaluate接受与公式相同的运算符样式，至少在很大程度上，所以如果你有值为 10 和 expression="+10" 的A1，那么Evaluate(Range("A1") & expression会给你20。

Replace(rng, ",", ".")从欧洲小数转换为美国标准(VBA 使用(。

Answer 2

尝试在输入框后使用以下行：

If Application.ThousandsSeparator = " " Then expression = Replace(expression, " ", "")
If Application.DecimalSeparator = "," Then expression = Replace(expression, ",", ".")

Answer 3

我可以将运算符和数字分开并使用 case select 语句，但是我必须包含所有可能的运算符并将功能限制为我包含的运算符。

这有效，输入框和单元格中的十进制逗号：

Sub CellMath()
Dim rng As Range
Dim c As Variant, operator As Variant
operator = InputBox("Enter operator (* / + - ^ or e).", "Operator")
If StrPtr(operator) = 0 Then Exit Sub
c = InputBox("Enter number.", "Number")
If StrPtr(c) = 0 Then Exit Sub
For Each rng In Selection
If WorksheetFunction.IsNumber(rng) Then
Select Case operator
    Case "*"
    rng.Value = rng * c
    Case "/"
    rng.Value = rng / c
    Case "+"
    rng.Value = rng + c
    Case "-"
    rng.Value = rng - c
    Case "^"
    rng.Value = rng ^ c
    Case "e"
    rng.Value = rng * 10 ^ c
End Select
Else
End If
Next rng
End Sub

我在这里忘记了什么有趣的运算符？