PDA

View Full Version : [SOLVED] Custom function not being able to record correct cell values - RANDOMLY???



HStro
10-14-2014, 11:28 AM
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
10-14-2014, 01:28 PM
post the code for the function
and samples of results for values

HStro
10-16-2014, 12:12 AM
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
10-16-2014, 12:46 AM
What is List? A workbook would be far better, save us second-guessing.

SamT
10-16-2014, 08:31 AM
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. :dunno


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
10-16-2014, 01:45 PM
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
10-16-2014, 03:14 PM
@ 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
10-17-2014, 02:13 AM
@ sam
probably depends how the double is calculated
i used the calculations from within the posted function


?.14 *100 = 14
Falsethe above implicitly or if explicitly converts to double

snb
10-17-2014, 02:46 AM
The use of variable names that can interfere (e.g. 'concatenate') with 'reserved' names in VBA or Excel might create unpredictable results.

SamT
10-17-2014, 05:00 AM
Well, we've given the Harry three good suggestions so far. I hope he shares the solution with us.

HStro
10-20-2014, 12:03 PM
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
10-20-2014, 12:20 PM
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
10-20-2014, 01:27 PM
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
10-21-2014, 01:38 AM
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!