Functions using variables in arrays

[Aaron]

I have a list of variables(numbers) in an excel spreadsheet. The
variables/numbers are listed in 6 different cells in 1 column (i.e. its a 1D
array). In VBA i have a function that uses these numbers as part of the
equation. It is better explained graphically below:

Excel: I VBA Function:
Column I SbeamSupport(xP, P, xS) = P *
(1 - (xP / xS))
Row A B C etc I
1 0 I
2 4 I
3 8 I
4 12 I
5 16 I
6 20 I
etc I

The values of P and xS in the VBA function are constants but the value of xP
is variable. The numbers in the excel worksheet represent this variable. So
basically i want to calculate the value of SbeamSupport for each value of xS
and display the answers to the function next to each respective variable in
the excel worksheet (i.e. column B). I realize it would be easier by simply
doing this in excel, but its an exam question at uni and this is what we have
to do.

I have written code to grab the values from excel and put them into an array
using a For-To loop. The problem i am having is when i try to include the
function in this loop to determine the SbeamSupport value for each variable,
i get an 'out of stack space' error. My code looks something like this:

For i = 0 To 5

xPArray(i) = Worksheets("Question 3").Cells(1 + i, 1).Value
xP = xPArray(i)
Worksheets("Question 3").Cells(1 + i, 2).Value = SbeamSupport(xP, P,
xS)

Next i

I could simply neglect loops altogether and determine each value
individually, but it is not very efficient and would take ages if i were
using a large array of values.

Thanks to anyone who can help.

 

[SandFly]

Well I am not quite sure what your function is trying to do so I
decided to multiply
try this and fit to your needs:

Sub CalcSbeam()
Dim i As Integer
Dim xPArray(5) As Long

' Fill your array with varaibles
For i = 0 To 5
xPArray(i) = Worksheets("Question 3").Cells(1 + i, 1).Value
Next

'send your array to the function to be processed
SbeamSupport xPArray

' put your results where they belong
For i = 0 To 5
Worksheets("Question 3").Cells(1 + i, 2).Value = xPArray(i)
Next
End Sub

Function SbeamSupport(sBeam() As Long)
Dim i As Integer
Const P = 5
Const xS = 3

For i = 0 To UBound(sBeam())
sBeam(i) = sBeam(i) * P * xS
Next
End Function