自定义函数 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

不需要使用函数，DataColChanged事件

'''

Select Case e.DataCol.Name

Case "资产期初数","资产期末数"

Dim str As String = "货币资金,短期投资,财政应返还额度,应收在院病人医疗款,应收医疗款,其他应收款,预付账款,待摊费用,存货,一年内到期的长期债权投资"

Dim str2 As String = "长期资产,固定资产原价,在建工程,固定资产清理,无形资产原价,长期待摊费用,待处理财产损溢"

Dim dict As new Dictionary(of String,String)

dict.Add("资产项目 = '流动资产合计'",str)

dict.Add("资产项目 = '非流动资产合计'",str2)

Dim dictB As new Dictionary(of String,String)

dictB.Add("资产项目 = '流动资产合计'","减：坏账准备")

dictB.Add("资产项目 = '非流动资产合计'","减：累计折旧,减：累计摊销")

For Each key As String In dict.Keys

Dim value As String = dict(key)

If value.Contains(e.DataRow("资产项目")) Then

Dim dr As DataRow = e.DataTable.Find(key)

If dr IsNot Nothing Then

dr(e.DataCol.Name) = e.DataTable.Compute("sum(" & e.DataCol.Name & ")","资产项目 in ('" & value.replace(",","','") & "')") - e.DataTable.Compute("sum(" & e.DataCol.Name & ")","资产项目 in ('" & dictB(key).replace(",","','") & "')")

End If

Exit For

End If

Dim dr2 As DataRow = e.DataTable.Find("资产项目 = '资产总计'")

If dr2 IsNot Nothing Then

dr2(e.DataCol.Name) = e.DataTable.Compute("sum(" & e.DataCol.Name & ")","资产项目 in ('流动资产合计','非流动资产合计')")

End If

Case "负债期初数","负债期末数"

Dim str3 As String = "短期借款,应缴款项,应付piao据,应付账款,预收医疗款,应付职工薪酬,应付福利费,应付社会保障费,应交税费,其他应付款,预提费用,一年内到期的长期负债"

Dim str4 As String = "长期借款,长期应付款"

Dim str5 As String = "事业基金,专用基金,待冲基金,财政补助结转（余）,科教项目结转（余）,本期结余,未弥补亏损"

Dim dict As new Dictionary(of String,String)

dict.Add("负债及所有者权益 = '流动负债合计'",str3)

dict.Add("负债及所有者权益 = '非流动负债合计'",str4)

dict.Add("负债及所有者权益 = '净资产合计'",str5)

For Each key As String In dict.Keys

Dim value As String = dict(key)

If value.Contains(e.DataRow("负债及所有者权益")) Then

Dim dr As DataRow = e.DataTable.Find(key)

If dr IsNot Nothing Then

dr(e.DataCol.Name) = e.DataTable.Compute("sum(" & e.DataCol.Name & ")","负债及所有者权益 in ('" & value.replace(",","','") & "')")

End If

Exit For

End If

Dim dr2 As DataRow = e.DataTable.Find("负债及所有者权益 = '负债和净资产总计'")

If dr2 IsNot Nothing Then

dr2(e.DataCol.Name) = e.DataTable.Compute("sum(" & e.DataCol.Name & ")","负债及所有者权益 in ('流动负债合计','非流动负债合计','净资产合计')")

End If

End Select

主题：自定义函数