Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Function is not working for me in Excel 2007 | Excel Discussion (Misc queries) | |||
How do i create a user defined chart in Powerpoint or excel? | Charts and Charting in Excel | |||
How can I create a user defined function in excel? | Excel Discussion (Misc queries) | |||
How to create User Defined Function | Excel Programming | |||
Create help for user-defined function | Excel Programming |