Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    24

    Unanswered: VBA newbie - runtime 6 error 'overflow'

    Hi,

    I'm new to VBA and I've been 'trying' to use it recently on one of my reports, I'm using it to create some hand made bar charts and it's been working exactly as needed- the problem is, I occasionally have null values and when I try to run the report in those circumstances it no longer works, instead it gives me a runtime 6 error pointing to my VBA code.

    Please can someone help me adjust this code so that it.

    Here's the code:
    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
            
        BarOcCompFtp.Height = (FtpOcComp / FtpOcTotal) * (1440 * 2)
        BarOcCompFtp.Top = ((FtpOcTotal - FtpOcComp) / FtpOcTotal) * (1440 * 2) + (1440 / 2)
        BarOcImpFtp.Height = (FtpOcImp / FtpOcTotal) * (1440 * 2)
        BarOcImpFtp.Top = ((FtpOcTotal - FtpOcImp) / FtpOcTotal) * (1440 * 2) + (1440 / 2)
        BarOcUnacFtp.Height = (FtpOcUnac / FtpOcTotal) * (1440 * 2)
        BarOcUnacFtp.Top = ((FtpOcTotal - FtpOcUnac) / FtpOcTotal) * (1440 * 2) + (1440 / 2)
    
        BarVcCompFtp.Height = (FtpVcComp / FtpVcTotal) * (1440 * 2)
        BarVcCompFtp.Top = ((FtpVcTotal - FtpVcComp) / FtpVcTotal) * (1440 * 2) + (1440 / 2)
        BarVcImpFtp.Height = (FtpVcImp / FtpVcTotal) * (1440 * 2)
        BarVcImpFtp.Top = ((FtpVcTotal - FtpVcImp) / FtpVcTotal) * (1440 * 2) + (1440 / 2)
        BarVcUnacFtp.Height = (FtpVcUnac / FtpVcTotal) * (1440 * 2)
        BarVcUnacFtp.Top = ((FtpVcTotal - FtpVcUnac) / FtpVcTotal) * (1440 * 2) + (1440 / 2)
    
        BarIbCompFtp.Height = (FtpIbComp / FtpIbTotal) * (1440 * 2)
        BarIbCompFtp.Top = ((FtpIbTotal - FtpIbComp) / FtpIbTotal) * (1440 * 2) + (1440 / 2)
        BarIbImpFtp.Height = (FtpIbImp / FtpIbTotal) * (1440 * 2)
        BarIbImpFtp.Top = ((FtpIbTotal - FtpIbImp) / FtpIbTotal) * (1440 * 2) + (1440 / 2)
        BarIbUnacFtp.Height = (FtpIbUnac / FtpIbTotal) * (1440 * 2)
        BarIbUnacFtp.Top = ((FtpIbTotal - FtpIbUnac) / FtpIbTotal) * (1440 * 2) + (1440 / 2)
    
    
        BarOcCompMs.Height = (MsOcComp / MsOcTotal) * (1440 * 2)
        BarOcCompMs.Top = ((MsOcTotal - MsOcComp) / MsOcTotal) * (1440 * 2) + (1440 * 3)
        BarOcImpMs.Height = (MsOcImp / MsOcTotal) * (1440 * 2)
        BarOcImpMs.Top = ((MsOcTotal - MsOcImp) / MsOcTotal) * (1440 * 2) + (1440 * 3)
        BarOcUnacMs.Height = (MsOcUnac / MsOcTotal) * (1440 * 2)
        BarOcUnacMs.Top = ((MsOcTotal - MsOcUnac) / MsOcTotal) * (1440 * 2) + (1440 * 3)
    
        BarVcCompMs.Height = (MsVcComp / MSVcTotal) * (1440 * 2)
        BarVcCompMs.Top = ((MSVcTotal - MsVcComp) / MSVcTotal) * (1440 * 2) + (1440 * 3)
        BarVcImpMs.Height = (MsVcImp / MSVcTotal) * (1440 * 2)
        BarVcImpMs.Top = ((MSVcTotal - MsVcImp) / MSVcTotal) * (1440 * 2) + (1440 * 3)
        BarVcUnacMs.Height = (MsVcUnac / MSVcTotal) * (1440 * 2)
        BarVcUnacMs.Top = ((MSVcTotal - MsVcUnac) / MSVcTotal) * (1440 * 2) + (1440 * 3)
    
        BarIbCompMs.Height = (MsIbComp / MsIbTotal) * (1440 * 2)
        BarIbCompMs.Top = ((MsIbTotal - MsIbComp) / MsIbTotal) * (1440 * 2) + (1440 * 3)
        BarIbImpMs.Height = (MsIbImp / MsIbTotal) * (1440 * 2)
        BarIbImpMs.Top = ((MsIbTotal - MsIbImp) / MsIbTotal) * (1440 * 2) + (1440 * 3)
        BarIbUnacMs.Height = (MsIbUnac / MsIbTotal) * (1440 * 2)
        BarIbUnacMs.Top = ((MsIbTotal - MsIbUnac) / MsIbTotal) * (1440 * 2) + (1440 * 3)
        
         
        BarOcCompTotal.Height = (TotalOcComp / TotalOcAll) * (1440 * 2)
        BarOcCompTotal.Top = ((TotalOcAll - TotalOcComp) / TotalOcAll) * (1440 * 2) + (1440 * 6.5)
        BarOcImpTotal.Height = (TotalOcImp / TotalOcAll) * (1440 * 2)
        BarOcImpTotal.Top = ((TotalOcAll - TotalOcImp) / TotalOcAll) * (1440 * 2) + (1440 * 6.5)
        BarOcUnacTotal.Height = (TotalOcUnac / TotalOcAll) * (1440 * 2)
        BarOcUnacTotal.Top = ((TotalOcAll - TotalOcUnac) / TotalOcAll) * (1440 * 2) + (1440 * 6.5)
    
        BarVcCompTotal.Height = (TotalVcComp / TotalVcAll) * (1440 * 2)
        BarVcCompTotal.Top = ((TotalVcAll - TotalVcComp) / TotalVcAll) * (1440 * 2) + (1440 * 6.5)
        BarVcImpTotal.Height = (TotalVcImp / TotalVcAll) * (1440 * 2)
        BarVcImpTotal.Top = ((TotalVcAll - TotalVcImp) / TotalVcAll) * (1440 * 2) + (1440 * 6.5)
        BarVcUnacTotal.Height = (TotalVcUnac / TotalVcAll) * (1440 * 2)
        BarVcUnacTotal.Top = ((TotalVcAll - TotalVcUnac) / TotalVcAll) * (1440 * 2) + (1440 * 6.5)
    
        BarIbCompTotal.Height = (TotalIbComp / TotalIbAll) * (1440 * 2)
        BarIbCompTotal.Top = ((TotalIbAll - TotalIbComp) / TotalIbAll) * (1440 * 2) + (1440 * 6.5)
        BarIbImpTotal.Height = (TotalIbImp / TotalIbAll) * (1440 * 2)
        BarIbImpTotal.Top = ((TotalIbAll - TotalIbImp) / TotalIbAll) * (1440 * 2) + (1440 * 6.5)
        BarIbUnacTotal.Height = (TotalIbUnac / TotalIbAll) * (1440 * 2)
        BarIbUnacTotal.Top = ((TotalIbAll - TotalIbUnac) / TotalIbAll) * (1440 * 2) + (1440 * 6.5)
        
    End Sub

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    An overflow error means that you're trying to assign a value to a variable that is too small to hold it. Step through the code until you find which line is throwing the error, and then have a look at the values involved.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2012
    Posts
    24
    Well the report adds results from 2 tables - FTP and MS, it then summarises the results.

    There are some occasions when there are ONLY FTP results, so that is when the error occurs; the debug then points to the first MS line, presumably because it doesn't know how to calculate zero results:

    Code:
    BarOcCompMs.Height = (MsOcComp / MsOcTotal) * (1440 * 2)
    So because there are ZERO MS results, it is essentially an irrelevant calculation:
    Height = ( 0 / 0 ) * (1440 *2)

    I don't know VBA so I don't know how to get around this problem

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You need to add some conditional logic to check for zero values, and if they are found, skip that operation.

    BTW, why do you have (1440 / 2) and (1440 * 2) all over the place? Why not simply use 2880 and 720?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    May 2012
    Posts
    24
    Could you point me in the right direction with example code?

    And with regards to your other question- i guess the answer is because i'm stupid. i followed an article online a bit too literally (it was talking about measurements of twips etc.)

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Code:
    If (MsOcComp <> 0) AND (MsOcTotal <> 0) Then
    
        BarOcCompMs.Height = (MsOcComp / MsOcTotal) * 2880
    
    End If
    And fair enough - we live and learn!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    May 2012
    Posts
    24
    Thank you so much for your help, works perfectly!!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looks like someone is converting from twips to change a text box or label control to act as a bar chart
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •