Excel: single #VALUE error when using UDF

Question

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.

EDIT:

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?

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

score 1 · Accepted Answer · answered Mar 19 '20 at 12:30

I recommend to declare the variables properly and don't use Value as variable name as it can easily get mixed up with Range().Value. Also I recommend always use Long as there is no benefit in using Integer in VBA.

Finally there seems to be some issue with valueText = value.Text. If you use the .Text of a cell/range object it could interfere with the numberformat that was set to that cell. I recommend to use ValueText = CStr(ValueCell.Value) instead.

So you end up with something like:

Option Explicit

Public Function DecodeVal(ValueCell As Range, Start As Long, Length As Long) As Long
    Dim Abschnitt As String
    Dim i As Long
    Dim ValueText As String

    ValueText = CStr(ValueCell.Value)
    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 (CLng(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

If that doesn't solve the issue, then your issue is not related to that code but to how you generate the binary and write that into the cell (or if it is a formula then the issue is in the code of that UDF).

Excel: single #VALUE error when using UDF

1 Answers1