Assign functions to categories 3

[Mutt]

In the list of functions available to the user, the Microsoft functions are nicely categorised - all my user defined ones are lumped under User Defined - anybody know how to create a new category and to allocate specific user defined functions to that category? Makes easier for users of my workbooks to find.

 

[joerd]

I received some code sometime that shows how to do it, it uses the (in my opinion infamous) ExecuteExcel4Macro method. I have not tried it, but it looks ok (sorry for the wrapped lines):

Const Lib = """c:\windows\system\user32.dll"""
Option Base 1
 
Private Function Multiply2(N1 As Double, N2 As Double) As Double
 Multiply2 = N1 * N2
End Function

Private Function Multiply3(N1 As Double, N2 As Double, N3 As Double) As Double
 Multiply3 = N1 * N2 * N3
End Function
 
Private Function Divide(N1 As Double, N2 As Double) As Double
 Divide = N1 / N2
End Function
 
Sub Auto_open()
 
 Register "DIVIDE", 3, "Numerator,Divisor", 1, "My Functions1", "Divides two numbers", """Numerator"",""Divisor """, "CharNextA"
 Register "MULTIPLY2", 3, "Number1,Number2", 1, "My Functions1", "Multiplies two numbers", """First number"",""Second number """, "CharB"
 Register "MULTIPLY3", 4, "Number1,Number2,Number3", 1, "My Functions2", "Multiplies three numbers", """First number"",""Second number"",""Third number  """, "CharPrevA"
 
End Sub
 

