How to add prompts to user defined function?

Alex Campbell
Alex Campbell used Ask the Experts™
on
I have added the Count_Chars user defined function to my spreadsheets (see below).
I would like to have prompt or help information provided as in in the standard IF function:

Prompts for IF function
---------------------------------------------------------------------------------
Function Count_Chars(Target As String, rng As Range)
   Count = 0
   If Target = "" Then GoTo Done
    For Each c In rng
      N = InStr(1, c.Value, Target)
      While N <> 0
       Count = Count + 1
       N = InStr(N + 1, c.Value, Target)
      Wend
    Next c
  Count_Chars = Count
 Done:
 End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
try customize:

=IF(Count_Chars("B",A:A) >= 5, "5 or more chars matched", "less than 5 chars matched")

Open in new window

Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
I don't think what you are asking is possible to be done with user defined functions.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Yes it is possible. Please see my Creating your own Excel formulas and doing the impossible article.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
Great, thanks!!
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome and I'm glad I was able to help. And a special thanks for my 4 millionth point!

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Alex
I am glad that martin's solution has helped you but from what I understood from your question , you wanted to add intellisense
In the UDFs as you can see them in the built in functions. As I confirmed in my message above it is currently not possible to do that however there is a workaround using
Add-in referred in the link below
https://fastexcel.wordpress.com/2016/10/07/writing-efficient-vba-udfs-part-15-adding-intellisense-to-your-udfs/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial