关于Excel自定义函数,求高手帮忙!
我希望定义一个自定义函数,通过传入物料编号,求当时的库存.目前的问题在于,如果函数内部进行数据库连接,效率太低,非常低,能否实现将数据库的Connection当参数传入函数,实现高效率的数据取数?
还请高人帮忙。
原函数如下:
Public Function sqlfun(sqlnum As String)
Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset, sqlStr As String
sqlStr = "select sum(t0.onhand) as onhand from oitw t0 "
sqlStr = sqlStr + "join oitm t1 on t0.itemcode = t1.itemcode "
sqlStr = sqlStr + " where left(t0.whscode,1)='f' and t0.whscode<>'f1008' and t0.whscode<>'f1009' and t0.whscode<>'f1010' and t0.itemcode = '" & sqlnum & "'"
sqlStr = sqlStr + " group by t0.itemcode"
With cnn
.ConnectionString = "Provider=SQLOLEDB.1; Data Source=localhost; Database=mgi2008; User ID=sa; PWD=12345678"
.CommandTimeout = 120
.Open
End With
With rst
.ActiveConnection = cnn
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = sqlStr
.Open
End With
If rst.EOF Then
sqlfun = 0
Else
sqlfun = rst.Fields(0)
End If
rst.Close: Set rst = Nothing: cnn.Close: Set cnn = Nothing
End Function
希望改成,
Public Function sqlfun(dbcon as adodb.connection sqlnum As String)
如果改成这样,用户如何调用??
--------------------编程问答-------------------- 把:
Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
声明成模块级的变量。
启动时打开数据库,中间过程中直接使用,结束运行时关闭。
================
友情Up..............
--------------------编程问答-------------------- 没看出这样后效率有什么提高的 --------------------编程问答-------------------- 用私有变量把Connection单独放在一个模块中,然后定义一个公有属性,判断这个变量打开没,打开了就直接返回,没有打开就打开它。这样肯定效率高些。 --------------------编程问答-------------------- 对于这样的问题,建议写成三个函数
OpenDb ----程序运行时连接数据库
ExecSql ----每次执行Sql语句前,先判断数据库是否连接
CloseDb ----程序退出时断开数据库连接
补充:VB , VBA