• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

How to add prompts to user defined function?

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
0
Alex Campbell
Asked:
Alex Campbell
1 Solution
 
Ryan ChongCommented:
try customize:

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

Open in new window

0
 
ProfessorJimJamCommented:
I don't think what you are asking is possible to be done with user defined functions.
0
 
Martin LissRetired ProgrammerCommented:
Yes it is possible. Please see my Creating your own Excel formulas and doing the impossible article.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Alex CampbellAuthor Commented:
Great, thanks!!
0
 
Martin LissRetired ProgrammerCommented:
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
0
 
ProfessorJimJamCommented:
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/
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now