Custom function not being able to record correct cell values - RANDOMLY???

[HStro]

Hi all!

I have a custom function running, which is very simple. All it does is take in a couple number and assess if the 2nd number passes a threshold with the 1st being under that threshold.

The function works so is not the problem. The issue however is that for some input values, namely 14.2% for the 1st number and 15.2% as the 2nd number and 15% being the threshold, the function seems to record 0 for the 1st number and 0.05 for the 2nd.

This is weird because for values of say 12% and 13% or 9% and 10% everything works exactly fine. It is just some specific numbers where the input seems to be taken in wrong...

Any ideas? I have never seen something like this. When debugging it is clear that the problem is with the actual initial input as opposed to anything in the code.

Thanks for the help!
Harry

[westconn1]

post the code for the function
and samples of results for values

[HStro]

Here is the code, I didn't think it would be that relevant as I figured this may be an excel glitch, but of course it helps.

Public Concatenate As String

Public Function Check(Value0 As Double, Value1 As Double, Value2 As Double, Value3 As Double, List As Range)
    'Value0 is the starting value i.e. %13.2
    'Value1 is the finishing value i.e. %14.1
    'Value2 is the starting value's lower integer limit - %13
    'Value3 is the starting value's upper integer limit - 14%
    Dim totalPos As Double
    Dim intTotal As Long
    Dim roundedUp As Double
    Dim roundedDown As Double
    Dim Thresholds() As Variant
    Dim element As Variant
    Dim startPos As Double
    Dim i As Integer
    Concatenate = vbNullString 'resetting string variable to null
    startPos = Value0 * 100
    totalPos = Value1 * 100
    
    intTotal = Int(totalPos)
    roundedDown = Value2 * 100 'lower bound of start pos
    roundedUp = Value3 * 100    'upper bound of start pos
    
    Check = "" 'init
    
    If intTotal >= roundedUp Then
        If startPos < 5 And intTotal >= 5 Then
            Check = Check & " 5"
        End If
        aboveTenCheck intTotal, startPos
        Check = Check + Concatenate
                        
    ElseIf intTotal <= roundedDown Then
        If startPos >= 5 And intTotal < 5 Then
            Check = Check & " 5"
        End If
        aboveTenCheck intTotal, startPos
        Check = Check + Concatenate
    End If
    
    If Check = 0 Or Check = "" Then
        Check = "N/A" 'in the event that no action is needed
    End If
         
End Function
    
Public Sub aboveTenCheck(finalPosition, startingPosition)
    For i = 10 To 29
        If startingPosition < i And finalPosition >= i Then
            Concatenate = Concatenate & " " & i
        ElseIf startingPosition >= i And finalPosition < i Then
            Concatenate = Concatenate & " " & i
        End If
    Next i
End Sub

I can't show the exact output but I added in examples of what the input values would be. Hope this enough info..!

[Bob Phillips]

What is List? A workbook would be far better, save us second-guessing.

[SamT]

Concatenate is an Excel Function, Value2 is a VBA Range Property, List and i are not used in the Function. intTotal as a long, when the "int" prefix indicates that the variable is Typed as an Integer.

The function works so is not the problem. The issue however is that for some input values, namely 14.2% for the 1st number and 15.2% as the 2nd number and 15% being the threshold, the function seems to record 0 for the 1st number and 0.05 for the 2nd.

roundedDown = Value2 * 100 'lower bound of start pos
    roundedUp = Value3 * 100 'upper bound of start pos

Are you certain that Value3 is not a decimal value larger than 0.15

[westconn1]

If intTotal >= roundedUp Then

even though, with the supplied values both variables = 14
one is a long and the other a double and they are not comparable,
try like

If intTotal >= clng(roundedUp) Then

the function will then produce a numeric result, up to you to check if it is correct

from immediate window

?roundedup
14
?roundedup = 14
False
?clng(roundedup) = 14
True

[SamT]

@ Westconn,

I could not get such a failure in Excel 2002(XP.) LongVar <> IntegerVar <> DoubleVar when all = 14

Placed Watch on A, X, Y, and Z. Z is True in all conditions

Sub Test()
Dim A As Integer
Dim X As Long
Dim Y As Double
Dim Z As Boolean

A = WorksheetFunction.RoundUp(13.6, 1)
X = 14
Y = 14.1 - 0.1
Z = Y = 14
Z = A = 14
Z = X = 14

Z = X = A
Z = X = Y
Z = A = Y
End Sub

[westconn1]

@ sam
probably depends how the double is calculated
i used the calculations from within the posted function

?.14 *100 = 14
False

the above implicitly or if explicitly converts to double

[snb]

The use of variable names that can interfere (e.g. 'concatenate') with 'reserved' names in VBA or Excel might create unpredictable results.

[SamT]

Well, we've given the Harry three good suggestions so far. I hope he shares the solution with us.

[HStro]

Hi Guys! I had no idea there were any replies past xld's on the 16th! Sorry about that!
In the mean time I have been checking to see what I can post up here, as the workbook is for work, not personal use.

I have tried a few of the solutions that you have generously provided. It seems as though the clng(roundedUp) did the trick. Why is that, what was wrong with having it as a double?

IntTotal is an integer, so apparently that can be compared to a long but not a double?

Apologies for not checking the site in a while! And thank you for your interest.
Harry

[SamT]

VBA is only "pretty good" about guessing Type conversions. Clng() removes all doubt.

As a programming style, I (almost) always use the Type conversion functions in order to "remove all doubt."

[westconn1]

Why is that, what was wrong with having it as a double?

it is to do with the way doubles are calculated, even 2 doubles that you can see have to be equal, vb may not agree

?cdbl(.14*100)= cdbl(15-1)
False
?(.14*100)-14
1.33226762955019E-15

i am sure that the difference is a very small number (0.0000000000000013322676295502)

there are several way to force significant equality, one way is to limit the number of decimal places using round function, which clng also does (in this case) by removing all decimal places

a single would also work correctly in this particular case

[HStro]

Ok I get the picture. In the future I will pay more attention regarding the types I am comparing between.
Thank you all so much!