Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
Greetings! The below code generates an overflow error when aNumShrs(21, 1)
contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
You need to ensure that aNumShrs(21,1) is a numeric expression. What is
aNumShrs(lRow, 1)? Is it a Sub? Is it a function that returns a Double data type? Maybe try testing if aNumShrs(lRow, 1) is numeric first. Hope this helps! If so, let me know, click "YES" below. Sub UserCode() If IsNumeric(aNumShrs(lRow, 1)) Then NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares Else MsgBox "aNumShrs is not numeric." End If End Sub -- Cheers, Ryan "MichaelDavid" wrote: Greetings! The below code generates an overflow error when aNumShrs(21, 1) contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
Hi Ryan:
Thanks for your speedy reply, aNumShares is the following array: aNumShrs = Range("L1:L" & LstRowData).Value LstRowData is defined as: Dim LstRowData as Long, and is typically about 500. I hope we can get to the bottom of this soon. Your help is greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ryan H" wrote: You need to ensure that aNumShrs(21,1) is a numeric expression. What is aNumShrs(lRow, 1)? Is it a Sub? Is it a function that returns a Double data type? Maybe try testing if aNumShrs(lRow, 1) is numeric first. Hope this helps! If so, let me know, click "YES" below. Sub UserCode() If IsNumeric(aNumShrs(lRow, 1)) Then NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares Else MsgBox "aNumShrs is not numeric." End If End Sub -- Cheers, Ryan "MichaelDavid" wrote: Greetings! The below code generates an overflow error when aNumShrs(21, 1) contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
Hi Ryan
I forgot to mention that array aNumShares is numeric. It contains mostly Long variables but occasionally contains a few Double Precision Variables. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ryan H" wrote: You need to ensure that aNumShrs(21,1) is a numeric expression. What is aNumShrs(lRow, 1)? Is it a Sub? Is it a function that returns a Double data type? Maybe try testing if aNumShrs(lRow, 1) is numeric first. Hope this helps! If so, let me know, click "YES" below. Sub UserCode() If IsNumeric(aNumShrs(lRow, 1)) Then NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares Else MsgBox "aNumShrs is not numeric." End If End Sub -- Cheers, Ryan "MichaelDavid" wrote: Greetings! The below code generates an overflow error when aNumShrs(21, 1) contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
Try this line. You are trying to round a range of cells. You need to sum
them first or calculate them to get a single value. NumShares = Round(WorksheetFunction.Sum(aNumShrs(lRow, 1)), 2) Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "MichaelDavid" wrote: Hi Ryan I forgot to mention that array aNumShares is numeric. It contains mostly Long variables but occasionally contains a few Double Precision Variables. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ryan H" wrote: You need to ensure that aNumShrs(21,1) is a numeric expression. What is aNumShrs(lRow, 1)? Is it a Sub? Is it a function that returns a Double data type? Maybe try testing if aNumShrs(lRow, 1) is numeric first. Hope this helps! If so, let me know, click "YES" below. Sub UserCode() If IsNumeric(aNumShrs(lRow, 1)) Then NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares Else MsgBox "aNumShrs is not numeric." End If End Sub -- Cheers, Ryan "MichaelDavid" wrote: Greetings! The below code generates an overflow error when aNumShrs(21, 1) contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
Obviously, this is not your full code... your use of lRow seems to indicate
that the code you showed us is in a loop. And, of course, we cannot see your other values in order to see what is going on during the calculations. I will tell you, though, I would not trust simply using On Error Resume Next to solve your problem... doing that in this situation would be just begging for trouble. Can you tell me how many rows of data have been processed when the error occurs... do you get past the first row at all? Do you always declare all of your variables? If yes, do you use Option Explicit at the top of your modules? If not, put that statement at the top of your module and run your code again... if you do declare all your variables and you don't use Option Explicit at the beginning of the module, then I am expecting you to see a misspelled variable name message popup when you run your code. If this above is not the case, can you send me your workbook so I can see the error happen for myself and, hopefully, be able to trace it to its source? Make sure you take out the NO.SPAM stuff from my address before sending it. -- Rick (MVP - Excel) "MichaelDavid" wrote in message ... Greetings! The below code generates an overflow error when aNumShrs(21, 1) contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
Hi Ryan,
In a flash of programming inspiration, I noticed that some of my variables were declared as Single Precision. So quickly, I did a Ctrl+H and replaced all "Dim ... As Single" to "Dim ... As Double" thruout all my macros. I then commented out the On Error Resume Next and On Error GoTo 0 as shown below. I then ran this macro umpteen times and the overflow error no longer occurs. Ray!!! Dim NumShares As Double Dim NumSharesLong As Long ' On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares ' On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Sincerely, Michael |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
Hi Rick,
In a flash of programming inspiration, I noticed that some of my variables were declared as Single Precision. So quickly, I did a Ctrl+H and replaced all "Dim ... As Single" to "Dim ... As Double" thruout all my macros. I then commented out the On Error Resume Next and On Error GoTo 0 as shown below. I then ran the macro containing this code umpteen times and the overflow error no longer occurs. Ray!!! Dim NumShares As Double Dim NumSharesLong As Long ' On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares ' On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Rick Rothstein" wrote: Obviously, this is not your full code... your use of lRow seems to indicate that the code you showed us is in a loop. And, of course, we cannot see your other values in order to see what is going on during the calculations. I will tell you, though, I would not trust simply using On Error Resume Next to solve your problem... doing that in this situation would be just begging for trouble. Can you tell me how many rows of data have been processed when the error occurs... do you get past the first row at all? Do you always declare all of your variables? If yes, do you use Option Explicit at the top of your modules? If not, put that statement at the top of your module and run your code again... if you do declare all your variables and you don't use Option Explicit at the beginning of the module, then I am expecting you to see a misspelled variable name message popup when you run your code. If this above is not the case, can you send me your workbook so I can see the error happen for myself and, hopefully, be able to trace it to its source? Make sure you take out the NO.SPAM stuff from my address before sending it. -- Rick (MVP - Excel) "MichaelDavid" wrote in message ... Greetings! The below code generates an overflow error when aNumShrs(21, 1) contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
In a flash of programming inspiration, I noticed that some of
my variables were declared as Single Precision. This is one of the problems with posting only parts of your code... we can't see these types of problems in order to comment on them for you. -- Rick (MVP - Excel) "MichaelDavid" wrote in message ... Hi Rick, In a flash of programming inspiration, I noticed that some of my variables were declared as Single Precision. So quickly, I did a Ctrl+H and replaced all "Dim ... As Single" to "Dim ... As Double" thruout all my macros. I then commented out the On Error Resume Next and On Error GoTo 0 as shown below. I then ran the macro containing this code umpteen times and the overflow error no longer occurs. Ray!!! Dim NumShares As Double Dim NumSharesLong As Long ' On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares ' On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Rick Rothstein" wrote: Obviously, this is not your full code... your use of lRow seems to indicate that the code you showed us is in a loop. And, of course, we cannot see your other values in order to see what is going on during the calculations. I will tell you, though, I would not trust simply using On Error Resume Next to solve your problem... doing that in this situation would be just begging for trouble. Can you tell me how many rows of data have been processed when the error occurs... do you get past the first row at all? Do you always declare all of your variables? If yes, do you use Option Explicit at the top of your modules? If not, put that statement at the top of your module and run your code again... if you do declare all your variables and you don't use Option Explicit at the beginning of the module, then I am expecting you to see a misspelled variable name message popup when you run your code. If this above is not the case, can you send me your workbook so I can see the error happen for myself and, hopefully, be able to trace it to its source? Make sure you take out the NO.SPAM stuff from my address before sending it. -- Rick (MVP - Excel) "MichaelDavid" wrote in message ... Greetings! The below code generates an overflow error when aNumShrs(21, 1) contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
Hi Rick,
This macro (Module 143) has 1800 lines, and some of the logic is a bit complex. It is difficult to know how much of the macro code should be submitted with the description of the problem. As it turns out, even if I had submitted the entire macro, the problem would be difficult to find. All the floating point variables defined in this macro were already of type "Double Precision". The actual problem code was the following definition contained in a different macro altogether (Module 3): Public EPrivWeighting As Single Thanks for looking into this problem with me. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Rick Rothstein" wrote: In a flash of programming inspiration, I noticed that some of my variables were declared as Single Precision. This is one of the problems with posting only parts of your code... we can't see these types of problems in order to comment on them for you. -- Rick (MVP - Excel) "MichaelDavid" wrote in message ... Hi Rick, In a flash of programming inspiration, I noticed that some of my variables were declared as Single Precision. So quickly, I did a Ctrl+H and replaced all "Dim ... As Single" to "Dim ... As Double" thruout all my macros. I then commented out the On Error Resume Next and On Error GoTo 0 as shown below. I then ran the macro containing this code umpteen times and the overflow error no longer occurs. Ray!!! Dim NumShares As Double Dim NumSharesLong As Long ' On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares ' On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Rick Rothstein" wrote: Obviously, this is not your full code... your use of lRow seems to indicate that the code you showed us is in a loop. And, of course, we cannot see your other values in order to see what is going on during the calculations. I will tell you, though, I would not trust simply using On Error Resume Next to solve your problem... doing that in this situation would be just begging for trouble. Can you tell me how many rows of data have been processed when the error occurs... do you get past the first row at all? Do you always declare all of your variables? If yes, do you use Option Explicit at the top of your modules? If not, put that statement at the top of your module and run your code again... if you do declare all your variables and you don't use Option Explicit at the beginning of the module, then I am expecting you to see a misspelled variable name message popup when you run your code. If this above is not the case, can you send me your workbook so I can see the error happen for myself and, hopefully, be able to trace it to its source? Make sure you take out the NO.SPAM stuff from my address before sending it. -- Rick (MVP - Excel) "MichaelDavid" wrote in message ... Greetings! The below code generates an overflow error when aNumShrs(21, 1) contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick . . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow Error But Right Answer is Produced
Michael,
I doubt switching your variable delcarations from Single to Double made all the different, but it could be possible. But I agree with Rick Rothstein, I would highly recommend no using On Error Resume Next Statements because this will cause problems, because if a calculation error happens you or the user will never know about it. Do you have code on how aNumShrs is calculated on the line below? NumShares = Round(aNumShrs(lRow, 1), 2) Is it a function? What data type does it return? What calculations does it preform? -- Cheers, Ryan "MichaelDavid" wrote: Hi Rick, This macro (Module 143) has 1800 lines, and some of the logic is a bit complex. It is difficult to know how much of the macro code should be submitted with the description of the problem. As it turns out, even if I had submitted the entire macro, the problem would be difficult to find. All the floating point variables defined in this macro were already of type "Double Precision". The actual problem code was the following definition contained in a different macro altogether (Module 3): Public EPrivWeighting As Single Thanks for looking into this problem with me. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Rick Rothstein" wrote: In a flash of programming inspiration, I noticed that some of my variables were declared as Single Precision. This is one of the problems with posting only parts of your code... we can't see these types of problems in order to comment on them for you. -- Rick (MVP - Excel) "MichaelDavid" wrote in message ... Hi Rick, In a flash of programming inspiration, I noticed that some of my variables were declared as Single Precision. So quickly, I did a Ctrl+H and replaced all "Dim ... As Single" to "Dim ... As Double" thruout all my macros. I then commented out the On Error Resume Next and On Error GoTo 0 as shown below. I then ran the macro containing this code umpteen times and the overflow error no longer occurs. Ray!!! Dim NumShares As Double Dim NumSharesLong As Long ' On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares ' On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Rick Rothstein" wrote: Obviously, this is not your full code... your use of lRow seems to indicate that the code you showed us is in a loop. And, of course, we cannot see your other values in order to see what is going on during the calculations. I will tell you, though, I would not trust simply using On Error Resume Next to solve your problem... doing that in this situation would be just begging for trouble. Can you tell me how many rows of data have been processed when the error occurs... do you get past the first row at all? Do you always declare all of your variables? If yes, do you use Option Explicit at the top of your modules? If not, put that statement at the top of your module and run your code again... if you do declare all your variables and you don't use Option Explicit at the beginning of the module, then I am expecting you to see a misspelled variable name message popup when you run your code. If this above is not the case, can you send me your workbook so I can see the error happen for myself and, hopefully, be able to trace it to its source? Make sure you take out the NO.SPAM stuff from my address before sending it. -- Rick (MVP - Excel) "MichaelDavid" wrote in message ... Greetings! The below code generates an overflow error when aNumShrs(21, 1) contains 15749.9999602351, and the macro executes the line which reads: TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1) When I modified the above code as follows: Dim aNumShrs As Variant Dim aPrice As Variant Dim TotalPurchases As Double Dim TotalSharesPurchased As Long Dim NumShares As Double Dim NumSharesLong As Long NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong I still get the overflow error, but I can tell by holding my cursor over the variables in the above, that the right answer is being generated. But the overflow error prohibits the program from continuing. So I then added On Error Resume Next to the above logic as follows: On Error Resume Next NumShares = Round(aNumShrs(lRow, 1), 2) NumSharesLong = NumShares On Error GoTo 0 If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1) TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1) TotalSharesPurchased = TotalSharesPurchased + NumSharesLong Now the logic runs without a hitch and produces the right answer, and the macro containing the above logic runs to completion. Is there any way to code this without using On Error Resume Next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overflow Error | Excel Programming | |||
Overflow Error | Excel Programming | |||
Overflow error | Excel Programming | |||
Overflow Error | Excel Discussion (Misc queries) | |||
Help! Overflow Error 6 | Excel Programming |