arguments not working with function

[maweilian]

Question from a Newbie Excel programmer,

I have a function that has arguments, for instance,
Private Function myfunction(arg1 as Double, arg2 as Double, etc...)
Dim anyvariable as Double
anyvariable = arg1 - arg2
.....
End Function

What is happening in the function I am working on is that when I add a
breakpoint at the line "anyvariable = arg1 - arg2" and then inspect the
values of the arguments "arg1" and "arg2", I discover that they have values
of zero. (obviously, "anyvariable" also acquires a value of zero after the
execution of that line of code.) However, in the spreadsheet the values in
the cells referred to by the arguments are non-zero! Can anyone think of a
reason why the arguments are not acquiring the values of the cells to which
they point?

The only thing I can think of is that the cells to which the arguments point
do happen to contain values that are dependent on other VBA macros and that
this could be creating a problem.

Please help,
Will

 

[JLGWhiz]

Posting the relevant code will produce speedier and more effective
solutions.

 

[Patrick Molloy]

put a break point in the function
edit/enter the formula in the spreadsheet, then when the code halts at the
breakpoint, check the values.
also have Option Explicit at the top of your module

 

[maweilian]

The function below is called by:

=equalpressdepth(G9,0,D9,G10,0,G11,0,D10,G12,0,G13,G23,D12,G14,G24,G15,G25,D14,G16,G26,D16)

Some the cells that are referenced above contain values that are calculated
by other VBA macros.

Option Explicit
Private Function equalpressdepth(p1a1 As Double, p1p1 As Double, x1 As
Double, p2a1 As Double, p2p1 As Double, p2a2 As Double, p2p2 As Double, x2 As
Double, p3a1 As Double, p3p1 As Double, p3a2 As Double, p3p2 As Double, x3 As
Double, p4a1 As Double, p4p1 As Double, p4a2 As Double, p4p2 As Double, x4 As
Double, p5a1 As Double, p5p1 As Double, x5 As Double)
Dim w1 As Double
Dim w2 As Double
Dim w3 As Double
Dim w4 As Double
Dim test As Double
Dim prevpressactive1 As Double
Dim prevpresspassive1 As Double
Dim prevpressactive2 As Double
Dim prevpresspassive2 As Double
Dim prevpressactive3 As Double
Dim prevpresspassive3 As Double
Dim pressactive As Double
Dim presspassive As Double
equalpressdepth = 0
pressactive = 0
presspassive = 0
Do Until Abs(pressactive + presspassive) < 5 And Abs(presspassive) > 0
Select Case equalpressdepth
Case x1 To x2
w1 = p2a1 - p1a1
w2 = p1a1
w3 = p2p1 - p1p1
w4 = p1p1
pressactive = ((w1 * (equalpressdepth - x1) ^ 2) / (2 * (x2 -
x1))) + (w2 * (equalpressdepth - x1))
presspassive = ((w3 * (equalpressdepth - x1) ^ 2) / (2 * (x2 -
x1))) + (w4 * (equalpressdepth - x1))
Case x2 To x3
w1 = p3a1 - p2a2
w2 = p2a2
w3 = p3p1 - p2p2
w4 = p2p2
prevpressactive1 = (0.5 * (x2 - x1) * (p2a1 - p1a1)) + ((x2 -
x1) * p1a1)
prevpresspassive1 = (0.5 * (x2 - x1) * (p2p1 - p1p1)) + ((x2 -
x1) * p1p1)
pressactive = ((w1 * (equalpressdepth - x2) ^ 2) / (2 * (x3 -
x2))) + (w2 * (equalpressdepth - x2)) + prevpressactive1
presspassive = ((w3 * (equalpressdepth - x2) ^ 2) / (2 * (x3 -
x2))) + (w4 * (equalpressdepth - x2)) + prevpresspassive1
Case x3 To x4
w1 = p4a1 - p3a2
w2 = p3a2
w3 = p4p1 - p3p2
w4 = p3p2
prevpressactive1 = (0.5 * (x2 - x1) * (p2a1 - p1a1)) + ((x2 -
x1) * p1a1)
prevpresspassive1 = (0.5 * (x2 - x1) * (p2p1 - p1p1)) + ((x2 -
x1) * p1p1)
prevpressactive2 = (0.5 * (x3 - x2) * (p3a1 - p2a2)) + ((x3 -
x2) * p2a2)
prevpresspassive2 = (0.5 * (x3 - x2) * (p3p1 - p2p2)) + ((x3 -
x2) * p2p2)
pressactive = ((w1 * (equalpressdepth - x3) ^ 2) / (2 * (x4 -
x3))) + (w2 * (equalpressdepth - x3)) + prevpressactive1 + prevpressactive2
presspassive = ((w3 * (equalpressdepth - x3) ^ 2) / (2 * (x4 -
x3))) + (w4 * (equalpressdepth - x3)) + prevpresspassive1 + prevpresspassive2
Case x4 To x5
w1 = p5a1 - p4a2
w2 = p4a2
w3 = p5p1 - p4p2
w4 = p4p2
prevpressactive1 = (0.5 * (x2 - x1) * (p2a1 - p1a1)) + ((x2 -
x1) * p1a1)
prevpresspassive1 = (0.5 * (x2 - x1) * (p2p1 - p1p1)) + ((x2 -
x1) * p1p1)
prevpressactive2 = (0.5 * (x3 - x2) * (p3a1 - p2a2)) + ((x3 -
x2) * p2a2)
prevpresspassive2 = (0.5 * (x3 - x2) * (p3p1 - p2p2)) + ((x3 -
x2) * p2p2)
prevpressactive3 = (0.5 * (x4 - x3) * (p4a1 - p3a2)) + ((x4 -
x3) * p3a2)
prevpresspassive3 = (0.5 * (x4 - x3) * (p4p1 - p3p2)) + ((x4 -
x3) * p3p2)
pressactive = ((w1 * (equalpressdepth - x4) ^ 2) / (2 * (x5 -
x4))) + (w2 * (equalpressdepth - x4)) + prevpressactive1 + prevpressactive2 +
prevpressactive3
presspassive = ((w3 * (equalpressdepth - x4) ^ 2) / (2 * (x5 -
x4))) + (w4 * (equalpressdepth - x4)) + prevpresspassive1 + prevpresspassive2
+ prevpresspassive3
Case Else
Exit Do
End Select
equalpressdepth = equalpressdepth + 0.1
Loop
End Function

 

[maweilian]

I have added breakpoints and checked the values. The values are zero when
referencing cells of non-zero value. However, it does not exhibit this
behavior for all the arguments, which is puzzling to me. Most of the
arguments exhibiting this behavior contain values calculated by other VBA
macros. I do not know of this is the course of the problem. I don't see why
it would be a problem.

 

[Patrick Molloy]

for a test, make sure that the cells pointed at contain just numbers, no
formula, and see if their vales get passed properly