Macro on VBA get stucked on values under 1

Question

I have this macro:

Sub SolverPaulo()

    Dim listo As Boolean
    Dim min As Single
    Dim max As Single
    Dim beta As Single
    Dim tolerancia As Single
    Dim deficit As Single
    Dim prom As Single

    listo = False
    min = 0
    max = 1
    beta = 0
    tolerancia = 0.1

    While listo = False
        prom = (min + max) / 2
        Cells(6, "CJ") = prom
        deficit = Cells(6, "CI")

        If (deficit > 0) Then
            If (deficit < tolerancia) Then
                beta = prom
                listo = True
            Else: min = prom
            End If

        Else
            If (Abs(deficit) < tolerancia) Then
                beta = prom
                listo = True
            Else: max = prom
            End If
        End If
    Wend

Application.ScreenUpdating = True
End Sub

If I define tolerancia = 1 or any value higher than 1 the macro works fine, but if I define values like tolerancia = 0.1 or inferior under 1, the macro keeps working there in an infinite loop. I need to use tolerancia = 0.00001

I'm not sure of Single is a correct data type for the vars that I'm using, this is the first time that I work with VBA.

The idea is the macro should stop when deficit becomes minor than the tolerancia

At a certain point, the macro decide this:

If (0.33 < tolerancia)

There's when it crash... if tolerancia is actually minor than 0.33. When I compare tolerancia with numbers higher than 1 there's no problem.

Now, in my Excel file, the configuration is in spanish, so the decimals are separated with , and not with . could be a reason that is failing on do that:

if (0,33 < 0.5)

comparing coma and point?

Any hint?

Answer 1

Ok, the problem was with the value type of the numbers, I had tu use Long, now it works, with Single the deficit would never reach the If case when listo is true (with tolerancia under 0.1). The code is:

Sub SolverPaulo()

    Dim listo As Boolean
    Dim min As Double
    Dim max As Double
    Dim beta As Double
    Dim tolerancia As Double
    Dim deficit As Double
    Dim prom As Double

    listo = False
    min = 0
    max = 1
    beta = 0
    tolerancia = 0.1

    While listo = False
        prom = (min + max) / 2
        Cells(6, "CJ") = prom
        deficit = Cells(6, "CI").Value2

        If (deficit > 0) Then
            If (deficit < tolerancia) Then
                beta = prom
                listo = True
            Else: min = prom
            End If

        Else
            If (Abs(deficit) < tolerancia) Then
                beta = prom
                listo = True
            Else: max = prom
            End If
        End If
    Wend

Application.ScreenUpdating = True
End Sub

A video working:

https://i.imgur.com/qRRXZ4Y.gif

Answer 2

When dealing with floating point values it's best to use floating point when referencing a number, even as a literal, or the assumed data type will be the type entered as a literal. 0 = INT, 0.0 = FLOATING POINT.