Solved

How to add prompts to user defined function?

Posted on 2016-10-07
6
47 Views
Last Modified: 2016-10-07
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
Comment
Question by:Alex972
6 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41833870
try customize:

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

Open in new window

0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41833871
I don't think what you are asking is possible to be done with user defined functions.
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41833886
Yes it is possible. Please see my Creating your own Excel formulas and doing the impossible article.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:Alex972
ID: 41833904
Great, thanks!!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41833908
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41834697
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Vb.net dynamic formulas in runtime 11 61
VBA MS Word "Table of Contents" extractor` 4 22
Excel VBA password protection 2 22
can't find file error on web browser 1 13
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now