Leading the way in Microsoft Office Development
 Home|Excel|Word|PowerPoint|Consultancy|Feedback|Contact 
 Microsoft Excel > Functions User Defined > Function Arguments< Previous | Next > 
 

Step 1 - Adding descriptions to your Function Arguments

 
 

By default there is no way to add descriptions to the arguments of your user defined functions.

 
 

However a workaround was found by the following people Jan Karel Pieterse, Laurent Longre and KeepITCool.

 
 

In this example are we are going to use the CONTAINS() user defined function.

 
 

Just adding the code to a workbook will display the following dialog box, i.e. with no argument descriptions.

 
   

 

Step 2 - With Argument Descriptions

 
 

You can add argument descriptions by using the old XLM Register Function in conjunction with the ExecuteExcel4Macro VBA function but you are limited to a maximum of 255 characters.

 
 

This method is therefore no good when you have functions that contain more than a couple of arguments.

 
 

It is possible to overcome this restriction by using an ancient XLM macro sheet.

 

 

Step 3 - ??

 
 

Laurent Longre discovered, that if one uses the Excel 4 macro language's register function to register a function residing within any system dll, using an alias name which is identical to the name of a UDF, one can assign the UDF to one's own category in the function wizard. At the same time, one can also add a function description and argument descriptions.

 
 

This method is based on a curious behaviour of XL97 : assume that an open workbook contains a function called "Myfunc". If you try now to register any DLL function (for instance, one of the Win95 API) with the same name, here's what happens: when you use MyFunc() in a worksheet, it works fine and returns the result of the VBA function. But if you call it with the function wizard, it displays the informations of ... the registered DLL function!

 

 

In other words, you can add a few lines in an Auto_open Sub which register some API functions with the same names as your VBA functions. REGISTER enables to assign the functions to any custom categories, and also to "document" each argument. When you call later one of the VBA custom function, these parameters passed to REGISTER will appear in the function wizard, including the custom category.

 

 

After further experimenting, I've noticed also that you must declare the VBA functions "Private" in order to remove them from the default "user-defined" category (otherwise, they would appear twice in the wizard). Of course, the VBA add-in must also provide an Auto_close Sub which unregisters the functions. To remove the added custom categories, I've found this way : first, unregister the functions, then register them with the MacroType argument set to 0 (= hidden function), and finally unregister them one more time.

 

 

This method is just a funny work-around. I don't know if it works without any restriction (I've just achieved a few tests), and it requires that you add some Auto_open and Auto_close code in the add-in workbook.

 

 

the REGISTER function allows one to "register" a function from a library such as USER32.dll with an alias name (perhaps originally intended to allow a more user-friendly name), assign the library function to a function category for Excel's Paste Function list palette, and also provide brief descriptions of the arguments for the library function that will appear in Excel's Paste Function formula palette. However, if the alias name (e.g. Multiply2) chosen for the library function (e.g. CharNextA) happens to be also the name of an UDF available in the workbook, then the UDF will be used in place of the library function when the function name/alias is called up.

 

 

I see that Joerd indicates that the same library function can be used in "registering" each UDF; I never tried this; please let me know if it really works. As I understand the method, each UDF has to be "registered over" a different function from the library. The library function that is "registered over" with a user-defined-function (UDF), is not available once the UDF is treated by this method but there are a lot of obscure functions in these libraries that are very seldomly used. All of the functions in the library (e.g. USER32.dll) can be quickly seen by opening the dll file in notepad and scrolling down to where identifiable words show up. A few of the functions in USER32.dll are: ActivateKeyboardLayout AdjustWindowRect AdjustWindowRectEx ... BeginDeferWindowPos BeginPaint ... CallMsgFilter CallMsgFilterA ... CharLowerA CharLowerBuffA CharLowerBuffW CharLowerW CharNextA CharNextExA CharNextW CharPrevA CharPrevExA CharPrevW CharToOemA CharToOemBuffA CharToOemBuffW CharToOemW CharUpperA CharUpperBuffA CharUpperBuffW CharUpperW ... DdeAbandonTransaction DdeAccessData ... etc.

 
 

However, I won't avdocate which library functions you should register over.

 


 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
Const Lib = """USER32"""
Option Base 1

Private Function Divide(ByVal N1 As Double, _
                        ByVal N2 As Double) As Double
  Divide = N1 / N2
End Function

Private Function Multiply2(ByVal N1 As Double, _
                           ByVal N2 As Double) As Double
  Multiply2 = N1 * N2
End Function

Private Function Multiply3(ByVal N1 As Double, _
                           ByVal N2 As Double, _
                           ByVal N3 As Double) As Double
  Multiply3 = N1 * N2 * N3
End Function

Private Sub Auto_Open()
  Call Register("DIVIDE", _
                3, _
                "Numerator,Divisor", _
                1, _
                "My Functions1", _
                "Divides two numbers", _
                """Numerator"",""Divisor""", _
                CharNextA)

  Call Register("MULTIPLY2", _
                3, _
                "Number1,Number2", _
                1, _
               "My Functions1", _
               "Multiplies two numbers", _
               """First number"",""Second number""", _
               "CharNextA")

  Call Register("MULTIPLY3", _
                4, _
               "Number1,Number2,Number3", _
                1, _
                "My Functions2", _
                "Multiplies three numbers", _
                """First number"",""Second number"",""Third number""", _
                "CharNextA")

End Sub

Private Sub Register(ByVal sFunctionName As String, _
                     ByVal iNoOfArguments As Integer, _
                     ByVal Args As String, _
                     ByVal MacroType As Integer, _
                     ByVal Category As String, _
                     ByVal Descr As String, _
                     ByVal DescrArgs As String, _
                     ByVal FLib As String)

 Application.ExecuteExcel4Macro "REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") & _
             """,""" & FunctionName & """,""" & Args & """," & _
       MacroType & ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"

End Sub

Sub Auto_Close()

 Dim FName
 Dim i As Integer
 FName = Array("DIVIDE", "MULTIPLY2", "MULTIPLY3")
 For i = 1 To 3
   With Application
     .ExecuteExcel4Macro "UNREGISTER(" & FName(i) & ")"
     .ExecuteExcel4Macro "REGISTER(" & Lib & ",""CharNextA"",""P"",""" & FName(i) & """,,0)"
     .ExecuteExcel4Macro "UNREGISTER(" & FName(i) & ")"
   End With
 Next

End Sub
   


 

Unfortunately, if you load files which introduce new categories, the category index numbers depend on the order of loading. If you can't predict this, you can't be sure what category the function will fall into. I know of no good solution to this,

 





 Copyright © 2004-2007 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next >