Overflow Error But Right Answer is Produced

[MichaelDavid]

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

 

[Ryan H]

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

 

[MichaelDavid]

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

 

[MichaelDavid]

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]

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.

 

[Rick Rothstein]

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.

 

[MichaelDavid]

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

 

[MichaelDavid]

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]

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.

 

[MichaelDavid]

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

 

[Ryan H]

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?