Excel: strange #VALUE error behaviour 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.

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

When you paste 100001101110 in the cell, what it display? How the cell is formatted? — FaneDuru
– FaneDuru, Commented Mar 19, 2020 at 12:13
Try please putting a Break point on the line valueText = value.Text and see what value.Text shows, in both situations. When it works as expected and when not... I suppose that here would be the key of the problem. — FaneDuru
– FaneDuru, Commented Mar 19, 2020 at 12:15
Try changing, valueText = value.Text to valueText = value.Value2 and then test the outcome. — shrivallabha.redij
– shrivallabha.redij, Commented Mar 19, 2020 at 12:30
Side note: If you're passing value as a Range... then be explicit about that ByVal value as Range. — BigBen
– BigBen, Commented Mar 19, 2020 at 12:41

shrivallabha.redij · Accepted Answer · 2020-03-19 12:42:01Z

2

Using Range.Text is not recommended practice. This will particularly cause errors when the input column has less width than data and cell is displaying ######. If the formula is calculated then it will return #VALUE error. See the snapshot below.

If you adjust the column width and recalculate it will then show the correct result.

To get around this, use either valueText = value.Value2 or valueText = value.Value instead of valueText = value.Text.

Also, I'd suggest using something like RngValue as a declared variable/argument instead of value which is a Range property as well (as it may result in conflicts).

answered Mar 19, 2020 at 12:42

shrivallabha.redij

5,9001 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BigBen Over a year ago

I'm assuming Range.Text is indeed the issue... but wondering if OP is using it since the source cell may have number formatting to preserve leading zero(s). In any case there should be a workaround that doesn't use .Text.

Clemens Ambros Over a year ago

you are right, this was the issue. Additionally, I added CStr

shrivallabha.redij Over a year ago

@BigBen if you format the cell as number format then the displayed text becomes as I have demonstrated in the first snapshot and the error occurs.

FaneDuru · Accepted Answer · 2020-03-19 13:27:38Z

1

The next two things would solve your problem:

Change

Public Function DecodeVal(value, start As Integer, length As Integer) As Long

with

   Public Function DecodeVal(val As Range, start As Integer, length As Integer) As Long

and use valueText = val.value

Be also sure that B1 is formatted as Text. The best way of formatting would be TextToColumns. Manually (from Data Tab) or in code:

Dim sh As Worksheet, rng As Range Set sh = ActiveSheet Set rng = sh.Range("B1:B10") rng.TextToColumns Destination:=rng, fieldInfo:=Array(1, 2)

Otherwise, Excel guess can be a scientific format...

edited Mar 19, 2020 at 13:27

answered Mar 19, 2020 at 12:53

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Comments

Gary's Student · Accepted Answer · 2020-03-19 12:53:14Z

1

Within your Do loop, you are using DecodeVal in calculations, but never assigned it an initial value.

(there may be other errors)

answered Mar 19, 2020 at 12:53

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Excel: strange #VALUE error behaviour when using UDF

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related