Excel User defined Functions

[wahmed]

Hello all,

I would like to make some user defined functions in Excel vba, I have managed to make that functions but problem is that they dont show help in entering data like excel own functions show.

Like when we enter =Vlookup( in excel then it start showing help menu which show Lookup_value,Table_array,Col_index etc which make easy for user to enter data as per function's requirement. but in User Defined function excel dot show any help like that which make very odd to remember each argument that which was for which purpose....

Could any one help me about that ?

Regards

Wahmed

 

[CharlesChuckieCharles]

Hello all,

I would like to make some user defined functions in Excel vba, I have managed to make that functions but problem is that they dont show help in entering data like excel own functions show.

Like when we enter =Vlookup( in excel then it start showing help menu which show Lookup_value,Table_array,Col_index etc which make easy for user to enter data as per function's requirement. but in User Defined function excel dot show any help like that which make very odd to remember each argument that which was for which purpose....

Could any one help me about that ?

Regards

Wahmed

The routines should be in a module ( thats not the Thisworkbook or sheet1 etc ) Module1 or MyMod or MyStuff etc

and use the Public keyword as in
"Sub GetTime(..." becomes "Public Sub GetTiime(..."


and its the same for functions

 

[wahmed]

Dear Charles,

thanks for your message but sorry it is not working

Please help me

regards

wahmed

The routines should be in a module ( thats not the Thisworkbook or sheet1 etc ) Module1 or MyMod or MyStuff etc

and use the Public keyword as in
"Sub GetTime(..." becomes "Public Sub GetTiime(..."


and its the same for functions

 

[Trevor G]

Hello all,

I would like to make some user defined functions in Excel vba, I have managed to make that functions but problem is that they dont show help in entering data like excel own functions show.

Like when we enter =Vlookup( in excel then it start showing help menu which show Lookup_value,Table_array,Col_index etc which make easy for user to enter data as per function's requirement. but in User Defined function excel dot show any help like that which make very odd to remember each argument that which was for which purpose....

Could any one help me about that ?

Regards

Wahmed

Try this out. Open the VBA screen then dispaly the immediate window (use Ctrl + G if not visible) then add this into the immedaite window and press Enter, Replace the Function name in Red First. Then go to the Excel front end select to use the Function and you should see the description..

Application.MacroOptions Macro:="nodupsArray", Description:="This is my UDF and it's really good!"

 

[CharlesChuckieCharles]

Whats not working
I take it you have compiled your routines and saved your spreadsheet?

 

[wahmed]

Dear Charles,

Here is Copy of a simple function I am doing.
===================

Public Function DATEDIFF(start_date As Date, End_Date As Date) As Double

DATEDIFF = (End_Date - start_date)

End Function

====================

I want that in Excel when I enter formula as =DATEDIFF( then it automatically show small help menu for (Start_Date,End_Date) which make simple for user which input function required...

thats it.

regards

Wahmed

 

[MrKowz]

Sadly, this is not an option that you can use with VBA. See http://www.mrexcel.com/forum/showthread.php?t=459987&highlight=user+defined+function+intellisense

 

[CharlesChuckieCharles]

I think the real issue is that function DATEDIFF in a built in excel function CHANGE the Name if you are using 2007 you do get a menu BUT YOUR OWN FUNCTION Cannot be called DATEDIFF

 

[Trevor G]

Sadly, this is not an option that you can use with VBA. See http://www.mrexcel.com/forum/showthread.php?t=459987&highlight=user+defined+function+intellisense

Please read what I have suggested as you can add a description, it remains in view whilst working with the Function, so you can add enough text to cover your function needs.

 

[wahmed]

Dear Charles,

Lets change function name to DATECOUNT but you still will not get results required.

regards

wahmed