Sub Register(FunctionName As String, NbArgs As Integer, Args As String, MacroType As Integer, Category As String, Descr As String, DescrArgs As String, FLib As String)
 
 Application.ExecuteExcel4Macro "REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") & """,""" & FunctionName & """,""" & Args & """," & MacroType & ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"
 
End Sub
 
Sub Auto_close()
 
 Dim FName, FLib
 Dim i As Integer
 FName = Array("DIVIDE", "MULTIPLY2", "MULTIPLY3")
 FLib = Array("CharPrevA", "CharNextA")
 For i = 1 To 3
   With Application
     .ExecuteExcel4Macro "UNREGISTER(" & FName(i) & ")"
     .ExecuteExcel4Macro "REGISTER(" & Lib & ",""CharPrevA"",""P"",""" & FName(i) & """,,0)"
     .ExecuteExcel4Macro "UNREGISTER(" & FName(i) & ")"
   End With
 Next
 
End Sub

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Mutt]

Thanks Joerd - works fine with some small mods - have to use system32 instead of system and didn't get the multiply2 function listed until CharB was replaced with CharNextA

Great help - much appreciated!!

Mutt.

 

[Mutt]

Hi Joerd - hit snag - can't seem to get more than two functions per category - tried all possible combos of Flib but not really sure what they represent - got some really wierd results as I messed around with the CharPrevA, CharNextA, Char B permutations. Do you have some background reference info I could read up on perhaps? Now I'm 3/4 way there really hooked to try to get sorted out!

Thanks.
Mutt.

 

[joerd]

Mutt,

We're getting into the deep undocumented features of Windows so I am getting lost as well. It seems a bit that a little used resource from USER32.DLL is used as a placeholder, to enable registration of the user defined function. So, as far as I can see, you can use CharNextA in all cases (not even a need for CharPrevA). CharB is not a USER32.DLL resource, so that will fail.
By the way, to avoid problems with Windows NT/2000 machines, where USER32.DLL is in c:\winnt\system32\, we should use the "friendly name" USER32 instead.

So the code can become:

Const Lib = """USER32"""
Option Base 1
 
Private Function Multiply2(N1 As Double, N2 As Double) As Double
 Multiply2 = N1 * N2
End Function

Private Function Multiply3(N1 As Double, N2 As Double, N3 As Double) As Double
 Multiply3 = N1 * N2 * N3
End Function
 
Private Function Divide(N1 As Double, N2 As Double) As Double
 Divide = N1 / N2
End Function
 
Sub Auto_open()
 
 Register "DIVIDE", 3, "Numerator,Divisor", 1, "My Functions1", "Divides two numbers", """Numerator"",""Divisor """, "CharNextA"
 Register "MULTIPLY2", 3, "Number1,Number2", 1, "My Functions1", "Multiplies two numbers", """First number"",""Second number """, "CharNextA"
 Register "MULTIPLY3", 4, "Number1,Number2,Number3", 1, "My Functions2", "Multiplies three numbers", """First number"",""Second number"",""Third number  """, "CharNextA"
 
End Sub
 

Sub Register(FunctionName As String, NbArgs As Integer, Args As String, MacroType As Integer, Category As String, Descr As String, DescrArgs As String, 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

Hope this works for you. By the way, I suppose Autpen and Auto_Close still work, otherwise these should be moved to the Workbook_Open and Workbook_Close event procedures.
To give proper credit, I guess the original code is from Laurent Longre, and can be found on John Walkenbach's site in "EEE Issue No. 09 (July 15, 1999)" :

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[TomSD]

Joerd & Mutt;

I got excited about Longre's method some time ago but most of my documentation is at home. Laurent Longre posted this work-around method for adding descriptions of arguments for VBA user-defined functions (UDF's) in Excel, and this method simultaneously allows the user-defined functions to be placed in a category. I have seen another posting by Laurent Longre that specifically dealt with assigning UDF's to categories but can't seem to locate it today.

For the method using the Excel4Macro function REGISTER,
the original posting of the code, with a brief description of the methodology, is at:

http://groups.google.com/groups?hl=...&prev=/groups?selm=34D1733C.7F87%40wanadoo.fr

(long URL I know but the only way I seem to be able to access the post)
The code was written by Laurent Longre, 1998/01/30, as the 3rd reply on
Subject: User-defined function Category
Newsgroups: microsoft.public.excel.programming </groups?hl=en&lr=&ie=UTF-8&group=microsoft.public.excel.programming>
Date: 1998/01/28

Hopefully, the original posting helps to explain how the work-around works but as I understand it, the REGISTER function allows one to &quot;register&quot; 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 &quot;registering&quot; each UDF; I never tried this; please let me know if it really works. As I understand the method, each UDF has to be &quot;registered over&quot; a different function from the library. The library function that is &quot;registered over&quot; 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.
I had wondered if a dummy library with dummy functions might be set up in order to avoid registering over potentially useful functions in exisiting libraries. However, it seemed that the dll resources have to be written in C language, but perhaps a VBA dll might also work. I am not enough of a programmer to explore this further.
However, I have found today, a free download made available by Laurent Longre at:

http://longre.free.fr/english/

I haven't tried using the download yet but my first guess is that he is providing a &quot;dummy&quot; dll.

Hope this helps
Tom

 

[Mutt]

Hi Tom - yep using the same library function results in all the categorised functions having the same descrips and argument names - have to have unique FLib for each it appears. Saw a version of the code on a german site using kernel32 library functions in similar manner. I have given up at this point to try as think is going to perhaps give my clients more hassles than benefits and have got local South African Microsoft guys exploring how to do this without using Excel4 - I understand this is going to fall away anyhow in future Excel versions according to one panel of experts I came across. As soon as I get feedback I'll post the method here. In meantime I use good old forms mimicing the formula menu function to allow my client to access the functions easier - just so tedious for coding!! Used to be dead easy in Version 5...right click and allocate to a category.

 

[TomSD]

Hi Mutt
Thought so. I have also seen posts that the method won't work as Excel4 macro functions are no longer supported and already some (e.g. Call, RegisterID) aren't allowed to work at all already due to security issues; the REGISTER function still works in the latest Excel version on XP but who knows what the next version will allow.
It's maddening that there is no straight-forward method of customizing UDF's with descriptions and into function categories. I would be most appreciative seeing any other methods that you discover.
Tom

 

[joerd]

Interesting thread, gentlemen!
I see that you also reach the conclusion that defining your own UDF category results in more trouble than benefit, so I stopped trying quite some time ago. I agree with Tom that there should be a more straightforward method to customize the UDF. In my company, we have resorted to providing a long description (which is easy to do) for the function, which also includes help for each argument (especially units of measurement), and to leave the function in the User Defined category.
By the way, if you have Quickview, then you can see all the function exports if you quickview the DLL. I have also found quite a few sites with listings when searching for Windows API USER32.DLL



Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Mutt]

Hi All - have eventually managed to figure out one workaround on this one - still checking for bugs across users - perhaps if you try code below, you may find bugs for me! Main problem I have is I don't know index value of new category added, so have to assume is last one and iterate thru whole lot until get error message and then shift all defined functions to the last working index value. I'm sure there must be a way to find out category listing but I can't yet!


Function test(a)
test = 2 * a
End Function

Function test2(b)
test2 = 3 * b
End Function

Sub test1()
ActiveWorkbook.Names.Add _
Name:=&quot;Steve&quot;, _
RefersTo:=&quot;S&quot;, MacroType:=1, Category:=&quot;Steve&quot;
On Error GoTo Errortrapper
For i = 1 To 100
Application.MacroOptions Macro:=&quot;test&quot;, Category:=i
Next i
Errortrapper:
'Move defined functions to last category on list named Steve.
Application.MacroOptions Macro:=&quot;test&quot;, Category:=i - 1
Application.MacroOptions Macro:=&quot;test2&quot;, Category:=i - 1
'Move dummy Steve name to All list - safely tucked away.
Application.MacroOptions Macro:=&quot;Steve&quot;, Category:=0
On Error GoTo 0
End Sub