Superheated tables VBA problem 1

[moader808]

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

 

[moader808]

Sorry for such a long reply. I did work your posts from the 26th. I have a question about the interp function you wrote. I got it to work and everything but, I do not quite understand what the function requires for input exactly. The t and v variant is throwing me off, could you explain that real quick?

I've been trying to modify the code to do the triple interpolation.

ii=application.match(val1, pi)
ii=Lii
ii+1=Uii

My question is when I write something like v(ii-1), and ii = 1, what happens when its v(0)? I run into this theoretical problem when Im trying to work on this code on paper to see the operations being produced. An example is what if I set up two different pressure tables, and only have two different temperatures? That confuses me when I run into ii=0.

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?

 

[moader808]

An attempt I made with your interpolation function to run the triple interpolation I am looking for. But my function only spits out values of 0...Any ideas?

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_6 As Variant, vi_6 As Variant, ui_6 As Variant, hi_6 As Variant, si_6 As Variant
Dim ti_35 As Variant, vi_35 As Variant, ui_35 As Variant, hi_35 As Variant, si_35 As Variant
Dim interp_6 As Double
Dim interp_35 As Double
Dim lower_bound As Double
Dim upper_bound As Double
Dim pfi As Double
Dim triple_interpolation As Double
Dim Answer As Double

prop1 = UCase(prop1)
prop2 = UCase(prop2)
prop3 = UCase(prop3)

pi = Array(6, 35, 70, 100, 150, 300, 500, 700, 1000, 1500, 2000, 3000, 4000, 6000, 8000, 10000, 12000, 14000, 16000, 18000, 20000, 24000, 28000, 32000)

If (prop1 = "P" And val1 = 6) Then
ti_6 = Array(36.16, 80, 120, 160, 200, 240, 280, 320, 360, 400, 440, 500)
vi_6 = 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)
ui_6 = Array(2425, 2487.3, 2544.7, 2602.7, 2661.4, 2721, 2781.5, 2843, 2905.5, 2969, 3033.5, 3132.3)
hi_6 = Array(2567.4, 2650.1, 2726, 2802.5, 2879.7, 2957.8, 3036.8, 3116.7, 3197.7, 3279.6, 3362.6, 3489.1)
si_6 = Array(8.3304, 8.5804, 8.784, 8.9693, 9.1398, 9.2982, 9.4464, 9.5859, 9.718, 9.8435, 9.9633, 10.1336)

ElseIf (prop1 = "P" And val1 = 35) Then
ti_35 = Array(72.69, 80, 120, 160, 200, 240, 280, 320, 360, 400, 440, 500)
vi_35 = 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)
ui_35 = Array(2473, 2483.7, 2542.4, 2601.2, 2660.4, 2720.3, 2780.9, 2842.5, 2905.1, 2968.6, 3033.2, 3132.1)
hi_35 = Array(2631.4, 2645.6, 2723.1, 2800.6, 2878.4, 2956.8, 3036, 3116.1, 3197.1, 3279.2, 3362.2, 3488.8)
si_35 = Array(7.7158, 7.7564, 7.9644, 8.1519, 8.3237, 8.4828, 8.6314, 8.7712, 8.9034, 9.0291, 9.149, 9.3194)

ElseIf (prop1 = "P" And 6 < val1 And val1 < 35 And prop2 = "T" And prop3 = "V") Then
lower_bound = interp_6
upper_bound = interp_35
pfi = (35 - val1) / (35 - 6)
triple_interpolation = upper_bound - (pfi * (upper_bound - lower_bound))
Answer = triple_interpolation

End If
 
p1p2p3_H2Os = Answer

End Function
Private Function interp_6(ti_6 As Variant, val2 As Double, vi_6 As Variant) As Variant
Dim valmin As Double
Dim valmax As Double
Dim Lbnd As Long
Dim Ubnd As Long
Dim Answer_6 As Variant
Dim ii_6 As Integer
Dim fi_6 As Double

Lbnd = LBound(ti_6)
Ubnd = UBound(ti_6)

 valmin = ti_6(Lbnd)
 valmax = ti_6(Ubnd)
 If (val2 < valmin) Then
 Answer = CVErr(xlErrValue)
 ElseIf (val2 > valmax) Then
 Answer = CVErr(xlErrValue)
 Else
 ii_6 = Application.Match(val2, ti_6)
 fi_6 = (val2 - ti_6(ii_6 - 1)) / (ti_6(ii_6) - ti_6(ii_6 - 1))
 Answer_6 = vi_6(ii_6 - 1) + fi_35 * (vi_6(ii_6) - vi_6(ii_6 - 1))
 End If
 
interp_6 = Answer_6
 End Function
Private Function interp_35(ti_35 As Variant, val2 As Double, vi_35 As Variant) As Variant
Dim valmin As Double
Dim valmax As Double
Dim Lbnd As Long
Dim Ubnd As Long
Dim Answer_35 As Variant
Dim ii_35 As Integer
Dim fi_35 As Double

Lbnd = LBound(ti_35)
Ubnd = UBound(ti_35)

 valmin = ti_35(Lbnd)
 valmax = ti_35(Ubnd)
 If (val2 < valmin) Then
 Answer = CVErr(xlErrValue)
 ElseIf (val2 > valmax) Then
 Answer = CVErr(xlErrValue)
 Else
 ii_35 = Application.Match(val2, ti_35)
 fi_35 = (val2 - ti_35(ii_35 - 1)) / (ti_35(ii_35) - ti_35(ii_35 - 1))
 Answer_35 = vi_35(ii_35 - 1) + fi_35 * (vi_35(ii_35) - vi_35(ii_35 - 1))
 End If
 
interp_35 = Answer_35
 End Function

 

[IDS]

The attached file returns a non-zero value, I haven't checked your interpolation calcs, so do your own check on the results.

I made the following changes to your code:

'Dim interp_6 As Double
'Dim interp_35 As Double

interp_6 and interp_35 are the names of functions, you shouldn't "Dim" them

If (prop1 = "P" And 6 <= val1 And val1 <= 35 And prop2 = "T" And prop3 = "V") Then
'If (prop1 = "P" And val1 = 6) Then
ti_6 = Array(36.16, 80, 120, 160, 200, 240, 280, 320, 360, 400, 440, 500)
..

You need to set up the arrays ti_6, vi_6, ti_35 and vi_35 if val1 is between 6 and 35, so move the If statement up to the top, and comment out the check if val1 - 6 and val1 = 35

lower_bound = interp_6(ti_6, val2, vi_6)
upper_bound = interp_35(ti_35, val2, vi_35)

You need to call the interp_6 and interp_35 functions properly, passing the arrays and val2.

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:

lower_bound = Interp(ti_6, vi_6, val2)
upper_bound = Interp(ti_35, vi_35, val2)

which gives the same result (but note the change in the order of the function arguments).



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/