i need help with a user-defined function in VBA 1

[kskinne]

I have put together some code in Excel VBA in an attempt to write a function that will query a sql database and return a value. Here's my code:

Dim adoCN As ADODB.Connection
Dim strConn As String
Dim adoRS As ADODB.Recordset
Dim strSQL As String

Public Function Acct_Bal(LookUpPeriod As String, LookUpAcct As String, _
LookUpFiscYr As String, LookUpCpnyID As String) As Variant
If adoCN Is Nothing Then Call SetUpConnection
Set adoRS = New ADODB.Recordset
strSQL = "SELECT YTDBal" & LookUpPeriod & " FROM AcctHist WHERE Acct = '" & _
LookUpAcct & "' and FiscYr = '" & LookUpFiscYr & "' and CpnyID = '" & _
LookUpCpnyID & "'"
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
Acct_Bal = adoRS.Fields("YTDBal08").Value
adoRS.Close
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=sql;Initial Catalog=BREDA;Data Source=BREDARETAIL"
adoCN.Open strConn
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "Error connecting to database"
End Sub


Not sure why this isn't working but all my function returns is a #VALUE! error. I believe I'm opening the connection and recordset correctly, the recordset only returns one record, and one value, so I'm thinking that I am not correctly extracting that record from my record set, to return it as the result of my function.

Anyone familiar with this, that can tell me what's wrong with this code?

Also, is there a way I could instead compile this code into a DLL instead of having the code in my Excel workbook?

Thanks in advance
Kevin

 

[PHV]

Very first at all, I'll replace this:
Acct_Bal = adoRS.Fields("YTDBal08").Value
with this:
Acct_Bal = adoRS.Fields("YTDBal" & LookUpPeriod).Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kskinne]

Thanks for your reply - I changed that as you said and it still doesn't work - also tried:

Acct_Bal = adoRS.Fields(0).Value

with the same result. Here is the function as I'm entering it on the spreadsheet:

=Acct_Bal("08","1130.00","2006","01")

where 08 is the last two digits in YTDBal08, 1130.00 is the Acct, 2006 is the FiscYr, and 01 is the CpnyID

it's still giving me the #VALUE! error

Any other ideas?

Thanks
Kevin

 

[PHV]

What are the data type of the relevant columns in the AcctHist table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kskinne]

Here are the data types:

Acct - char(10)
CpnyID - char(10)
FiscYr - char(4)

all of the YTDBal?? columns, which includes YTDBal08, are float data types

Kevin

 

[PHV]

Have you tested the runtime value of strSQL in the QueryAnalyzer ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kskinne]

yes I did that too, and returns the correct value that I'm looking for - the query string is good, so I think it's got be somewhere else in the code?


Thanks for your help
Kevin

 

[kskinne]

is there a way to upload files to the forum? if so i'd be willing to upload the spreadsheet if that would make troubleshooting easier

thanks
kevin

 

[PHV]

Do you know how to step the code in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kskinne]

not sure exactly how to do that in Excel - can you explain the process?

thanks
Kevin

 

[kskinne]

I can set a breakpoint on the SetupConnection Sub procedure, and step into that - when I do that it goes through the code fine without an error. Not sure how to step in the Function procedure though...

 

[PHV]

Insert a Stop instruction as the first statement in your Acct_Bal code.
Then in the Immediate window (Ctrl-G) type:
? Acct_Bal("08","1130.00","2006","01")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kskinne]

ok, i was able then to debug it, and got it to error out on the strConn line - I did some digging and found out from our IT person that the server name has changed so I changed it in my code, and it is now connecting correctly and working just as it should. thank you very much!

I had one other question after this and this may not be the best forum for it - I right now have this code in a VBA project within the workbook. I do have access to the VB 6.0 program. Is there a way that I could compile this into a DLL file so that I could just install on any workstations and they would have access to this UDF? Or can you recommend the best place on tek-tips where I should post this question?

Thanks again and merry christmas

Kevin

 

[SkipVought]

Hi,

You can EXPORT the module and IMPORT into VB 6.0.

You can also have others IMPORT the module into their PERSONAL.XLS workbook. I supply functions to may users this way.

Skip,

_[red][/red]