excel - 视觉基础。错误返回 1

Question

我找到了一个名为 FindNum 的可视化基本代码，它将为您提供包含文本和数字的字符串的数字。

当字符串中实际上不包含任何数字时，我真的希望该函数返回一个数字。 (我已经对我的想法做了一个模型)。
http://imgur.com/LuP0KNN

这是视觉基本代码。如果有人可以提供帮助，我将不胜感激。

Function FindNum(parameter, Optional ignore As Variant, Optional side As Variant) As Double
Dim n
Dim i
Dim p
i = 0
If IsMissing(ignore) = True Then
    p = parameter
Else

    If side = 1 Then
        p = Right(parameter, (Len(parameter) - Len(ignore)))

    ElseIf side = 0 Then
        p = Left(parameter, (Len(parameter) - Len(ignore)))
    Else
        p = parameter

    End If
End If
If IsNumeric(Left(p, (Len(p) - (Len(p) - 1)))) = True Then
        Do
                n = Left(p, (Len(p) - i))
                i = i + 1
        Loop Until IsNumeric(n) = True
        FindNum = n
Else

        Do
                n = Right(p, (Len(p) - i))
                i = i + 1
        Loop Until IsNumeric(n) = True
        FindNum = n
End If
End Function

Answer 1

我做了一个类似的功能。它可以从左到右或从右到左读取，如果您愿意，可以将“ertdfgcvb”设置为小数点分隔符。

Function FindNum2(num As String, Optional DecimalSeparator As String, Optional FromRight As Boolean) As Double
' text where the number is   | string that works as decimal sep | who the hell wants to read from right to left? if you do, this one's for you
If Len(num) = 0 Then Exit Function
Dim x As String, y As String, DefaultValue As Double
DefaultValue = 1 'here's your default

num = Replace(num, DecimalSeparator, ".")

For i = IIf(FromRight, Len(num), 1) To IIf(FromRight, 1, Len(num)) Step IIf(FromRight, -1, 1)
        y = Mid(num, i, 1) 'the current character
    If y Like "#" Then 'if it's a number
        x = x & y 'then append it
    ElseIf y = "." Then 'if it's a decimal separator
        x = x & y 'then append a decimal separator; if there are multiple of it then tough luck
    End If
Next i
If x = "" Then
    FindNum2 = DefaultValue
Else
    FindNum2 = Val(x) 'converts the string to a double
End If
End Function