We help IT Professionals succeed at work.

How to add prompts to user defined function?

Alex Campbell
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

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.
"There is still no cure for the common birthday." ~John Glenn
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.

Author

Commented:
Great, thanks!!
Martin Liss"There is still no cure for the common birthday." ~John Glenn
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/