Excel VBA Function Descriptions Dilaog

Attribute VB_Name = "FunctionDescriptions" Option Explicit ''https://myengineeringworld.net/2013/07/add-description-to-custom-vba-function.html Enum eFunCat ''https://learn.microsoft.com/en-us/office/vba/api/excel.application.macrooptions Financial = 1 Date_Time = 2 Math_Trig = 3 Statistical = 4 Lookup_Reference = 5 Database = 6 text = 7 Logical = 8 Information = 9 Commands = 10 Customizing = 11 Macro_Control = 12 DDE_External = 13 User_Defined = 14 First_custom_category = 15 Second_custom_category = 16 Third_custom_category = 17 Fourth_custom_category = 18 Fifth_custom_category = 19 Sixth_custom_category = 20 Seventh_custom_category = 21 Eighth_custom_category = 22 Ninth_custom_category = 23 Tenth_custom_category = 24 Eleventh_custom_category = 25 Twelfth_custom_category = 26 Thirteenth_custom_category = 27 Fourteenth_custom_category = 28 Fifteenth_custom_category = 29 Sixteenth_custom_category = 30 Seventeenth_custom_category = 31 Eighteenth_custom_category = 32 End Enum '----------------------------------------------------------------------------------- Private Sub FunctionRegisterDescriptions_register() Call FunctionRegisterDescriptions( _ "FunctionRegisterDescriptions", _ "Register function by VBA name to explan each variables funciton in the funciton dialog box.", _ Commands, _ Arr1( _ "VBA Name of function to register", _ "Description of funciton", _ "Function category from VBA eFunCat Enumeration.", _ "Array of Comma separated strings ''string1'', 'string2'' describing each parameters function." _ ) _ ) End Sub '----------------------------------------------------------------------------------- Function Arr1(ParamArray var() As Variant) As Variant Dim i As Integer Dim arrOut() As String ReDim arrOut(1 To UBound(var()) + 1) As String ''set bound from 1 to count For i = 1 To UBound(var) + 1 arrOut(i) = var(i - 1) ''apply values Next i Arr1 = arrOut End Function '----------------------------------------------------------------------------------- Function FunctionRegisterDescriptions(FuncName As String, FuncDescript As String, FuncCat As eFunCat, argDesc As Variant, Optional NoMsg As Boolean = True) Attribute FunctionRegisterDescriptions.VB_Description = "Register function by VBA name to explan each variables funciton in the funciton dialog box." Attribute FunctionRegisterDescriptions.VB_ProcData.VB_Invoke_Func = " \n10" Dim msg As String '------------------------------------------------------------------------ 'This sub can add a description to a selected user-defined function, '(UDF) as well as to its parameters, by using the MacroOptions method. 'After running successfully the macro the UDF function no longer appears 'to the UDF category of functions, but into the desired category. 'By Christos Samaras 'Date: 23/07/2013 'xristos.samaras@gmail.com 'https://myengineeringworld.net///// '------------------------------------------------------------------------ 'Delclaring the necessary variables 'Dim FuncName As String 'Dim FuncDesc As String 'Dim FuncCat As Variant <<-----Redefined as an enum. 'Depending on the function arguments define the necessary variables on the arry. 'Here UDF funciton has four arguments, so four variables are declared. 'Dim ArgDesc(1 To 4) As String '"FrictionFactor" is the name of the function. 'FuncName = "FrictionFactor" 'Here we add the function's description. 'FuncDesc = "Calculates the friction factor of a pipe using Churchill's equation." 'Choose the built-in function category (it will no longer appear in UDF category). 'For example, 15 is the engineering category, 4 is the statistical category etc. 'See the code at the end for all available categories. ''FuncCat = 15 'You can also use instead of numbers the full category name, for example: 'FuncCat = "Engineering" 'Or you can define your own custom category: 'FuncCat = "My VBA Functions" 'Here we add the description for the function's arguments. 'ArgDesc(1) = "Pipe Roughness in m" 'ArgDesc(2) = "Pipe Diameter in m" 'ArgDesc(3) = "Fluid Velocity in m/s" 'ArgDesc(4) = "Fluid Viscosity in m2/s" 'Using the MacroOptions method add the function description (and its arguments). Application.MacroOptions _ Macro:=FuncName, _ Description:=FuncDescript, _ Category:=FuncCat, _ ArgumentDescriptions:=argDesc 'Available built-in categories in Excel. 'This select case is somehow irrelevelant, but it was added for 'demonstration purposues. Select Case FuncCat Case 1: msg = "Financial" Case 2: msg = "Date & Time" Case 3: msg = "Math & Trig" Case 4: msg = "Statistical" Case 5: msg = "Lookup & Reference" Case 6: msg = "Database" Case 7: msg = "Text" Case 8: msg = "Logical" Case 9: msg = "Information" Case 10: msg = "Commands" Case 11: msg = "Customizing" Case 12: msg = "Macro Control" Case 13: msg = "DDE/External" Case 14: msg = "User Defined default" Case 15: msg = "Engineering" Case Else: msg = FuncCat End Select 'Inform the user about the process. If Not NoMsg Then MsgBox FuncName & " was successfully added to the " & msg & " category!", vbInformation, "Done" End Function