Calling functions within a function 2

[jproj]

I am trying to define the variables used in a custom function, but am not having much luck in figuring it out

Example (simplified of course):

Function test(a)
test = Excel.WorksheetFunction.RoundUp(a, 0.1)
End Function

Does anyone know how to add the definition of the selected variable (in this case "a&quot under the data inputs of the function (when you call the function).

for example... when you call the function "ROUNDUP", it has variables of "Number" and "Num_Digits". For the definition of "Number" it gives it's definition as

"Number is any real number you want rounded up."

Any help is much appreciated!

jproj

 

[ivymike]

so you're trying to add the "help" text for the variable? That's something I've been wondering about too...

 

[Mala]

If you are talking about the data type for the arguments in the 'Function' statement you could use:

Function test(a as Single)
test = Excel.WorksheetFunction.RoundUp(a, 0.1)
End Function

In its present form :
Function test(a)
the argument 'a' is the 'variant' data type.

If (as ivymike suggests) you are trying to write text to describe each argument in the Function input functionality that comes with Excel (the box which appears to the left of the screen with a separate input area for each arg and explanatory text beneath)...let me see...will get back with something (or maybe nothing) soon.

 

[jproj]

Sorry if my question was unclear. As ivymike stated, I am trying to add the help text to variables so users will know what each variable means (i.e. how it is defined) when they call the function for use. I've found an explination on the listed website, but have no clue how to use it...

http://www.j-walk.com/ss/excel/eee/eee009.txt

If you hit the "page down" key six times you should be just below the start of the proceedure...

From what I've heard, this is a difficult little problem. As always, any help is much appreciated.

jproj

 

[Mala]

I saw the related material you refer to. Didn't test it, but it appears fairly straightforward. What I can make out of it is:
1. Open a new workbook
2. Insert a module
3. On the top of the code page of the module enter the lines:
Const Lib = """c:\windows\system\user32.dll"""
Option Base 1
4.Write YOUR function procedures (like the Divide and Multiply examples).
5.Write the Autpen and Auto_Close procedures (alternatively you could put these in the Workbook_Open and Workbook_BeforeClose events respectively) - take care to subtitute the relevant parts of the strings you see with those pertaining to your own functions. Note that the arg descriptions are contained in the Auto_open proc.
6. Copy the 'Register' subroutine as is.

The whole idea is that once the functions are registered, they work like the native Excel functions and the arg description will show in the function input dialog box.

For this to work, obviously the workbook containing the above code must be open. To ensure this, try converting this to an add-in and with Tools>addins, add this to the list of Add-ins that automatically load when XL starts. Then the thing will be available in every Excel session.

Hope I was able to be of help...
Good luck!

 

[yakpol]

jproj,
Thanks for digging it up!