Superheated tables VBA problem
Superheated tables VBA problem
(OP)
Hi all,
Im pretty new to VBA coding in excel. But im trying to code the super heated steam tables and im having a bit of trouble. I only have pressure, temperature, and specific volume in the code right now, but I cannot get the code to spit out a non zero value. If someone could point me in the right direction or even tell me how to debug this code somehow that would be great. Or reference a code that already has this done, I've seen codes do the super heated tables in thermodynamics before, but they all reference the holtzman constant.
Thanks in advance!
Function p1p2p3_H2Os(prop1 As String, val1 As Double, prop2 As String, val2 As Double, prop3 As String) As Double
Dim pi As Variant
Dim ti As Variant
Dim vi As Variant
Dim ii As Integer
Dim fi As Double
Dim ii2 As Integer
Dim fi2 As Double
Dim answer As Double
Dim valmin As Double
Dim valmax As Double
pi = Array(6, 35)
prop1 = UCase(prop1)
If (prop1 = "p" And val1 = "6") Then
ti = Array(36.16, 80, 120, 160, 200, 240, 280, 320, 360, 400, 440, 500)
vi = Array(23.739, 27.132, 30.219, 33.302, 36.383, 39.462, 42.54, 45.618, 48.696, 51.774, 54.851, 59.467)
ElseIf (prop1 = "p" And val1 = "35") Then
ti = Array(72.69, 80, 120, 160, 200, 240, 280, 320, 360, 400, 440, 500)
vi = Array(4.526, 4.625, 5.163, 5.696, 6.228, 6.758, 7.287, 7.815, 8.344, 8.872, 9.4, 10.192)
ElseIf (prop1 = "p" And "6" < val1 < "35") Then
ii = Application.Match(val1, pi)
fi = (val1 - pi(ii - 1)) / (pi(ii) - pi(ii - 1))
End If
prop2 = UCase(prop2)
If (prop2 = "t") Then
valmin = ti(0)
valmax = ti(11)
If (val2 < valmin) Then
answer = CVErr(xlErrValue)
ElseIf (val2 > valmax) Then
answer = CVErr(xlErrValue)
Else
ii2 = Application.Match(val2, ti)
fi2 = (val2 - ti(ii2 - 1)) / (ti(ii2) - ti(ii2 - 1))
End If
prop3 = UCase(prop3)
If (prop3 = "v") Then
answer = vi(ii2 - 1) + fi2 * (vi(ii2) - vi(ii2 - 1))
End If
p1p2p3_H2Os = answer
End If
End Function
Im pretty new to VBA coding in excel. But im trying to code the super heated steam tables and im having a bit of trouble. I only have pressure, temperature, and specific volume in the code right now, but I cannot get the code to spit out a non zero value. If someone could point me in the right direction or even tell me how to debug this code somehow that would be great. Or reference a code that already has this done, I've seen codes do the super heated tables in thermodynamics before, but they all reference the holtzman constant.
Thanks in advance!
Function p1p2p3_H2Os(prop1 As String, val1 As Double, prop2 As String, val2 As Double, prop3 As String) As Double
Dim pi As Variant
Dim ti As Variant
Dim vi As Variant
Dim ii As Integer
Dim fi As Double
Dim ii2 As Integer
Dim fi2 As Double
Dim answer As Double
Dim valmin As Double
Dim valmax As Double
pi = Array(6, 35)
prop1 = UCase(prop1)
If (prop1 = "p" And val1 = "6") Then
ti = Array(36.16, 80, 120, 160, 200, 240, 280, 320, 360, 400, 440, 500)
vi = Array(23.739, 27.132, 30.219, 33.302, 36.383, 39.462, 42.54, 45.618, 48.696, 51.774, 54.851, 59.467)
ElseIf (prop1 = "p" And val1 = "35") Then
ti = Array(72.69, 80, 120, 160, 200, 240, 280, 320, 360, 400, 440, 500)
vi = Array(4.526, 4.625, 5.163, 5.696, 6.228, 6.758, 7.287, 7.815, 8.344, 8.872, 9.4, 10.192)
ElseIf (prop1 = "p" And "6" < val1 < "35") Then
ii = Application.Match(val1, pi)
fi = (val1 - pi(ii - 1)) / (pi(ii) - pi(ii - 1))
End If
prop2 = UCase(prop2)
If (prop2 = "t") Then
valmin = ti(0)
valmax = ti(11)
If (val2 < valmin) Then
answer = CVErr(xlErrValue)
ElseIf (val2 > valmax) Then
answer = CVErr(xlErrValue)
Else
ii2 = Application.Match(val2, ti)
fi2 = (val2 - ti(ii2 - 1)) / (ti(ii2) - ti(ii2 - 1))
End If
prop3 = UCase(prop3)
If (prop3 = "v") Then
answer = vi(ii2 - 1) + fi2 * (vi(ii2) - vi(ii2 - 1))
End If
p1p2p3_H2Os = answer
End If
End Function





