VBA Application MacroOptions

[excelgolfer]

Hi,
This Macro code won't list all the Argument Descriptions, it only list the first description in the array "ArgDesc (1 to 2). How do i list all the strings in the array "ArgDesc" ?
thanks

Sub DescribeFunction()
'This can be used to add help text to each function


   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 2) As String
  
'type the function name in the quotes below
   FuncName = "OilHeat_DSN100G_LOF"


'type the help text in the quotes below
   FuncDesc = "estimates the oil heat gain in (BTU/min) with oil restrictor plug installed, from input power (hp)and airend adiabatic efficiency (%)"
   
   Category = 14 'User Defined
   
'type the argument descriptions in the quotes below.  Add more arguments as needed, and change X in Dim ArgDesc(1 To X)
 'to a number at least as large as total number of arguments
   ArgDesc(1) = " = input power (hp)"
   ArgDesc(2) = " = System adiabatic efficiency (%)"
         
   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc


'run the macro called DescribeFunction, then save


End Sub

 

[NdNoviceHlp]

Dim ArgDesc(1 To 2) As Variant

Maybe...

ArgumentDescriptions:=ArgDesc(1) & ArgDesc(2)

HTH. Dave

 

[excelgolfer]

Dave,
Thanks for the quick reply.
Got a run time error on the last line.
ArgumentDescriptions:=ArgDesc(1) & ArgDesc(2)
Any other suggestions.

I tried

ArgumentDescriptions:=ArgDesc()

Only listed the first variable again

 

[NdNoviceHlp]

Where's the code for the FuncName macro? I have no idea what Application.MacroOptions is nor have I seen it used in code before? To get data out of an array...

For Cnt = LBound(ArgDesc) To UBound(ArgDesc)
Msgbox ArgDesc(cnt)
Next Cnt

Maybe others will have better vision than me? Dave

 

[NdNoviceHlp]

Google info... https://docs.microsoft.com/en-ca/office/vba/api/Excel.Application.MacroOptions

 

[6StringJazzer]

This is a few years old but I have the same question and have not found answer. However, I can at least clarify the question.

When you write a UDF, you can call Application.MacroOptions to set up the wizard dialog for the function. It will give an input box for the value of each argument, and underneath will give a description of the function plus a description of each argument. The descriptions of the arguments are passed in as an array. However, only the first element of the array is used, to describe the first argument, and the rest are ignored.

   Dim SA() As String
        
    ReDim SA(1 To 3)
    SA(1) = "The base date"
    SA(2) = "Months to add (may be negative)"
    SA(3) = "Days to add (may be negative)"
  
    Application.MacroOptions _
        Macro:="DATEOFFSET", _
        Description:="Offset a date by the given number of year, months, days." & vbCrLf & _
                     "Valid for dates before 1900", _
        Category:="Date & Time", _
        ArgumentDescriptions:=SA

Public Function DATEOFFSET(BaseDate As Variant, Years As Long, Months As Long, Days As Long) As Variant

Result:

 

[6StringJazzer]

OK, here's the answer. The code in the OP is correct. The description underneath is for whichever input box has focus.

And so on.