(Solved) – Excel: strange #VALUE error behaviour when using UDF

(solved)-–-excel:-strange-#value-error-behaviour-when-using-udf

I know the topic “Excel formulas not updating” has been discussed a lot on many forums but I haven’t found a useful solution to my problem.

In a worksheet, I am using built-in Excel formulas as well as own functions written with VBA in the module of the worksheet and I am referencing them within the worksheet.

There is a binary code which gets generated from a hexadecimal code in cell A1. The binary code gets calculated in cell B1.

Let’s take following code as an example: 100001101110

Cell C1 contains following:

=DecodeVal(B1;0;20)

If I now paste a hex code into A1 and the binary code gets created in B1, cell C1 is displaying an #VALUE! error.

If I go back to cell A1, click in the textbox and press enter again, the correct value (= 2158) gets displayed.

Why is there a Value error at first, but not if I press enter one more time?

If I paste the binary code directly as text, there is no error at all.

This is the function I’m referring to:

Public Function DecodeVal(value, start As Integer, length As Integer) As Long
Dim abschnitt As String
Dim i As Integer
Dim valueText As String

    valueText = value.Text
    If (Len(valueText) - start - length   1 > 0) Then
        abschnitt = Mid(valueText, Len(valueText) - start - length   1, length)
    Else
        If (Len(valueText) > start) Then
            abschnitt = Left(valueText, Len(valueText) - start)
            length = Len(valueText) - start
        End If
    End If

    Do
        If (Int(Left(abschnitt, 1)) = 1) Then
            DecodeVal = DecodeVal * 2   1
        Else
            DecodeVal = DecodeVal * 2
        End If
        abschnitt = Right(abschnitt, length - 1)
        length = length - 1
    Loop While length > 0


End Function

Yes, calculation options are set to automatic.

Any suggestions?

Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *