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.
CodePudding user response:
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.
CodePudding user response:
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