469,643 Members | 2,061 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

Overflow (Not a newbie question) VBA

First, let me start by saying I know what an overflow is (Too large of a value into something that can't hold it, i.e. 35000 into an int, or 0/0, etc).

Now to the issue. I have a class that I am trying to apply a value to one of its properties.

Expand|Select|Wrap|Line Numbers
  1. ...
  2. Streams.Item(rsName!Description).HInput = STMPTH(rs!P, rs!T)
  3. ...
  5. <in class>
  6. Property Let HInput(HInput As Double) 'Set the Enthalpy Input Value
  7.     pHInput = HInput
  8.     pH = pHInput
  9. End Property
where STMPTH() is a Steam Table look up method that takes a Double P and Double T and returns a Double H (Pressure, Temperature, return Enthalpy)

There are over 100 items in the Streams collection and only 2 throw an over flow error at the first line of code I have put up here. Now, before you ask, I do not have access to the code of the STMPTH() method (it's in a DLL file that my company did not create). However, I know the method does work. When the overflow error is thrown, the compiler stops on that line:

Expand|Select|Wrap|Line Numbers
  1. Streams.Item(rsName!Description).HInput = STMPTH(rs!P, rs!T)
So, I decide to use Step Through (F8) to try to debug the issue (thinking maybe I'm passing a negative value or something). But when I hit F8, the program continues without a hitch.

This is far from the first time I encountered this "phenomena" and have worked around it previously by catching the error and retrying the calculation (which worked. Example:

Expand|Select|Wrap|Line Numbers
  1. Property Get H() As Double 'Return Enthalpy of Stream
  2.     H = 0
  3.     On Error GoTo HErr
  4.     If pSameAs = "" Then
  5.         If pH <= 0 Then 'Enthalpy Not Known
  6.             If pT > 0# And pP > 0# Then 'Calculate From Pressure and Temperature
  7.                 pH = STMPTH(pP, pT)
  8.             ElseIf pT > 0# Then
  9.                 If pType = STREAMTYPE.Water Then
  10.                     pH = STMTQH(pT, 0#)
  11.                 End If
  12.             ElseIf pP > 0# Then
  13.                 If pType = STREAMTYPE.Water Then
  14.                     pH = STMPQH(pP, 0#)
  15.                 End If
  16.             End If
  17.         End If
  18.     Else
  19.         pH = Streams(pSameAs).H
  20.     End If
  21.     H = pH
  22.     GoTo HExit
  23. HErr:
  24.     H = 0
  25.     pT = pT
  26.     pP = pP
  27.     If pSameAs = "" Then
  28.         If pH <= 0 Then 'Enthalpy Not Known
  29.             If pT > 0# And pP > 0# Then 'Calculate From Pressure and Temperature
  30.                 pH = STMPTH(pP, pT)
  31.             ElseIf pT > 0# Then
  32.                 If pType = STREAMTYPE.Water Then
  33.                     pH = STMTQH(pT, 0#)
  34.                 Else
  35.                     pH = STMTQH(pT, 1#)
  36.                 End If
  37.             ElseIf pP > 0# Then
  38.                 If pType = STREAMTYPE.Water Then
  39.                     pH = STMPQH(pP, 0#)
  40.                 Else
  41.                     pH = STMPQH(pP, 1#)
  42.                 End If
  43.             End If
  44.         End If
  45.     Else
  46.         pH = Streams(SameAs).H
  47.     End If
  48.     H = pH
  49. HExit:
  50. End Property
However, this little "work around" does not want to work in this case and I am now at a loss as to how to fix this.

I have seen a couple of posts from other people on some forums with the same issue but they work around it by changing languages or compilers. I am limited to using VBA in access and am getting extremely frustrated with this problem.

Does anyone know of a solution?

Oh and I am using MS Access 2000 9.0.8961 SP-3
Mar 4 '08 #1
8 2906
2,653 Expert 2GB
Hi, Tophurious.

Actually I'm somewhat confused with the code of H get property.
You check variable pSameAs and if it is empty string do calculations otherwise you call the same property in other instance of the class which may or may not call the same in another instance.
Could you explain the logic?
Could it be so that the property at certain conditions becomes recursive?

Mar 4 '08 #2
The Get H Property is not where the issue is. That is an example of my work around. the problem is in the first part.

As far as the sameas that simply checks to see if we had set that item's enthalpy to be equal to another item's. if it is use the other items. if not calculate.

And if you notice the overflow error is not occurring on the Get H Property it is occuring in the Set HInput Property (which is included at the top)

Other clarifications:

pH: Double (private local)
pHInput: Double (private local)
rsName!Description: String (holds the key of item in collection)
rs!P: Double (pulled from database table, defined as double)
rs!T: Double (pulled from database table, defined as double)
Mar 4 '08 #3
2,653 Expert 2GB
One more stupid question. Sorry.
STMPQH() function is in the same DLL, isn't it?
Did the code of H get property (before you've made the patch) throw the error when try to call particularly it?
Mar 4 '08 #4
STMPQH() also in the DLL (Pressure, Quality, return Enthalpy). I do not remember if that was one that threw an error or not. The issue is not calling these methods.

The previous issue was that the compiler would "forget" the value until you looked at it (that is the best way I can explain it). Basically, the run would throw an overflow and break. I would hover over the variable (say for example, pH) and the tooltip would say "pH = 0" even though I KNEW it wasn't so I would look at the local properties window and in there it would say pH = 1200 (or something). I go and hover over the variable again and it would say "pH = 1200" (This is still in break mode, so the variables shoule not have changed at all) Thats what I mean by "Forget".

The current issue, is not that it is forgetting the value, it just throws an overflow. So I click run, when it hits that line (for only 2 out of the 100+ items in the collection) it throws an overflow and breaks. I click F8 to go forward and it works. Both rs!P and rs!T show proper values in break mode.

To Summarize: Current Issue:
Run == Overflow Error
Step Through == A'OK!!!
Mar 4 '08 #5
Stewart Ross
2,545 Expert Mod 2GB
Hi. You mention that the compiler stops at this line:
Expand|Select|Wrap|Line Numbers
  1. Streams.Item(rsName!Description).HInput = STMPTH(rs!P, rs!T)
VBA passes values by reference by default (as pointers). You are passing recordset field references, which are not updatable, and should only be passed by value, not by reference. Unless the function headers define that these variables are indeed passed by value (which you won't know for STMPTH as it isn't yours) this can cause overflow and other errors if the internal function code tries to change the value (for example, by incrementing it or setting it to another value as a shortcut).

I would suggest you assign the recordset fields to local variables and use these within the function calls instead.

Mar 4 '08 #6
Nice suggestion but nope.

put this in:
Expand|Select|Wrap|Line Numbers
  1. P = CDbl(rs!P)
  2. T = CDbl(rs!T)
  3. Streams.Item(rsName!Description).HInput = STMPTH(P, T)
same error, same spot. I had attempted this in my previous issue and it did not resolve it either. Still tried (hell I tried everything else I can think of)

Just so you know, here is the thread I started back in june or july with my original issue (and no one could give me an answer then either)
Mar 4 '08 #7
Stewart Ross
2,545 Expert Mod 2GB
Nice suggestion but nope.

put this in:
Expand|Select|Wrap|Line Numbers
  1. P = CDbl(rs!P)
  2. T = CDbl(rs!T)
  3. Streams.Item(rsName!Description).HInput = STMPTH(P, T)
same error, same spot. I had attempted this in my previous issue and it did not resolve it either. Still tried (hell I tried everything else I can think of)
OK. And you are certain that you are passing by value
rsName!Description in your streams call? Funnily enough it is with strings that I've had the overflow error when accidentally passing literals by reference.
Mar 4 '08 #8
2,653 Expert 2GB
Actually I'm not so sure that variable values you see in debug mode are the same as at runtime.

I've made a little experiment.

Code module
Expand|Select|Wrap|Line Numbers
  1. Public Sub Test()
  2.     Dim objClass1 As New Class1
  3.     objClass1.q = 1
  4. End Sub
Class module: Class1
Expand|Select|Wrap|Line Numbers
  1. Private lngQ As Long
  3. Public Property Get q() As Variant
  4.     lngQ = lngQ + 1
  5.     q = lngQ
  6. End Property
  8. Public Property Let q(ByVal vNewValue As Variant)
  9.     Err.Raise 9999
  10. End Property
You see Get property is never invoked in code and Let property simply throws a error, but the value of lngQ as well as q in locals window constantly increments when going into debug mode. That makes evidence that locals window invokes Get q() property to refresh itself.

I would suggest to get real runtime values of the variables. And maybe you'll need to code several attempts on the unstable code until you get a right value.

Mar 4 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

15 posts views Thread by Andrew Fedoniouk | last post: by
1 post views Thread by Marek Mänd | last post: by
13 posts views Thread by Ioannis Vranos | last post: by
4 posts views Thread by Chua Wen Ching | last post: by
8 posts views Thread by starffly | last post: by
7 posts views Thread by amit.atray | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.