Solved: VBA function procedure homework help

[gaross]

[VBA][/VBA]I need help with an assignment I've been working on. I feel like I'm completely lost on this. It's driving me crazy.

The assignment states:

"Create a VBA function procedure called R_K to evaluate the Redlich-Kwong equation of state given in problem 11 on page 327 (attached jpg). Note that there is confusion in the text between v (lowercase) and V (uppercase). All instances should be v, with units of L/mole. Do not use the values of T and v given in the problem statement; those should be a function arguments so the user can choose values. Remember that a function procedure must be stored in a standard module in order to be executed from the Excel worksheet."

The instructor is a little off his rocker and over half the class has dropped so far (9 weeks in). Any help would be greatly appreciated!!

This is my code plus I have a chart for the data in excel:

[VBA]Option Explicit
Type CompoundData
compound As String
ctemp As Double
cpres As Double
End Type
Sub temp_press_data()
Dim RK(4) As CompoundData
RK(1).compound = "Methane"
RK(1).ctemp = 190.6
RK(1).cpres = 45.4
RK(2).compound = "Ethylene"
RK(2).ctemp = 282.4
RK(2).cpres = 49.7
RK(3).compound = "Nitrogen"
RK(3).ctemp = 126.2
RK(3).cpres = 33.5
RK(4).compound = "Water (vapor)"
RK(4).ctemp = 647.1
RK(4).cpres = 217.6
Call R_K
End Sub
Public Function R_K()
Dim R As Double, T As Double, P As Double, V As Double, a As Double, b As Double, x As Integer
Dim RK(4) As CompoundData
x = InputBox("Please select a compound from the chart")
T = InputBox("Please enter temperature (K)")
V = InputBox("Please enter volume (L)")
R = 0.08205
If x = 1 Then
a = 0.427 * (((R ^ (2)) * (RK(1).ctemp ^ (2.5)) / (RK(1).cpres)))
b = 0.0866 * R * ((RK(1).ctemp) / (RK(1).cpres))
P = ((R * T) / (V - b)) - ((a) / (V * (V + b) * Sqr(T)))
ElseIf x = 2 Then
a = 0.427 * (((R ^ (2)) * (RK(2).ctemp ^ (2.5)) / (RK(2).cpres)))
b = 0.0866 * R * ((RK(2).ctemp) / (RK(2).cpres))
P = ((R * T) / (V - b)) - ((a) / (V * (V + b) * Sqr(T)))
ElseIf x = 3 Then
a = 0.427 * (((R ^ (2)) * (RK(3).ctemp ^ (2.5)) / (RK(3).cpres)))
b = 0.0866 * R * ((RK(3).ctemp) / (RK(3).cpres))
P = ((R * T) / (V - b)) - ((a) / (V * (V + b) * Sqr(T)))
Else
a = 0.427 * (((R ^ (2)) * (RK(4).ctemp ^ (2.5)) / (RK(4).cpres)))
b = 0.0866 * R * ((RK(4).ctemp) / (RK(4).cpres))
P = ((R * T) / (V - b)) - ((a) / (V * (V + b) * Sqr(T)))
End If
End Function
[/VBA]

[Bob Phillips]

When it says that T and v should be function arguments, that does not mean inputboxes, but arguments to the function signature, like so

[vba]Public Function R_K(T as Double, v As Double) As Double[/vba]

and then you calculate the value in that function and pass back to the worksheet by setting R_K to that value.

I don't see the mention of the compound in that image, but that should also be an argument

[vba]Public Function R_K(x As Long,T as Double, v As Double) As Double[/vba]

Then on the worksheet you would call it like so

=R_K(2, 1.2345, 0.0654) as an example.

You can simplify the test of x because it is used as an index into RK, so you can use it directly. Also, the setup data should be in the function.

[VBA]Option Explicit

Type CompoundData
compound As String
ctemp As Double
cpres As Double
End Type

Public Function R_K(x As Long, T As Double, v As Double) As Double
Dim R As Double, a As Double, b As Double
Dim RK(1 To 4) As CompoundData
RK(1).compound = "Methane"
RK(1).ctemp = 190.6
RK(1).cpres = 45.4
RK(2).compound = "Ethylene"
RK(2).ctemp = 282.4
RK(2).cpres = 49.7
RK(3).compound = "Nitrogen"
RK(3).ctemp = 126.2
RK(3).cpres = 33.5
RK(4).compound = "Water (vapor)"
RK(4).ctemp = 647.1
RK(4).cpres = 217.6
R = 0.08205
a = 0.427 * (((R ^ (2)) * (RK(1).ctemp ^ (2.5)) / (RK(x).cpres)))
b = 0.0866 * R * ((RK(x).ctemp) / (RK(1).cpres))
R_K = ((R * T) / (v - b)) - ((a) / (v * (v + b) * Sqr(T)))
End Function
[/VBA]

You can also pass cell references that contain the values to the function.

[gaross]

Thank you for your response. I'll give this a try.

[gaross]

My instructor has come back and said that I need to write my function to work with any compound. Right now the user is limited to choosing one of the four compounds on the chart. Would I define my variables but not give them values? This would allow them to enter something like =R_K(methane, 12.34, 0.21) into Excel, right? How would I do that?

[JKwan]

I am reading between the lines here, since the instructor wanted the function to work with ANY compound, to me, this means you will need to supply the cTemp and cPres on the fly (or define it in your routine, which will go on forever.....). So, I changed the function to accept the 2 extra parameter, so the new one would look like this:
[VBA]Public Function R_K(cTemp As Long, cPres As Double, T As Double, v As Double) As Double
Dim R As Double, a As Double, b As Double
R = 0.08205
a = 0.427 * (((R ^ (2)) * (cTemp ^ (2.5)) / (cPres)))
b = 0.0866 * R * ((cTemp) / (cPres))
R_K = ((R * T) / (v - b)) - ((a) / (v * (v + b) * Sqr(T)))
End Function
[/VBA]

[gaross]

JKwan, I believe thats right. This would allow the user to enter the critical temperature and critical pressure of any compound and compute the pressure for a particular temperature and volume.

But, you never know; my instructor seems to change his mind in mid assignment. We'll see. Thanks a lot for your help. I have another post on here about mean and standard deviation. I think I'm more far gone on it than this one if you want to take a look.