Increaseing the number of aurguments in a function

[Ian Mangelsdorf]

Hi all


I have written a Function to calculate a hypobolic function for a
given range of data ( in this case 5 samples). Is there a way I can
change this to accept any number of samples (I would like to accept at
least 15)? I had thought that a function using arrays might do it,
but unfortuanatly the theory behind arrays is doing my head in! here
is a copy of the function code wich I have achieved by brute force (
in summary its a funtion to add the sums of various combinations of x
and y)

Thanks for any help

Ian Mangelsdorf

Function Sw_hyp(P1, P2, p3, p4, p5, sw1, sw2, sw3, sw4, sw5, Pc_hyp)
y1 = P1
y2 = P2
y3 = p3
y4 = p4
y5 = p5
x1 = sw1
x2 = sw2
x3 = sw3
x4 = sw4
x5 = sw5



Sumx = x1 + x2 + x3 + x4 + x5
Sumy = y1 + y2 + y3 + y4 + y5
Sumx2 = x1 ^ 2 + x2 ^ 2 + x3 ^ 2 + x4 ^ 2 + x5 ^ 2
Sumy2 = y1 ^ 2 + y2 ^ 2 + y3 ^ 2 + y4 ^ 2 + y5 ^ 2


Sumxy = (x1 * y1) + (x2 * y2) + (x3 * y3) + (x4 * y4) + (x5 * y5)


'calculate x squared y's

xSy1 = x1 ^ 2 * y1
xSy2 = x2 ^ 2 * y2
xSy3 = x3 ^ 2 * y3
xSy4 = x4 ^ 2 * y4
xSy5 = x5 ^ 2 * y5

Sumx2y = xSy1 + xSy2 + xSy3 + xSy4 + xSy5

'calculate x y squared

xyS1 = y1 ^ 2 * x1
xyS2 = y2 ^ 2 * x2
xyS3 = y3 ^ 2 * x3
xyS4 = y4 ^ 2 * x4
xyS5 = y5 ^ 2 * x5

Sumxy2 = xyS1 + xyS2 + xyS3 + xyS4 + xyS5


'calculate x sq y sq

xSyS1 = y1 ^ 2 * x1 ^ 2
xSyS2 = y2 ^ 2 * x2 ^ 2
xSyS3 = y3 ^ 2 * x3 ^ 2
xSyS4 = y4 ^ 2 * x4 ^ 2
xSyS5 = y5 ^ 2 * x5 ^ 2


Sumx2y2 = xSyS1 + xSyS2 + xSyS3 + xSyS4 + xSyS5


number1 = Sumx2 * (Sumxy * Sumxy2 - Sumy * Sumx2y2) + Sumxy * (Sumx *
Sumx2y2 - Sumxy * Sumx2y) + Sumx2y * (Sumy * Sumx2y - Sumx * Sumxy2)
number2 = samples * (Sumx2y * Sumxy2 - Sumxy * Sumx2y2) + Sumx * (Sumy
* Sumx2y2 - Sumxy * Sumxy2) + Sumxy * (Sumxy ^ 2 - Sumy * Sumx2y)
number3 = samples * (Sumx2 * Sumxy2 - Sumxy * Sumx2y) + Sumx * (Sumy *
Sumx2y - Sumx * Sumxy2) + Sumxy * (Sumx * Sumxy - Sumy * Sumx2)
denomin = samples * (Sumx2y ^ 2 - Sumx2 * Sumx2y2) + Sumx * (Sumx *
Sumx2y2 - Sumxy * Sumx2y) + Sumxy * (Sumxy * Sumx2 - Sumx * Sumx2y)


a = number1 / denomin
b = number2 / denomin
C = number3 / denomin



Sw_hyp = (a - Pc_hyp) / (C * Pc_hyp - b)

End Function

 

[Bob Phillips]

Ian,

Look at 'Understanding Parameter Arrays' in VBA Help, I think this will help
you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dana DeLouis]

Don't know if this would be of interest. Just to add...If you break your
numbers into arrays, you might be able to do something along this line...

' perhaps like this...
tx = Array(x1, x2, x3, x4, x5)
ty = Array(y1, y2, y3, y4, y5)

With WorksheetFunction
sumx = .Sum(tx)
Sumy = .Sum(ty)

Sumx2 = .SumSq(tx)
Sumy2 = .SumSq(ty)

Sumxy = .SumProduct(tx, ty)
'...etc
End With

instead of:

Sumx = x1 + x2 + x3 + x4 + x5
Sumy = y1 + y2 + y3 + y4 + y5
Sumx2 = x1 ^ 2 + x2 ^ 2 + x3 ^ 2 + x4 ^ 2 + x5 ^ 2
Sumy2 = y1 ^ 2 + y2 ^ 2 + y3 ^ 2 + y4 ^ 2 + y5 ^ 2
Sumxy = (x1 * y1) + (x2 * y2) + (x3 * y3) + (x4 * y4) + (x5 * y5)

(Which function is this? I don't recognize it ;>O )

 

[Dana DeLouis]

Don't know if this would work for you, but here is just an idea.

Function Sw_hyp(tx, ty, Pc_hyp)

With WorksheetFunction

sumx = .Sum(tx)
sumy = .Sum(ty)
sumx2 = .SumProduct(tx, tx)
sumy2 = .SumProduct(ty, ty)

sumxy = .SumProduct(tx, ty)

sumx2y = .SumProduct(tx, tx, ty)
Sumxy2 = .SumProduct(tx, ty, ty)

Sumx2y2 = .SumProduct(tx, tx, ty, ty)

'number1 = Your same function...
'number2 = Your same function...
'number3 = Your same function...
'denomin = Your same function...

Sw_hyp = (number1 - denomin * Pc_hyp) / (-number2 + number3 * Pc_hyp)

End With
End Function



Sub TestIt()
Dim Xs, Ys
Xs = Array(9, 8, 7, 6, 5)
Ys = Array(1, 2, 3, 4, 5)

Debug.Print Sw_hyp(Xs, Ys, 1)
End Sub


I removed the three variable (a,b,c) because dividing by donomin is mostly
factored out in the final equation.

a = number1 / denomin
b = number2 / denomin
C = number3 / denomin
Sw_hyp = (a - Pc_hyp) / (C * Pc_hyp - b)

I don't know what the underlying equation is for number1, number2..etc is.

 

[Ian Mangelsdorf]

Tahnks Dana

The underlying equation is for modeling the saturation of rock samples
at various pressures. It lloks like a dogs breakfast but usually works
quite well. Ill give your suggestions a go

Cheers

Ian