CODEWITHSUNDEEP
excelvbaexcel-formula
SHAIK IRFAN KAREEM ALI
SHAIK IRFAN KAREEM ALI

Reputation: 1

Why is my User defined Function not showing results VBA

I have created a UDF called YRatio(Str). When I use the insert formula for a cell the result appears on the dialog box but inside the cell it shows "=YRatio(C2)"

The result I get is right. But it doesn't appear in the cells.

Function YRatio(CellRef As String) As String
Dim Arr() As String
Dim tot As Integer
Dim yyes As Integer
Dim Str As String

Str = CellRef '.Value

Arr() = Split(Str, Chr(10))

For Each Line In Arr
    tot = tot + 1
    If Left(Line, 1) = "Y" Then
        yyes = yyes + 1
    End If
Next
YRatio = CStr(yyes) & "/" & CStr(tot)
End Function

Upvotes: 0

Views: 870

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

inside the cell it shows "=YRatio(C2)"

You need to change the formatting of the cell to General. Currently it is formatted as Text

Also Arr() = Split(Str, Chr(10)) in your code should be Arr() = Split(Range(Str).Value, Chr(10)).

Imp Tip: Avoid using LINE and STR as variables. Use more meaningful names.

Your code can be written as

Option Explicit

Function YRatio(CellRef As Range) As String
    Dim Arr() As String
    Dim tot As Long
    Dim yyes As Long
    Dim itm As Variant
    
    Arr() = Split(CellRef.Value2, Chr(10))
    
    For Each itm In Arr
        tot = tot + 1
        If Left(itm, 1) = "Y" Then yyes = yyes + 1
    Next
    YRatio = yyes & "/" & tot
End Function

Here I am passing the cell as Range.

Now you can type in the cell =YRatio(C2) which is formatted as General and it will work.

You can use use this version of the code which uses UBound(Arr) to get the total elements of the array rather then using tot = tot + 1

Option Explicit

Function YRatio(CellRef As Range) As String
    Dim Arr() As String
    Dim yyes As Long
    Dim itm As Variant
    
    Arr() = Split(CellRef.Value2, Chr(10))
    
    For Each itm In Arr
        If Left(itm, 1) = "Y" Then yyes = yyes + 1
    Next
    YRatio = yyes & "/" & UBound(Arr)
End Function

Upvotes: 1

Related Questions