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
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.