How to Create UDF

[sg2209]

Dear Mentors/Friends
Hope You are doing good
Though i have watched so many videos and gone though several post to create formula to excel udf ( User Defined Function) that i can use it easily however i am not sure how to prepare it.

Here is the formula

E2 =IF(COUNTIF(A22,"Yes"),"Rule"&SUBSTITUTE(TRIM(IF(A2="Yes","1 ","")&IF(B2="Yes","2 ","")&IF(C2="Yes","3 ","")&IF(D2="Yes","4",""))," ",","),"")



Also is it possible to select the ranges though input box
Just started learning excel and vba not very much sure how to do it .

Thanks for you efforts

[jolivanes]

Can't answer your first request but this should give you an idea for your InputBox question.

Sub Select_With_InputBox()
    Dim Rng As Range
    On Error GoTo Canceled
    Set Rng = Application.InputBox(Prompt:="Select a Range with the mouse." & vbLf & "For multiple Ranges, hold the ""Ctrl"" Button down.", Title:="Select Range(s)", Type:=8)
    'Do something like clearing the selection(s)
    Rng.ClearContents
Canceled:
End Sub

[p45cal]

Hope You are doing good

I always try to do good when I can, although I wouldn't quite put myself in the category of do-gooders. In fact I can be downright wicked at times.
I am, however, quite well.

to create formula to excel udf ( User Defined Function) that i can use it easily however i am not sure how to prepare it.

It would be good if you could supply a workbook to give us a better idea of how you're going to use it.
Depending on your version of Excel this UDF might do it for you:

Function WhatRules1(rng1, rng2)
WhatRules1 = Evaluate("IF(COUNTIF(" & rng1.Address(external:=True) & ",""Yes""),""Rule "" & TEXTJOIN("","",TRUE,FILTER({1,2,3,4}," & rng2.Address(external:=True) & "=""Yes"",""none"")),"""")")
End Function

and you'd use it like this:
2021-02-01_095102.png

If your version of Excel is older, then the above won't work, but this one might:

Function WhatRules2(rng1, rng2)
If Evaluate("COUNTIF(" & rng1.Address(external:=True) & ",""Yes"")") Then
  Count = 0
  For Each cll In rng2.Cells
    Count = Count + 1
    If cll.Value = "Yes" Then wr2 = wr2 & Count & ","
  Next cll
  If Len(wr2) > 0 Then wr2 = "Rules " & Left(wr2, Len(wr2) - 1) Else wr2 = "No rules"
  WhatRules2 = wr2
Else
  WhatRules2 = "Not Applicable"
End If
End Function

used in exactly the same way as the first UDF (except it's called WhatRules2.
Both of these UDFs assume a sequence 1,2,3,4, which you may not want. This is why it's better to provide a workbook and a bit more explanation of how you want it to work.

Also is it possible to select the ranges though input box

All UDFs when used on a worksheet will allow you to select ranges with the mouse or type the references in.

[SamT]

I do not recommend using InputBoxes in UDFs, because, if you use that UDF in many places, you will need to input the Ranges for all cells using that UDF every time the sheet calculates... And there is no easy way to tell which Cell you need to reenter the ranges for.

[jolivanes]

I thought that the InputBox question was separate from the UDF question but maybe not.

[sg2209]

Thank You for the response, appreciate your efforts
Let me check and will let you know

[jolivanes]

Re: "Let me check and will let you know."
Are you doing this for a customer?

[sg2209]

No i am using this for my self not for any customer, First thing i will understand the logic to learn something new and i can ask my subboridnate to do it this can reduce my work

[sg2209]

I tried both the code unfortunately getting an error #Value.

Added file Dummy here, please review

[p45cal]

See attached.

[Paul_Hossler]

See attached.

I'd suggest a couple of changes

Function WhatRules2(rng1 As Range, rng2 As Range) As Variant
    Dim i As Long
    Dim s As String
    
    WhatRules2 = CVErr(xlErrNA)


    For i = 1 To rng1.Columns.Count - 1 Step 2
        If rng1.Cells(1, i).Value = "Yes" Then s = s & ", " & rng2.Cells(1, i).Value
    Next i


    If Len(s) > 0 Then WhatRules2 = Mid(s, 3)
End Function

[sg2209]

Thank You so much p45cal & Paul_Hossler.

Worked like a charm, really appreciate your efforts thanks a ton again

Thank You jolivanes

[sg2209]

Thank You so much everyone