Solved run-time error '6' Overflow

[VasiuF]

Howdy,
I have a problem with a calculation in which I have a value of zero
can you help me with some advice to solve the situation?
I also attached a print screen
dim m_suma1..m_suma4, m_nr1 ... m_nr4 as long
thanks

 

[Deleted member 147267]

None of that helps much - can't even tell which line raises the error.
However, when you don't provide a "value if Null" parameter for Nz, you get a Null (or possibly a null string) as a result. No point in using it if you don't provide the value if null cases. So you might be trying to multiply Null by 100 and divide null by null.

 

[VasiuF]

It's about m_suma4, you can see in watch window

 

[Gasman]

It's about m_suma4, you can see in watch window

Also m_nrm4 ?

They both have zero, but it would help if you did not have breakpoints on all the lines? normally the error would be higlighted.
You have 0/0 * 100 /100

So you need to decide what you want to do when that happens.?

Perhaps test for both and if both not zero then do the calculation.?, else just set to zero.?

 

[VasiuF]

of course I marked the error, but I stopped running for the print screen

 

[VasiuF]

next m_mediaan in which I have to do another calculation with all 4: m_medias1 .... m_medias4 and m_nr1 ... m_nr4
next line of code

 

[Gasman]

Personally I cannot see how any of those variables could be Null?
Have they been Dimmed correctly? They are not controls are they.?

 

[VasiuF]

I just try with nz function bat initially was not

 

[Uncle Gizmo]

It looks to me like you are dividing 0 by 0 which could be causing your problem.

See:-

 

[VasiuF]

You are right, 0 by 0 can be any variable in the sum_1 ... 4

 

[Cronk]

In #1, you have by default declared m_suma1,m_suma4, m_nr1 (ie all but the last variable) as variants which can hold nulls and give 0 when used with the nz function.

If you want them all as longs, then the statement needs to be
dim m_suma1 as long,m_suma4 as long, m_nr1 as long, m_nr4 as long

But you would still have a problem because you cannot set a long variable = null.

You can trap for the error or test that variables contain non zero/non null and if so, skip the line so that no error occurs.

 

[arnelgp]

shortcut, cuts life short.
evaluate each expression if 0:

m_suma1 = nz(m_suma1, 0)
m_suma2 = nz(m_suma2, 0)
m_suma3 = nz(m_suma3, 0)
m_suma4 = nz(m_suma4, 0)

m_nr1 = nz(m_nr1, 0)
m_nr2 = nz(m_nr2, 0)
m_nr3 = nz(m_nr3, 0)
m_nr4 = nz(m_nr4, 0)

m_medias1 = 0
m_medias2 = 0
m_medias3 = 0
m_medias4 = 0

m_mediaan = 0
m_rr = 0

if m_suma1 <> 0 and m_nr1 <> 0 then
    m_medias1 = int(m_suma1 / m_nr1 * 100) / 100

    m_mediaan = m_mediaan + m_suma1
    m_rr = m_rr + m_nr1
end if

if m_suma2 <> 0 and m_nr2 <> 0 then
    m_medias2 = int(m_suma2 / m_nr2 * 100) / 100

    m_mediaan = m_mediaan + m_suma2
    m_rr = m_rr + m_nr2
end if

if m_suma3 <> 0 and m_nr3 <> 0 then
    m_medias3 = int(m_suma3 / m_nr3 * 100) / 100

    m_mediaan = m_mediaan + m_suma3
    m_rr = m_rr + m_nr3
end if

if m_suma4 <> 0 and m_nr4 <> 0 then
    m_medias4 = int(m_suma4 / m_nr4 * 100) / 100

    m_mediaan = m_mediaan + m_suma4
    m_rr = m_rr + m_nr4
end if

'final
if m_mediaan <> 0 and m_rr <> 0 then
    m_mediaan = int(m_mediaan / m_rr * 100) / 100

end if

 

[Cronk]

NB if using arnelgp's code, there should be an NZ wrapped around the various rs1 fields in the loop shown in one of the attachments in #1 in case any of the values is null.

 

[VasiuF]

excellent!
the solution you gave me arnelgp , works perfectly
Thank you all for the promptness and advice given that really helped me