help with XLA...
 

I believe Laurent Longre discusses these issues at

http://xcell05.free.fr/

or
http://xcell05.free.fr/english/



--
Regards,
Tom Ogilvy


"Edmundo J. Dávila" wrote:

Hello,



I just created an AddIn (XLA) which will have different functions to query a
MS SQL DB.



I have the following questions:



1. How can I create a category function to group all the functions included
in the XLA? I would like to display this category with the rest of
categories included in MS EXCEL.



2. How can I add a short help text to be shown (like all the other fuctions
in MS Excel) when the user selects a function from my category?



In advance I'll appreciate all your comments.



Regards,



Edmundo J. Davila

help with XLA...
 

"Edmundo J. Dávila" wrote in message
...
1. How can I create a category function to group all the functions
included
in the XLA? I would like to display this category with the rest of
categories included in MS EXCEL.

Right-click on a sheet tab, select Inserty, and choose MS Excel 4.0 Macro.
On the Insert menu, point to Name, and then click Define. Select Function
from the Macro buttons.
In the Name box, type the name of your category.
In the Category box, replace "User Defined" with the new category name, and
then click OK.

Excel adds the new category to the list.
After you add custom functions to the new category, you can delete the
initially added function name. The new category remains, but if you delete
the Microsoft Excel 4.0 macro sheet, whilst functions already assigned to
the custom category remain in that category, you cannot assign a new
function to that category after you delete the macro sheet.

2. How can I add a short help text to be shown (like all the other
fuctions
in MS Excel) when the user selects a function from my category?

You can add a brief description(not arguments) if you open the VBEditor and
ViewObject Browser.

Find your UDF and right-click and you can add a comment there.

This comment will be shown in the Function Wizard when you click on your
UDF.

help with XLA...
 

Hello,



I just created an AddIn (XLA) which will have different functions to query a
MS SQL DB.



I have the following questions:



1. How can I create a category function to group all the functions included
in the XLA? I would like to display this category with the rest of
categories included in MS EXCEL.



2. How can I add a short help text to be shown (like all the other fuctions
in MS Excel) when the user selects a function from my category?



In advance I'll appreciate all your comments.



Regards,



Edmundo J. Davila

help with XLA...
 

Thanks both of you for your help.



Taking advantage of your answer I would like to show you one of the
functions of my XLA, if I execute it from the query analyzer of MS SQL
Server it's executed in 1 second or less, if I call the function from MS
Excel (probably the recordset is not the best way to get the data) it takes
almost 10 seconds. Also, if I refresh the sheet sometimes the values are
not calculated.



I don't have experience with VBA, may be I'm not setting properties of the
workbook in the right way. If you have any comments to improve the
performance of the function I'll appreciate it.



Regards,



Edmundo





Public Function cnTotalConcepto(ByVal Periodo As Date, ByVal Nomina As
String, ByVal Concepto As String) As Double
On Error GoTo EH


Dim sSQL As String
sSQL = " SELECT SUM(MONISA.EMPLEADO_CONC_NOMI.TOTAL) AS Monto " & _
" FROM MONISA.EMPLEADO_CONC_NOMI INNER JOIN " & _
" MONISA.NOMINA_HISTORICO ON
MONISA.EMPLEADO_CONC_NOMI.NOMINA = MONISA.NOMINA_HISTORICO.NOMINA AND " & _
" MONISA.EMPLEADO_CONC_NOMI.NUMERO_NOMINA =
MONISA.NOMINA_HISTORICO.NUMERO_NOMINA " & _
" WHERE (MONISA.NOMINA_HISTORICO.PERIODO = '" & Year(Periodo)
& Format(Periodo, "MM") & Format(Periodo, "dd") & "') AND " & _
" (MONISA.NOMINA_HISTORICO.NOMINA = '" & Nomina & "' )
AND " & _
" (MONISA.EMPLEADO_CONC_NOMI.CONCEPTO = '" & Concepto &
"') "

Dim rs As ADODB.Recordset

Set rs = Cnn.Execute(sSQL)
cnTotalConcepto = IIf(IsNull(rs(0)), 0, rs(0))

rs.Close
Set rs = Nothing

Exit Function
EH:
Err.Raise Err.Number

End Function



"Edmundo J. Dávila" escribió en el mensaje
...
Hello,



I just created an AddIn (XLA) which will have different functions to query
a MS SQL DB.



I have the following questions:



1. How can I create a category function to group all the functions
included in the XLA? I would like to display this category with the rest
of categories included in MS EXCEL.



2. How can I add a short help text to be shown (like all the other
fuctions in MS Excel) when the user selects a function from my category?



In advance I'll appreciate all your comments.



Regards,



Edmundo J. Davila