造价盒子V1.1.2版本推出之后,其中的算量助手,因其强大便捷的公式录入与计算功能,广受好评。
但是也有用户很快跟我反馈,当计算公式字符长度超过255个字符时,计算结果就会出错。
这是因为V1.1.2版本中,造价盒子是利用宏表函数evaluate计算的。evaluate使用方便,但是当计算文本长度超过255字符,就无法计算。而在造价工作中,手工算量计算公式超过255字符的情况几乎比比皆是,比如某位仁兄发的这张照片,amazing到了我:
如果你自己懂VBA,可以用以下代码自己创建此函数,如果不懂,那就用造价盒子帮你一键生成吧。
Function 计算公式(ByVal rg As Range) As Single Dim jss$ Dim reg As Object Dim oSC As Object jss = rg.Value '去除[]及其以内文本 Set reg = CreateObject("vbscript.regexp") With reg .Global = True .Pattern = "\[.*?(?=\])." jss = .Replace(jss, "") End With '64位office创建ScriptControl对象 Set oSC = CreateObjectx86("MSScriptControl.ScriptControl") With oSC .Language = "vbscript" 计算公式 = .Eval(jss) End With CreateObjectx86 , TrueEnd FunctionFunction CreateObjectx86(Optional sProgID, Optional bClose = False) Static oWnd As Object Dim bRunning As Boolean #If Win64 Then bRunning = InStr(TypeName(oWnd), "HTMLWindow") > 0 If bClose Then If bRunning Then oWnd.Close Exit Function End If If Not bRunning Then Set oWnd = CreateWindow() oWnd.execScript "Function CreateObjectx86(sProgID): Set CreateObjectx86 = CreateObject(sProgID): End Function", "VBScript" End If Set CreateObjectx86 = oWnd.CreateObjectx86(sProgID) #Else Set CreateObjectx86 = CreateObject("MSScriptControl.ScriptControl") #End IfEnd FunctionFunction CreateWindow() Dim sSignature, oShellWnd, oProc On Error Resume Next sSignature = Left(CreateObject("Scriptlet.TypeLib").GUID, 38) CreateObject("WScript.Shell").Run "%systemroot%\syswow64\mshta.exe about:""about:"", 0, False Do For Each oShellWnd In CreateObject("Shell.Application").Windows Set CreateWindow = oShellWnd.GetProperty(sSignature) If Err.Number = 0 Then Exit Function Err.Clear Next LoopEnd Function