在Excel中，有一个Evaluate方法，来获得给定运算式的结果，我们常常在VBA是借用它，但使用Evaluate方法时，给定的字符串如果超过255个字符，则会提示类型不匹配，如：

Sub text1()
    Dim x As String
    x = "1+2+3+4+5"
    MsgBox Application.Evaluate(x)
    x = "((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+4*(0.0+0.0)+3*0.0+0.0+(2.4+3.9+2.8+1.3)+2+5+5*2+2+5+5*2-((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+4*(0.0+0.0)+3*0.0+0.0+(2.4+3.9+2.8+1.3)+2+5+5*2+2+5+5*2* ((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+4*(0.0+0.0)+3*0.0+0.0+(2.4+3.9+2.8+1.3)+2+5+5*2+2+5+5*2"
    MsgBox Application.Evaluate(x)
End Sub

这时我们可以借用Eval函数来获得上面的结果。

Function Autocal1(Cell)
With CreateObject("MSScriptControl.ScriptControl")
.Language = "vbscript"
Autocal1 = .Eval(Cell)
End With
End Function

Function Autocal2(Cell)
With CreateObject("Access.Application")
Autocal2 = .Eval(Cell)
End With
End Function

Sub test()
    Dim x As String
    x = "((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+4*(0.0+0.0)+3*0.0+0.0+(2.4+3.9+2.8+1.3)+2+5+5*2+2+5+5*2-((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+4*(0.0+0.0)+3*0.0+0.0+(2.4+3.9+2.8+1.3)+2+5+5*2+2+5+5*2* ((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+((2.4+3.9+2.8+1.3)+(4.7+2.8)+(6.3+0.7+9.1+5.1+3.2+0.4+4.2+0.6))*0.5+0.0+2*0.0+4*(0.0+0.0)+3*0.0+0.0+4*(0.0+0.0)+3*0.0+0.0+(2.4+3.9+2.8+1.3)+2+5+5*2+2+5+5*2"
    MsgBox Autocal1(x)
    MsgBox Autocal2(x)
    [A1] = x
    MsgBox Autocal1([A1])
    MsgBox Autocal2([A1])
End Sub

上面两个自定义函数，还可以在工作表中直接使用，如：

=Autocal1(A1)
=Autocal2(A1)