(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:


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)
        If (Len(valueText) > start) Then
            abschnitt = Left(valueText, Len(valueText) - start)
            length = Len(valueText) - start
        End If
    End If

        If (Int(Left(abschnitt, 1)) = 1) Then
            DecodeVal = DecodeVal * 2   1
            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?


Leave a Reply

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