RE: Superheated tables VBA problem
To step through a UDF:
- Create a break point in the code wherever you want to start de-bugging (click in the margin or press F9)
- Go back to the spreadsheet and select a cell containing the UDF, press F2 then enter.
- You will be taken to the VB Editor window at your breakpoint. Press F8 to step through the code.
If you are going to have a lot of different options for the prop codes you might want to check out the Select Case statement, rather than using a load of if-thens.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Superheated tables VBA problem
RE: Superheated tables VBA problem
You can, but in this case you just need to call the same function three times.
If you have a function: Interp(x1,y1,x2,y2,x3) which returns y3 at x3 by interpolation
and you have four known points:
x11,y11,t1
x21,y21,t1
x12,y12,t2
x22,y22,t2
and you want to find y3 at x3, t3
Then you find:
y31 at x3, t1 using Interp(x11,y11,x21,y21,x3)
y32 at x3, t2 using Interp(x12,y12,x22,y22,x3)
then the answer is given by interp(t1, y31, t2, y32, t3)
Does that make sense?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Superheated tables VBA problem
But I think, what your saying is I want to nest this interp function 3 times correct?
Sorry im very new to vba and coding in general
RE: Superheated tables VBA problem
CODE -->
You could write a separate function:
CODE -->
Then in your main function you would use:
CODE -->
Then you can use the same Interp function anywhere in the code where you needed to do an interpolation.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Superheated tables VBA problem
Click
"Thank IDS
and star this post!"
also known as LPS = Litte Purple Star
=====================================
(2B)+(2B)' ?
RE: Superheated tables VBA problem
RE: Superheated tables VBA problem
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Superheated tables VBA problem
Assuming that val1 is a number of some sort, you don't really want to evaluate a number against a string.
"6" has a numerical value of 0.
RE: Superheated tables VBA problem
How do you suggest I should write the code if the value of p is in between 6 and 35 then?
RE: Superheated tables VBA problem
RE: Superheated tables VBA problem
Just don't put "" around the numbers.
Did you look at my spreadsheet I posted?
Does it solve your problem?
If not, what errors are you getting?
Have you tried stepping through the code?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Superheated tables VBA problem
ya I looked at your interp function, It could streamline my code a bit but does not solve my pressure interpolation problem. Or I have not yet figured out how to write it properly. I've been messing with your and looking up other ways to do it. Basically what im trying to do was listed in another thread, you actually posted in so maybe you can give me some pointers.
Now my question is how do i set this up for varying arrays and varying bounds. Say for example p=20 and t=100 and I want to find the corresponding v. I just started playing around with the code, so forgive me I am a noob at this haha. I need the code to do all 3 interpolations and then spit out the answer. Thanks for helping me already, I really appreciate it!
RE: Superheated tables VBA problem
CODE -->
I attempted to change it over to Lbound(ti) and Ubound(ti) but my function will return "value"
RE: Superheated tables VBA problem
CODE -->
That function should work with any bounds you send to it.
It would help if you could give more details of what isn't working; e.g. post the actual code that isn't working, and the exact error message you get.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Superheated tables VBA problem
I haven't saved any of my work for the triple interpolation for the pressure groups, as I really haven't worked all that much on it. My problem lies in the fact that I don't really know how to start it.
I've been thinking of a couple of different ways to do it, and if you could give me your input that would be fantastic. I'm thinking that your approach of having a separate interpolation function is the best way to solve the pressure group interpolation. But I am still wondering if my original idea of having an elseif statement with interpolation is an option. Attached is my current code for my project. The elseif statement I am referring to is currently commented out. The interpolations I need done are is the process I quoted in a couple posts back.
RE: Superheated tables VBA problem
Secondly, I'm not sure exactly what your problem is now. Your function seems to work if Val1 is exactly one of the values you have arrays for, so you just need to get two answers for the bounding Val1 values, then interpolate for the actual value.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Superheated tables VBA problem
I was thinking about that today actually, pulling the two bounds for val1, and interpolating but how do i do that exactly? Would functions like hlookup and vlookup be useful? My VBA knowledge is very limited sorry if that seems like a dumb question
RE: Superheated tables VBA problem
Have you worked through my posts of 26th Jan? They give an interpolation function, and how to apply it.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Superheated tables VBA problem
I've been trying to modify the code to do the triple interpolation.
CODE -->
Another question is, I have figured out how to specify each table and set them equal to a position. Such that ii is equal to the lower bound pressure, and ii + 1 is equal to the upper bound pressure. But is ii + 1 even a valid operation? Once I figured out the two pressure bounds, I ran into the problem im currently trying to figure out, how do I get the two temperature bounds for the designated lower and upper bound pressures?
RE: Superheated tables VBA problem
CODE -->
RE: Superheated tables VBA problem
I made the following changes to your code:
CODE -->
CODE -->
CODE -->
There were also some copying errors in interp_6 and interp_35 that I have fixed.
The code above will work, but it isn't necessary to have two different functions to do the interpolation, they both do exactly the same thing (with different values) and the same as the original interp function.
You could use:
CODE -->
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/