+ Reply to Thread
Results 1 to 16 of 16

Overflow error?

  1. #1
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Overflow error?

    Hello, I am experiencing an overflow error but I'm not sure why. The code I am using it:

    Please Login or Register  to view this content.
    I am getting an overflow error for my holder variable but I'm not sure why. I tried to dim it as single, double and variant but nothing is working.
    Last edited by danielexcelvba; 07-11-2017 at 10:42 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    554

    Re: Overflow error?

    Try long. Integers have a limit of 32767

    Oh and you'll need to put your code between [ code ] tags when posting on the forum or a moderator will whinge at you.

  3. #3
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: Overflow error?

    It still gives the same error.

    Thank you I was wondering how to make it look like code

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    554

    Re: Overflow error?

    Just to be clear, its the line 'holder = (x / y)' that errors?

    What values are assigned to x and y when it errors?

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Overflow error?

    Hi,

    Declare x, y and holder all as Double, or Long depending on the size of the values you are dealing with.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  6. #6
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    554

    Re: Overflow error?

    Oh, duh! Sorry, just seen the problem:

    You can't do this:

    Please Login or Register  to view this content.
    the AND operator only works during the criteria phase of the IF statement.

    Try:

    Please Login or Register  to view this content.
    I just tested what happens with a x = 1 AND y = 2 and both variables are assigned the value of 0. 0 / 0 = overflow.

  7. #7
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: Overflow error?

    You're right about 0s being stored in the x and y variables, but I tried
    Please Login or Register  to view this content.
    And the same thing is happening, so i still get the error. Not sure why

  8. #8
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: Overflow error?

    I found a way around it but I have no idea why it works. Instead of using this code:
    Please Login or Register  to view this content.
    Which caused x and y to receive values of 0, I deleted the holder line and replaced with range("B2").value = (x/y) which somehow works.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,559

    Re: Overflow error?

    Which caused x and y to receive values of 0, I deleted the holder line and replaced with range("B2").value = (x/y) which somehow works.
    That does not make sense to me. If y is 0, then neither statement should work, since 0/0 should result in an error no matter where you try to store the result.

    I do note that holder is dimmed as single where the Excel cell will hold a double, though I don't know why that difference in data type should matter.

    You will have to decide if your workaround really works, but something still doesn't seem right about it.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  10. #10
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: Overflow error?

    It doesn't make sense to me either. This is the full code I'm using and it is working
    Please Login or Register  to view this content.

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Overflow error?

    It looks like you're only getting the last row data so the loop is frivolous

    Whenever you must divide you need code to avert dividing by zero - that will always cause an overflow.

    Please Login or Register  to view this content.
    *999 is a value we used in the old days to flag a zero divisor
    Last edited by xladept; 07-11-2017 at 12:21 PM. Reason: Bad mistake
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,559

    Re: Overflow error?

    If it works, then it works. However, I would be concerned that it has merely "masked" the bug and that there is still some kind of bug hidden.

    I would note that it is difficult to debug on this side of the internet without knowing exactly what is in Cells(i,5) and Cells(i,8), that is getting converted to something that is 0 but not 0 when stored in x and y.

  13. #13
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Overflow error?

    Quote Originally Posted by xladept View Post
    Whenever you must divide you need code to avert dividing by zero - that will always cause an overflow.

    Please Login or Register  to view this content.
    *999 is a value we used in the old days to flag a zero divisor
    Immediate If (IIf) always calculates both expressions, so will not be of use for this. You should use an If...Then construct.

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Overflow error?

    Hi xlnitwit,

    Obviously, I didn't know that - thanks!

    Please Login or Register  to view this content.

  15. #15
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: Overflow error?

    Thanks all! Yes as long as it is working I suppose it's fine. The values in the cells are simply integer values (i,5) is number of people who have completed training, (i,8) is total number of people. I suppose you are correct about the loop being frivolous, it's just the only way I knew how to search for a value in a column on VBA (I guess I could have used worksheetfunction.vlookup).

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Overflow error?

    You're welcome and thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Overflow error
    By Crispy85 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-01-2016, 10:12 AM
  2. overflow error
    By cmccabe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-09-2014, 02:19 PM
  3. [SOLVED] OVerflow 6 error
    By Mortada in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-25-2013, 06:09 PM
  4. Explanation of the Run-time error '6': Overflow Error
    By mgphill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2012, 10:46 AM
  5. Want to do a while-loop but get error message: run error '6' overflow
    By danzi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2011, 01:48 PM
  6. Overflow error
    By Grd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2006, 04:20 AM
  7. Overflow Error
    By DG in forum Excel General
    Replies: 3
    Last Post: 04-15-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1