i create a user defined function as bellow. when i call this function
from VBA, it return correct result. but when call from excel sheet, it
return #value!. can anyone help me this? thanks in advance!
-----------------------------------------------------------------------------------------------------------------------
Function getsales(area As String, product As String, startd As String,
endd As String, stype As String) as double
'area: sh=shanghai, bj=beijing, gz=guangzhou, cd=chengdu
'stype could be:
'P: project
'R: retial
'D: Diy

Dim adoCN As ADODB.Connection, adoRS As ADODB.Recordset
Dim strSQL, salestype As String
Dim FinalRow, i As Integer
Dim sales

'determine sales type
If stype = "P" Then
salestype = "005396%"
ElseIf stype = "R" Then
salestype = "005398%"
End If

Set adoCN = New ADODB.Connection

'Connect string
adoCN.Open "Provider=sqloledb;" & _
"Data Source=sql-01;" & _
"Initial Catalog=DefDB;" & _
"User Id=abc;" & _
"Password=abc"

'open connction
Set adoRS = New ADODB.Recordset

'connect string
strSQL = _
"select sum(ST03020) As qty from ST035300" & _
" where ST03017 = '" & product & "'" & _
" and ST03015 = '" & startd & "'" & _
" and ST03015 <= '" & endd & "'" & _
" and ST03007 = '" & area & "'" & _
" and ST03009 like '" & salestype & "'"

'Open the recordset
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly

'Copy the recordset into a worksheet (will only work with Excel
2000 onwards)
Range("P1").CopyFromRecordset adoRS
sales = Range("P1").Value

'disconnect from database
adoRS.Close
adoCN.Close
getsales = sales

End Function