Excel VBA code exits unexpectedly, certainly related to: "a function call on the left side of assignment should return Variant or Object"

Question

Here is my code:

Public Function get_best(cell As Variant, Optional threshold = 0.5) As String
    Dim res As String
    Dim Rate As Double
    Lines = Split(cell.Value, vbCr)
    res = ""
    Tag = "[C : "
    For i = LBound(Lines, 1) To UBound(Lines, 1)
        pos = InStr(Lines(i), Tag)
        If pos > 0 Then
            pos1 = pos + Len(Tag) + 1
            pos2 = InStr(pos1, Lines(i), "%]")
            sRate = Mid(Lines(i), pos1, pos2 - pos1)
            Rate = CDec(sRate)
            If Rate >= threshold Then
                res = res & Lines(i) & vbCrLf
            End If
        End If
    Next i
    get_best = res
End Function

Here is an example of cell on which to apply it, say A1:

[C : 5.1%] azerty
    aaa bbb ccc 
[C : 0.2%] qwerty
    ddd eee fff

Then a call:

= get_best(A1)

Expected result:

[C : 5.1%] azerty

What it does is:

• Parse the lines in the cell passed as argument
• Throw all lines that do not contain the tag
• For the lines containing the tag, returns the ones where the percentage value after the tag is superior to the threshold passed as argument

It fails:

• With debugger, I can see it silently exit function at line Rate = CDec(sRate)
• If I suppress Dim Rate As Double then I have the error (translated from french): compilation error: a function call on the left side of assignment should return Variant or Object

I don't understand this error.

Answer 1

Your first issue is that your variable pos1 is off by 1, therefore sRate gets .1 instead of 5.1. The function CDec doesn't like that and throws an error that it cannot convert this to a Double. If you correct that, it will work.

Your second issue is that Rate is a build-in function in VBA. When you declare you variable rate as Double, you will hide the function and the VBA runtime knows that you want to use a variable. If you don't define it, it will assume you are accessing the function, and you can't assing a value to a function, therefore the compiler error. But if you fix this, you will still get the conversion error.

Minor issue: You will likely need to split by vbLf, not by vbCr.

You should, by the way, use Option Explicit and declare all your variables.

Answer 2

The main problem was a locale one: The input data for CDEC shall use the same decimal separator than the one of your locale e.g. if in your locale the decimal separator is a comma ',' and if your input data uses the dot '.' then you have to convert your data with Replace(sRate, ".", ",").

Final code is:

Public Function get_best(cell As Variant, Optional threshold = 0.5) As String
    Dim res, sRate As String
    Dim fRate As Double
    Lines = Split(cell.Value, vbLf)
    res = ""
    Tag = "[Couverture : "
    For i = LBound(Lines, 1) To UBound(Lines, 1)
        pos = InStr(Lines(i), Tag)
        If pos > 0 Then
            pos1 = pos + Len(Tag) + 1
            pos2 = InStr(pos1, Lines(i), "%]")
            sRate = Mid(Lines(i), pos1, pos2 - pos1)
            fRate = CDbl(Replace(sRate, ".", ","))
            If fRate >= threshold Then
                res = res & Lines(i) & vbLf
            End If
        End If
    Next i
    get_best = res
End Function