Help on some simple code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to add a new module(funktion) in a set of existing modules, compiled
together as excel add-in.
The formula is called "PET_GAS_COMPRESS_Cg" and is as follow:

Cg = 1/P -((1/Z)*(deltaZ/deltaP)
where: deltaZ = Zi - Zi-1
and deltaP = Pi - Pi-1

The values of "P" will be as column somewhere in worksheet (I dont know
where; I want to click and choose it)
The values of "Z" will be as column somewhere in worksheet (I dont know
where; I want to click and choose it)

What I have written so far is:

Function Pet_GAS_COMPRESS_Cg(P, Z)
Rem File Sait Petroleum Functions (.xls & .xla)
Rem Gas Compressibility (Not to be confussed with "Z" factor)
Rem P = Pressure, kPa
Rem Z = Gas Deviation Factor
Rem Written by: Patrioti

Rem Test for Errors
If IsNumeric(P) = False Or IsMissing(P) = True Then GoTo perr:
If IsNumeric(Z) = False Or IsMissing(Z) = True Then GoTo zerr:
If P < 0 Then GoTo perr:
If Z <= 0 Then GoTo zerr:
GoTo starthere:
perr: Pet_GAS_COMPRESS_Cg = "**Problem: P Outside Range": GoTo hereend:
zerr: Pet_GAS_COMPRESS_Cg = "**Problem: Z Outside Range": GoTo hereend:

starthere:
If Z(i - 1) < 0 Then Cg = 1 / P(i)
deltaZ = Z(i) - Z(i - 1)
deltaP = P(i) - P(i - 1)
Cg = 1 / P - ((1 / Z) * deltaZ / deltaP)
hereend: End Function


The result is always zero where it should be a number. What I am missing?

TIA

Tim
 
Where is the function getting its value of i from?

Put a break point near the beginning of the function, say at line:
If IsNumeric(P) = False Or IsMissing(P) = True Then GoTo perr:

then when when it's called it will stop there and show you the line,
highlighted in yellow. At this point, you can step through one line at a time
using the F8 key and observe the Locals window to see whether the variables
hold the values you expect.
 
Good programming practice are to aviod goto statements. Not sure what you
problem is but I re-wrote your code using good programming practices

Function Pet_GAS_COMPRESS_Cg(P, Z)
Rem File Sait Petroleum Functions (.xls & .xla)
Rem Gas Compressibility (Not to be confussed with "Z" factor)
Rem P = Pressure, kPa
Rem Z = Gas Deviation Factor
Rem Written by: Patrioti

Rem Test for Errors
If (IsNumeric(P) = False) Or (IsMissing(P) = True) Or _
(P < 0) Then
Pet_GAS_COMPRESS_Cg = "**Problem: P Outside Range"
Else

If (IsNumeric(Z) = False) Or (IsMissing(Z) = True) Or _
(Z <= 0) Then
Pet_GAS_COMPRESS_Cg = "**Problem: Z Outside Range"
Else

If Z(i - 1) < 0 Then Cg = 1 / P(i)
deltaZ = Z(i) - Z(i - 1)
deltaP = P(i) - P(i - 1)
Cg = 1 / P - ((1 / Z) * deltaZ / deltaP)
End If
End If
End Function
 
I think your main problem is that you did not use the name of the
function for the return value, so the line of code:

Cg = 1 / P - ((1 / Z) * deltaZ / deltaP)

should be

Pet_GAS_COMPRESS_Cg = 1 / P - ((1 / Z) * deltaZ / deltaP)

Also, how do you get Z(i), Z(i-1), P(i), and P(i-1)? These are arrays,
so you need to specify a value for i somehow.
You probably should re-declare your function to be something like:

Function Pet_GAS_COMPRESS_Cg(P0, Z0, P1, Z1)

where P0 is the Pressure at the previous point, and P1 is the current
Pressure; and the same for Z0 and Z1.

I would also put Option Explicit at the top of the code module and
declare variables for deltaZ and deltaP:

dim deltaZ as Double
dim deltaP as Double

Single-step through the routine, and you should be able to find any
other errors, as previous posters have suggested.
 
If ...IsMissing(P) = True Then ...
If ...IsMissing(Z) = True Then ...
Click to expand...

Just to add. If one uses IsMissing, one usually uses "Optional" as in...

Function Pet_GAS_COMPRESS_Cg(Optional P, Optional Z)

End the function similar to:

Pet_GAS_COMPRESS_Cg = Cg
End Function

It appears you did not pass the values of Z, DeltaZ, or DeltaP to the
function.
 
Thanks all you guys for the answers.
I was experimenting around but without success.
I was getting constantly errors like "?Value" and "#Name"
When I saved the module in add-in and try to reopen excel, a fatal error
occurred, later on, the excel was able to open but my module was not there.
What I think the problems are two:
The name of the function is somehow not correct
and last six lines of the code are screwed as well.

Sorry for my ignorance as I am a begginer in VBA

Tim
 
Thanks all you guys for the answers.
I was experimenting around but without success.
I was getting constantly errors like "?Value" and "#Name"
When I saved the module in add-in and try to reopen excel, a fatal error
occurred, later on, the excel was able to open but my module was not there.
What I think the problems are two:
The name of the function is somehow not correct
and last six lines of the code are screwed as well.

Sorry for my ignorance as I am a begginer in VBA

Tim
 
#NAME? errors occur because you have undefined variables in your routine
(i.e. deltaP, deltaZ, i).
#VALUE! errors occur because your function is producing an invalid value
somewhere. For example, attempting to divide by 0, or not supplying a
required argument when calling the function from a worksheet formula.

Save your code as a normal Excel workbook (*.XLS) for a while, until you
have all of the bugs out. Convert to an add-in only when the code is
robust.
 
OK, I changed the code as follow, but I am still getting #Value error only in
the first cell, although I am trying to state that if the previous cell has
not a numerical value then Cg = 1/ P, otherwise Cg = 1/P -
(1/Z)(deltaZ/deltaP)
Other cells are calculated OK.

Function Pet_GAS_COMPRESS_Cg(p, z)
Rem File Sait Petroleum Functions (.xls & .xla)
Rem Gas Compressibility (Not to be confussed with "Z" factor)
Rem P = Pressure, kPa
Rem Z = Gas Deviation Factor
Rem Written by: Patrioti

Rem Test for Errors

If (IsNumeric(p) = False) Or (IsMissing(p) = True) Or _
(p < 0) Then
Pet_GAS_COMPRESS_Cg = "**Problem: P Outside Range"
Else

If (IsNumeric(z) = False) Or (IsMissing(z) = True) Or _
(z <= 0) Then
Pet_GAS_COMPRESS_Cg = "**Problem: Z Outside Range"
Else

Dim i As Integer
i = 1

If (IsNumeric(p(i - 1)) = False) Or (IsMissing(p(i - 1)) = True) Or
((p(i - 1)) <= 0) Or _
(IsNumeric(z(i - 1)) = False) Or (IsMissing(z(i - 1)) = True) Or ((z(i -
1)) <= 0) Then Cg = 1 / p(i)

deltaZ = z(i) - z(i - 1)
deltaP = p(i) - p(i - 1)
Cg = 1 / p - ((1 / z) * deltaZ / deltaP)
Pet_GAS_COMPRESS_Cg = Cg

End If
End If
End Function
 
Rem P = Pressure, kPa
Rem Z = Gas Deviation Factor
Click to expand...

Hi. I may be wrong, but it appears you are passing single values to P & Z.
ie > If (IsNumeric(p) = False)

But here, it looks like they are arrays.
deltaZ = z(i) - z(i - 1)
deltaP = p(i) - p(i - 1)
Click to expand...

If you are passing 2 arrays to the function, then the parts
(IsNumeric(p) = False) ...and (p < 0) don't make sense.

How exactly are you calling the function?
the first cell, although I am trying to state that if the previous cell
has ...
Click to expand...

It sounds like you are calling the function from the worksheet.
Are you trying to have p(i-1) the previous cell?

Not neccessary of course, but just an idea here...

Const Error_Z = "**Problem: Z Outside Range"
Const Error_P = "**Problem: P Outside Range"

If Not IsNumeric(p) Or IsMissing(p) Or (p < 0) Then
Pet_GAS_COMPRESS_Cg = Error_P
Else

Since "i" doesn't change, it may be easier to read by removing "i" and using
something like this:

deltaZ = z(1) - z(0)
deltaP = p(1) - p(0)

Again, just some thoughs.
 
Back
Top