在VBA中,我尝试制作如下函数:
Function DoMath(x as long)
static command As Object
static param As Object
if command is nothing then
Set command = CreateObject("ADODB.Command")
Set param = command.CreateParameter(, 3)
command.CommandText = "?+10"
command.Parameters.Append param
command.prepared = True
end if
'Set param value and evaluate:
param.Value = x
DoMath = command.Execute()
End Function
不幸的是,执行此功能时报告:
运行时错误"3709";
该连接无法用于执行此操作。要么在此上下文中关闭或无效。
这让我相信你需要一个连接。但即使创建了一个到空白数据库的连接:
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/temp/tbd.mdb"
...
DoMath = command.Execute(conn)
这似乎并不能解决错误。
有什么想法可以让这个功能发挥作用吗?或者我想做的事情是不可能的?
感谢Brian M Stafford如何建立活动连接。我已经改进了该功能,使其不需要MDB文件。
Function DoMath(ByVal x As Long) As Long
Static command As Object
Static param As Object
If command Is Nothing Then
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:temp;Extended Properties = ""text;HDR=Yes;FMT=Delimited"""
conn.Open
Set command = CreateObject("ADODB.Command")
Set command.ActiveConnection = conn
Set param = command.CreateParameter(, ADODBDataTypes.adInteger)
command.CommandText = "SELECT ?+10"
command.Parameters.Append param
command.prepared = True
End If
'Set param value and evaluate:
param.Value = x
DoMath = command.Execute()(0).Value
End Function
编辑:
所以我把它做成了一个类,至少使用后期绑定函数,它并不比直接使用Application.Evaluate
好。知道有用,但也不幸。