user defined function return #value!
 

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

user defined function return #value!
 

When called as a UDF from within a sheet a function can only modify the cell
that it is in. When you copyfromrecordset to cell P1 the action will fail...
You need to get the value directly from the recordset without first copying
it to a sheet...
--
HTH...

Jim Thomlinson


"AY Xu" wrote:

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

user defined function return #value!
 

Hi Jim, it works. i use getsales = adoRS.Fields(0).Value. thank you
very much.

Jim Thomlinson wrote:
When called as a UDF from within a sheet a function can only modify the cell
that it is in. When you copyfromrecordset to cell P1 the action will fail...
You need to get the value directly from the recordset without first copying
it to a sheet...
--
HTH...

Jim Thomlinson


"AY Xu" wrote:

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