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
@v_count INT,
@v_return INT
 
BEGIN
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