Creating a user defined Formula with multiple optional entries

M

mahhdy

Board Regular
Joined
Sep 15, 2016
Messages
86
Hello,

I need to develop an UDF for checking a cell value against several possible entries to check if the cell is equal to any of those values or not. I know how to do the check (I am thinking to assign the entries to a scripting dictionary and do a dic.exists function). But writing the function in a way that can accept multiple optional entries in not clear for me. The example in EXCEL functions is CONCATENATE( text1, [ text2, ... text_n ] ). My function for specific optional entries is as follow:

Code: 
Function IfAmong(TextToCheck As String, Text1 As String, Optional Text2 As String, Optional Text3 As String, Optional Text4 As String, Optional Text5 As String, Optional text6 As String) As Boolean    Dim dd As New Scripting.Dictionary
    dd.CompareMode = TextCompare
    dd.Add Text1, dd.Count
    If Text2 <> "" Then dd.Add Text2, dd.Count
    If Text3 <> "" Then dd.Add Text3, dd.Count
    If Text4 <> "" Then dd.Add Text4, dd.Count
    If Text5 <> "" Then dd.Add Text5, dd.Count
    If text6 <> "" Then dd.Add text6, dd.Count
    IfAmong = dd.Exists(TextToCheck)
    dd.RemoveAll
End Function

I want to make it dynamic for number of optional entries (As said like concatenate). and also if possible make the checking of entries automatic through a loop.

Code: 
for i =2 to Ubound(text(i))
     if text(i) <>"" then..........
next

I was not able to do that as well

Thanks and regards,
M
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Hello,

I found the solution with help of stackoverflow fellas. Here is the reply.

Use the <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">ParamArray</code>. The <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">ParamArray</code> must always be the last thing to be declared and must be of the type <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">Variant</code>. It will allow you to enter as many variables as you like without having to define each one of them.
Click to expand...
https://stackoverflow.com/questions...fined-function-in-vba-excel/46075900#46075900

Code: 
[COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit] IfAmong[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]TextToCheck[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#101094][FONT=inherit]ParamArray[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Text[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Boolean[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txt [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Variant
[/FONT][/COLOR][COLOR=#303336][FONT=inherit]    on error resume next
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] dd [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]New[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Scripting[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Dictionary
    dd[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]CompareMode [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] TextCompare

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txt [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Text[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Not[/FONT][/COLOR][COLOR=#303336][FONT=inherit] txt [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] vbNullString [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit] dd[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Add Key[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]txt[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Item[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]dd[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    IfAmong [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] dd[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Exists[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]TextToCheck[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    dd[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]RemoveAll [/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR]

It is not so good routine as will turn error if duplicated value entered! you can find a more simple suggestion on link above.

Regards,
M

mahhdy said:
Hello,

I need to develop an UDF for checking a cell value against several possible entries to check if the cell is equal to any of those values or not. I know how to do the check (I am thinking to assign the entries to a scripting dictionary and do a dic.exists function). But writing the function in a way that can accept multiple optional entries in not clear for me. The example in EXCEL functions is CONCATENATE( text1, [ text2, ... text_n ] ). My function for specific optional entries is as follow:

Code: 
Function IfAmong(TextToCheck As String, Text1 As String, Optional Text2 As String, Optional Text3 As String, Optional Text4 As String, Optional Text5 As String, Optional text6 As String) As Boolean    Dim dd As New Scripting.Dictionary
    dd.CompareMode = TextCompare
    dd.Add Text1, dd.Count
    If Text2 <> "" Then dd.Add Text2, dd.Count
    If Text3 <> "" Then dd.Add Text3, dd.Count
    If Text4 <> "" Then dd.Add Text4, dd.Count
    If Text5 <> "" Then dd.Add Text5, dd.Count
    If text6 <> "" Then dd.Add text6, dd.Count
    IfAmong = dd.Exists(TextToCheck)
    dd.RemoveAll
End Function

I want to make it dynamic for number of optional entries (As said like concatenate). and also if possible make the checking of entries automatic through a loop.

Code: 
for i =2 to Ubound(text(i))
     if text(i) <>"" then..........
next

I was not able to do that as well

Thanks and regards,
M
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
265
Micron
M
D
  • Question Question
Create running data table from daily table updates in another closed workbook
Replies
1
Views
691
ddander54
D
R
  • Question Question
Create Empty Elements in a TEXTJOIN function
Replies
4
Views
405
Eric W
Eric W
P
  • Solved
Creating a Train Time Table
2 3
Replies
29
Views
4K
I_know_nuffin
I
C
  • Question Question
Data Entry Form : VBA Run-time Error '91': Object Variable not set with ws.cells.find function
Replies
3
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top