Hello, I am experiencing an overflow error but I'm not sure why. The code I am using it:
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.
Please Login or Register to view this content.
Last edited by danielexcelvba; 07-11-2017 at 10:42 AM.
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.
It still gives the same error.
Thank you I was wondering how to make it look like code
Just to be clear, its the line 'holder = (x / y)' that errors?
What values are assigned to x and y when it errors?
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.
Oh, duh! Sorry, just seen the problem:
You can't do this:
the AND operator only works during the criteria phase of the IF statement.Please Login or Register to view this content.
Try:
I just tested what happens with a x = 1 AND y = 2 and both variables are assigned the value of 0. 0 / 0 = overflow.Please Login or Register to view this content.
You're right about 0s being stored in the x and y variables, but I tried
And the same thing is happening, so i still get the error. Not sure whyPlease Login or Register to view this content.
I found a way around it but I have no idea why it works. Instead of using this code:
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.Please Login or Register to view this content.
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.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.
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.
Originally Posted by shg
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.
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.
*999 is a value we used in the old days to flag a zero divisorPlease Login or Register to view this content.
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
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.
Immediate If (IIf) always calculates both expressions, so will not be of use for this. You should use an If...Then construct.
Whenever you must divide you need code to avert dividing by zero - that will always cause an overflow.
*999 is a value we used in the old days to flag a zero divisorPlease Login or Register to view this content.
Hi xlnitwit,
Obviously, I didn't know that - thanks!
Please Login or Register to view this content.
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).
You're welcome and thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks