Help on some simple code

[Tim]

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

 

[p45cal]

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.

 

[Joel]

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

 

[Bill Renaud]

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.

 

[Dana DeLouis]

If ...IsMissing(P) = True Then ...

If ...IsMissing(Z) = True Then ...

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.

 

[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

 

[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

 

[Bill Renaud]

#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.

 

[Tim]

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

 

[Dana DeLouis]

Rem P = Pressure, kPa

Rem Z = Gas Deviation Factor

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)

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 ...

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.