|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
前段时间刚好写了一个自定义函数,分享给你。
- Function EEVAL(ByVal s$) As Double
- Dim a$, b$, i%, oDom As Object, oWin As Object, strJS$
-
- Set oReg = CreateObject("vbscript.regexp")
-
- s = VBA.LCase(s)
- With oReg
- .Global = True
- .IgnoreCase = True
-
- a = "+-×÷(){}【】"
- b = "+-*/()[][]"
- For i = 1 To Len(a)
- s = Replace(s, Mid(a, i, 1), Mid(b, i, 1))
- Next
- .Pattern = "[^\w\+\-\*\/\(\)\.\^\[\]]" '去掉字母、数字、四则运算和计算用到的符号外的字符
- s = .Replace(s, "")
-
- .Pattern = "(\w+(?:\([^\(\)]*\))?)\^(\w+(?:\([^\(\)]*\))?)"
- s = .Replace(s, "pow($1,$2)") '支持幂运算符
- .Pattern = "\b(sqrt|round|random|pow(er)?|PI|min|max|log|Int|floor|exp|E|ceil|abs|a?tan2?|a?sin|a?cos)\b"
- s = .Replace(LCase(s), " Math.$1") '支持基本数学函数和两个重要常数(圆周率和自然常数)
-
- .Pattern = "(\d)\.(\d)" '把数字小数点替换成@
- s = .Replace(s, "$1@$2")
- .Pattern = "(?!=Math\.)[a-zA-Z \.]+(?=[\+\-\*\/\d ]|$)" '去掉前面没有Math.的英文字符
- s = .Replace(s, "")
- .Pattern = "^[\+\-\*\/]+|[\+\-\*\/]+$" '删除计算式前后的四则运算符号
- s = .Replace(s, "")
- .Pattern = "([\+\-\*\/])[\+\-\*\/]+" '如存在多个四则运算符号在一起,则仅保留第一个
- s = .Replace(s, "$1")
-
- '-------------- '如果[]()前为数字或),即非四则运算符号,则忽略[]()中的内容
- s = Replace(s, "(", "[")
- s = Replace(s, ")", "]")
- .Pattern = "(\d|\])\[[^\]]*\]"
- s = .Replace(s, "$1")
- s = Replace(s, "[", "(")
- s = Replace(s, "]", ")")
- '---------------
-
- ' .Pattern = "\[[^\[\]]*\]|(Math\.)(?=\1)"
- ' s = .Replace(s, "")
- .Pattern = "\b(Math\.)?Int"
- s = .Replace(s, "parseInt") '支持Int函数
- .Pattern = "pow(er)?"
- s = .Replace(s, "pow")
- .Pattern = "Math.e\b"
- s = .Replace(s, "Math.E")
- .Pattern = "\.pi\(\)"
- s = .Replace(s, ".PI")
- End With
-
- s = Replace(s, "@", ".") '把@重新替换成数字小数点
- Set oDom = CreateObject("htmlfile")
- Set oWin = oDom.parentWindow
- oDom.write Replace("<script Language = JavaScript> function eEvaluate(){return eexp} </script>", "eexp", s)
- EEVAL = oWin.eval("eEvaluate()")
- End Function
复制代码 |
评分
-
1
查看全部评分
-
|