How to create User Defined function in Excel
 

Hi

Is there any way to create UDF in Excel?

All I want is to have a function to retrieve the sales
amount from ODBC source. The following is an example.

From Date To date Amount
01/10/2003 31/01/2003 calSales(A2,B2)


calSales is an User Defined Function. It will return the
total sales amount between dates that entered at cell A2
and B2.

Thanks
Johnny Ko

How to create User Defined function in Excel
 

you should go to visual basic editor, add a module to your spreadsheet, and
then write the function:

Public Function calSales (FromDate as date, ToDate as date) as Double

calSales=....
end function

then use it in excel like a regular function, just type in a cell
=calSales(a2, b2)

Best -
RADO


"Johnny Ko" wrote in message
...
Hi

Is there any way to create UDF in Excel?

All I want is to have a function to retrieve the sales
amount from ODBC source. The following is an example.

From Date To date Amount
01/10/2003 31/01/2003 calSales(A2,B2)


calSales is an User Defined Function. It will return the
total sales amount between dates that entered at cell A2
and B2.

Thanks
Johnny Ko

How to create User Defined function in Excel
 

Hi Johnny,
How to create UDF in Excel is not so difficult.(I think ODBC would be
difficult.)
Open VBE(Visual Basic Editor), insert a standard module, and write code
there.


Function calSales(ByVal Target1 As Range, ByVal Target2 As Range) As Double
'? Change variable type this function to suit.
'Declare variables at here.

'place your ODBC code here....This is just a sample, so it doesn't
work.
Set cn = CreateObject("ADODB.Connection")
objCnnection.ConnectionString =
"ODBC;DSN=xxx;UID=Uset-ID;PWD=Password;DBQ=xxx;ASY=OFF;"
objCnnection.Open
select_sql = ""
select_sql = assy_sql + "SELECT -----" & Chr(13) & "" & Chr(10)
select_sql = assy_sql + "FROM ------" & Chr(13) & "" & Chr(10)
select_sql = assy_sql + "WHERE -----" & Chr(13) & "" & Chr(10)
Set rs = CreateObject("ADODB.Recordset")
objRecordset.Open select_sql, cn, 0
objRecordset.Close
objCnnection.Close
Set objCnnection = Nothing
Set objCnnection = Nothing

'set Return Value
calSales = xxx
End Function


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
"Johnny Ko" wrote in message
...
Hi

Is there any way to create UDF in Excel?

All I want is to have a function to retrieve the sales
amount from ODBC source. The following is an example.

From Date To date Amount
01/10/2003 31/01/2003 calSales(A2,B2)


calSales is an User Defined Function. It will return the
total sales amount between dates that entered at cell A2
and B2.

Thanks
Johnny Ko