依据《国家发展计划委员会文件计价格［2002］1980号》计算招标代理费的自定义函数
Option ExplicitFunction 招标代理费(服务类型 As String, 中标金额 As Double) As DoubleDim brrIf 服务类型 = "货物" Then '货物brr = [{1.50,1.10,0.80,0.50,0.25,0.05,0.01}/100]ElseIf 服务类型 = "服务" Then '服务brr = [{1.50,0.80,0.45,0.25,0.10,0.05,0.01}/100]ElseIf 服务类型 = "工程" Then '工程brr = [{1.00,0.70,0.55,0.35,0.20,0.05,0.01}/100]Else招标代理费 = "error"Exit FunctionEnd IfIf 中标金额 >= 0 And 中标金额 <= 1000000 Then招标代理费 = 中标金额 * brr(1)Exit FunctionElseIf 中标金额 > 1000000 And 中标金额 <= 5000000 Then招标代理费 = 1000000 * brr(1) + (中标金额 - 1000000) * brr(2)Exit FunctionElseIf 中标金额 > 5000000 And 中标金额 <= 10000000 Then招标代理费 = 1000000 * brr(1) + 4000000 * brr(2) + (中标金额 - 5000000) * brr(3)Exit FunctionElseIf 中标金额 > 10000000 And 中标金额 <= 50000000 Then招标代理费 = 1000000 * brr(1) + 4000000 * brr(2) + 5000000 * brr(3) + (中标金额 - 10000000) * brr(4)Exit FunctionElseIf 中标金额 > 50000000 And 中标金额 <= 100000000 Then招标代理费 = 1000000 * brr(1) + 4000000 * brr(2) + 5000000 * brr(3) + 40000000 * brr(4) + (中标金额 - 50000000) * brr(5)Exit FunctionElseIf 中标金额 > 100000000 And 中标金额 <= 1000000000 Then招标代理费 = 1000000 * brr(1) + 4000000 * brr(2) + 5000000 * brr(3) + 40000000 * brr(4) + 50000000 * brr(5) + (中标金额 - 100000000) * brr(6)Exit FunctionElseIf 中标金额 > 1000000000 Then招标代理费 = 1000000 * brr(1) + 4000000 * brr(2) + 5000000 * brr(3) + 40000000 * brr(4) + 50000000 * brr(5) + 100000000 * brr(6) + (中标金额 - 1000000000) * brr(7)Else招标代理费 = "error"Exit FunctionEnd IfEnd Function
以下为给函数添加帮助信息的代码
'此过程用户注册自定义函数,给自定义函数添加帮助，使用前先运行一次。Sub 需要运行一次()Dim 函数名称 As String '函数名称Dim 函数描述 As String '函数描述Dim 函数类别 As Integer '函数类别Dim 参数个数(1)'函数参数描述 数组 个数函数名称 = "招标代理费"函数描述 = "国家发展计划委员会文件计价格［2002］1980号"函数类别 = 14参数个数(0) = "货物、服务或工程"参数个数(1) = " "Call Application.MacroOptions(Macro:=函数名称, Description:=函数描述, Category:=函数类别, ArgumentDescriptions:=参数个数)End Sub
以上是代码，把excel文件另存为.xlam格式，再加一个customui就基本完美了。
