×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Superheated tables VBA problem

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

RE: Superheated tables VBA problem

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/

RE: Superheated tables VBA problem

(OP)
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?

RE: Superheated tables VBA problem

Quote:

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/

RE: Superheated tables VBA problem

(OP)
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

RE: Superheated tables VBA problem

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

CODE -->

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:

CODE -->

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:

CODE -->

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/

RE: Superheated tables VBA problem

Quote (moader)

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)' ?

RE: Superheated tables VBA problem

(OP)
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?

RE: Superheated tables VBA problem

Quote:

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.

RE: Superheated tables VBA problem

(OP)

Quote (MintJulep)

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?

RE: Superheated tables VBA problem

I suggest using numbers, not strings for numerical comparisons.

RE: Superheated tables VBA problem

Quote:


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/

RE: Superheated tables VBA problem

(OP)
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.

Quote:

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!

RE: Superheated tables VBA problem

(OP)
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

CODE -->

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

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

RE: Superheated tables VBA problem

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

CODE -->

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/

RE: Superheated tables VBA problem

(OP)
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.

RE: Superheated tables VBA problem

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/

RE: Superheated tables VBA problem

(OP)
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

RE: Superheated tables VBA problem

Quote:

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/

RE: Superheated tables VBA problem

(OP)
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.

CODE -->

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?

RE: Superheated tables VBA problem

(OP)
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?

CODE -->

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 

RE: Superheated tables VBA problem

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:

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

CODE -->

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

CODE -->

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:

CODE -->

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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources