Problem with VBA function

[RadekLodz]

Hi everybody,
I have problem with my function. Previously code:

Public Function Wylicz(addressCell As String, cityCell As String, startDate As String, endDate As String)

Dim address As String
Dim city As String

address = Replace(addressCell, " ", "%")
city = Replace(cityCell, " ", "%")

Dim objRec As ADODB.Recordset
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim ConnectionString As String
Dim cmdString As String

Set objRec = New ADODB.Recordset
Set objConn = New ADODB.Connection
Set objCmd = New ADODB.Command

ConnectionString = "Provider=SQLOLEDB;Data source=Server;Initial catalog=Database;User ID=sa;Password=Password"
objConn.Open ConnectionString

cmdString = "DECLARE @table AS TGroups"

objCmd.CommandText = cmdString
objCmd.Execute , , adExecuteNoRecords
MsgBox ("Declare")

cmdString = "INSERT INTO @table (groupName) VALUES ('Group 1')"
objCmd.CommandText = cmdString
objCmd.Execute , , adExecuteNoRecords
MsgBox ("1 INSERT")

cmdString = "INSERT INTO @table (groupName) VALUES ('Group 2')"
objCmd.CommandText = cmdString
objCmd.Execute , , adExecuteNoRecords
MsgBox ("2 INSERT")

cmdString = "INSERT INTO @table (groupName) VALUES ('Group 3')"
objCmd.CommandText = cmdString
objCmd.Execute , , adExecuteNoRecords
MsgBox ("3 INSERT")

cmdString = "EXEC dbo.SprzedazPoAdresieDostawy @groups = @table, @address = address, @city = city, @startDate = startDate, @endDate = endDate"
MsgBox (cmdString)
Set objRec = objConn.Execute(cmdString)

If objRec.EOF = True Then
    Wylicz = 0
    'MsgBox (0)
Else
    Wylicz = objRec!ACTINDX
    'MsgBox (objRec!ACTINDX)
End If
objConn.Close
End Function

After run this function in Excel sheet, a received error value #ARG!. Only first MsgBox is displayed, and function stop run, without any errors or something else. What is wrong with it?

Best Regards

 

[Dan_W]

Have you set a reference to the ADODB type library in the workbook?
Separately, is this something that you really need to be a function and not a macro subroutine that you run on-demand? I ask because I wonder if there might be an issue about making so many calls to the databse if you're using this function from the worksheet from multiple cells... I might not be problematic, but just a thought.

 

[RadekLodz]

Yes. I set a reference to ActiveX Data Objects and ActiveX Data Objects Recordset.
An yes, i need a function, because stored procedure get data from db search by address and city. There is a list in Excel.