Developing.ru

seerqy писал(а):теория вроде что понятно а примерои не поделитесь

1) Copy FunCustomize.dll in the same directory as your XLA.
2) Enter the parameters of your functions in a worksheet of the XLA, in the following form :
A B C D E F G H
Name Argument Type Category HelpId FunctionDescription TextArg1 TextArg2

Name = name of the function (same as the VBA name, not case-sensitive)
Arguments = enter here the names of each argument, separated with commas.
Type = 0 or 1 (empty = 1). If 0, the function will be hidden.
Category = name or index of the function category.
Help id = path of the help file + help context id, if needed.
Function description = description of the function
Text Arg 1, Text Arg 2, Text Arg 20 = description of each argument.
You can enter up to 20 argument descriptions (25 columns max. including the 5 first parameters)
To make the worksheets of your XLA visible, select ThisWorkbook and set its IsAddin property to False. Once you have finished, set again this property to True and save the add-in from the VB editor.
I assume that the worksheet containing these parameters has the CodeName "shFunctions" and that they are stored in the range A2:Z5 (4 user-defined functions).
3) Add the following Auto_Open Sub in any standard module of the XLA :

Sub Auto_Open()
Application.RegisterXLL ThisWorkbook.Path & "\FunCustomize.dll"
Run [FunCustomize], ThisWorkbook.Name, shFunctions.Range("A2:Z5")
End Sub
Replace shFunctions.Range("A2:Z5") with the reference of the range you have used to store the needed informations.
You can put these lines of code in Workbook_Open, but you should be aware that this Sub, unlike Auto_Open, could be disabled if Application.EnableEvents is set to False.
You can rename FunCustomize.dll and put it where you want, provided that you pass a valid path to the Application.RegisterXLL method.
Optionally, you can use a VBA Array instead of a range if you don't want to store the informations in a worksheet :
Run [FunCustomize], ThisWorkbook.Name, MyArray
This array must have the same fields as the range above.

4) Add the following lines to ThisWorkbook :

Private Sub Workbook_AddinUninstall()
If Run([FuncDelete], shFunctions.Range("A2:Z5")) = -1 Then _
ExecuteExcel4Macro "UNREGISTER(""" & Me.Path & "\FunCustomize.dll" & """)"
End Sub
Replace shFunctions.Range("A2:Z5") like in Auto_Open.
This Sub deletes the names of the functions in the function wizard when the user unloads the add-in from the add-ins wizard, deletes the new function category if it is empty, and closes FunCustomize.dll if it is no more needed.
You can also put these lines in Auto_Close or Workbook_BeforeClose. I think that Workbook_AddinUninstall is better, because the Workbook_BeforeClose and Auto_Close Subs can be automatically called when the user selects File => Quit but cancels it before the application is closed.

5) Add "Option Private Module" in all modules of your XLA containing user-defined functions
This will prevent the function wizard to show these functions twice, in the "Custom" category and in the new function category created by FunCustomize.dll. 'Option Private Module' must appear at the beginning of the module.
Your modules should look like :
Option Private Module
Function Func1(...)
' blablabla...
End Function
Function Func2(...)
' blablabla...
End Function
' blablabla...
Limitations
- Unlike with standard functions, you can't use String arguments containing more than 255 characters.
- FunCustomize.dll can customize up to 200 functions. However, if you want to use more functions, you can copy and rename FunCustomize.dll, and then use this new copy like the original FunCustomize.dll.
- The total length of all string fields (Function name, argument names, descriptions…) can't contain more than 255 characters. If it exceeds this limit, the last strings will be truncated in the function wizard.
- The functions are limited to 29 arguments, instead of 30.
- FunCustomize is compatible with VBA functions using ParamArray() arguments, but in this case you must enter a name for every optional argument in the "Arguments" field, for instance: "Param 1,Param 2,Param 3,…,Param 29"
- Since there is no link between workbooks and XLA add-ins using FunCustomize, it is recommended to force a full recalculation in Auto_Open, in order to avoid some problems with volatile user-defined functions :

Sub Auto_Open()
Application.RegisterXLL ThisWorkbook.Path & "\FunCustomize.dll"
Run [FunCustomize], ThisWorkbook.Name, shFunctions.Range("Options")
Application.SendKeys "%^{F9}"
End Sub