Calling a SQL Server Function From Excel VBA

[BrettHawk]

Dear Forum,

As I'm new to VBA, I was hoping you could answer a VBA / SQL Server 2005 Function question for me.

The scenario is this:

I am trying to call a SQL Server 2005 Function from within Excel (as a Function).
So basically, I want my excel user to type into a cell =MyFunction() which will call the SQL Sever 2005 Function.

I have managed to call Stored Procs etc from within Excel VBA, but functions remain a mystery to me. I just cannot seem to assign the returned value correctly.

To further explain, I have created a small sample Function in SQL Server and is posting that code below.

Thanks

Brett

CREATE FUNCTION dbo.countrecordsnow (
)
RETURNS INT
AS
BEGIN

declare
[INDENT][/INDENT]@v_count INT,
[INDENT][/INDENT]@v_return INT

BEGIN
[INDENT][/INDENT]
select @v_count = COUNT(*) from dbo.ANALYST

set @v_return = @v_count
return (@v_return)

end

END

[vb5prgrmr]

use a recordset object to execute your stored procedure and since you have not aliased the count, you would use rs.fields(0)



Good Luck