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

 

[IDS]

You are converting your prop input to UCase, then checking against a LCase letter. Just change all the UCase statements to LCase and it will work.

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/

 

[moader808]

Dam that worked, thanks a billion. Sometimes I cannot even spot my own simple errors haha. Do you have any idea how to get the code to interpolate properly between pressures? I have been looking around and it seems I need to write a double interpolation but is it possible to have a function inside of another function?

 

[IDS]

I have been looking around and it seems I need to write a double interpolation but is it possible to have a function inside of another function?

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/

 

[moader808]

The interp function you listed makes sense. I am a little confused though when you say I just need to call my function 3 times, I assume you are referring to the interpolation function you listed or my p1p2p3_H2Os?
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

 

[IDS]

I meant the interpolation function. In your code you have:

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

You could write a separate function:

 Function Interp(ti As Variant, vi As Variant, val2 As Double) As Variant
Dim valmin As Double, valmax As Double, Lbnd As Long, Ubnd As Long
Dim Answer As Variant, ii2 As Double, fi2 As Double

Lbnd = LBound(ti)
Ubnd = UBound(ti)

 valmin = ti(Lbnd)
 valmax = ti(Ubnd)
 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))
 Answer = vi(ii2 - 1) + fi2 * (vi(ii2) - vi(ii2 - 1))
 End If
Interp = Answer
 End Function

Then in your main function you would use:

prop2 = LCase(prop2)
prop3 = LCase(prop3)
If (prop2 = "t") and (prop3 = "v") Then
 answer = Interp(ti, vi, val2)
 End If

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/

 

[electricpete]

Dam that worked, thanks a billion.

There is in fact a feature built into the site to document your appreciation.

Click
"Thank IDS
and star this post!"

also known as LPS = Litte Purple Star

=====================================
(2B)+(2B)' ?

 

[moader808]

I've been trying to modify your interpolation function to work for pressures in between 6 and 35. My code just spits out errors. For the additional function to work, do you need to call it with something special or is it just as you posted?

 

[IDS]

I have attached an example.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MintJulep]

If (prop1 = "p" And val1 = "6") Then

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.

 

[moader808]

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.

How do you suggest I should write the code if the value of p is in between 6 and 35 then?

 

[MintJulep]

I suggest using numbers, not strings for numerical comparisons.

 

[IDS]

How do you suggest I should write the code if the value of p is in between 6 and 35 then?

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/

 

[moader808]

Hey Doug,

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.

function lin_interp(x, x1, y1, x2, y2)
lin_interp = (x - x1) / (x2 - x1) * (y2 - y1) + y1
End Function

This will give you a one-way linear interpolation if you like. So now you can apply this equation as many times as necessary to interpolate your in-between values.

For example, you know P1, P2, T1, T2, rho1, rho2 and you want to calculate rho at P1.5 and T1.5. You first run your linear interpolation to find the intermediate rho value at P1.5 and T1:

lin_interp(P1.5,P1,rho1(at T1),P2,rho2(at T1))
# This will calculate rho at P1.5 and T1.

Then you will need to calculate the intermediate rho value at P1.5 and T2:

lin_interp(P1.5,P1,rho1(at T2),P2,rho2(at T2))
# This will calulate rho at P1.5 and T2.

Now it is simply finding rho at P1.5 and T1.5.

lin_interp(T1.5,T1,rho(P1.5,T1),T2,rho(P1.5,T2)).

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!

 

[moader808]

I also have a question about Ubound and Lbound, if you dont mind answering that also. Im trying to make my code error proof as I need to add more tables with more arrays. Some of the arrays have 11 values instead of 12. This is a problem right here

valmin = ti(0)
valmax = ti(11)

I attempted to change it over to Lbound(ti) and Ubound(ti) but my function will return "value"

 

[IDS]

The code I posted before (and is included in the download spreadsheet) should work:

Lbnd = LBound(ti)
Ubnd = UBound(ti)

 valmin = ti(Lbnd)
 valmax = ti(Ubnd)

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/

 

[moader808]

I played around with the bounds some more, ended up that I had to place those commands right before the valmin and valmax lines. I didn't know order mattered lol

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.

 

[IDS]

I've had a look at your code. First thing is I suggest you save your work as an xlsm or xlsb file, rather than an add-in. It just makes it easier to access the worksheet, and you can save data on the worksheet with the macros, rather than having to open a new file.

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/

 

[moader808]

Didn't not know that about the different file types, thanks for the heads up.
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

 

[IDS]

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.

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/