For Loop within a Function

[mboyd02255]

I am trying to write a basic function in the VBA editor in excel. It must have a loop inside it which creates values and then sums those values once the loop has completed. To understand what I am trying to do, I wrote a code in Matlab so you can see what I am trying to do. I have included comments and my attempt at the VBA code so far. Thanks in advance!

MATLAB CODE:

function binomial (k,n,p)

binomial=zeros(1,(n-k+1)); %allocate memory space for vector
for i=1:(n-k+1) %loop starts at 1, (n-k+1) ensures the loop is the correct length
    binomial(i)=(factorial(n))/(factorial(k+i-1)*factorial(n-(k+i-1)))*p^(k+i-1)*(1-p)^(n-(k+i-1)); %(k+i-1) is used to ensure the correct position of k and allow the matrix created to start adding in position (1,1)
end

sum(binomial) %sums all the odds of all posibilities of success

end

EXCEL CODE SO FAR:

Function binomial(k, n, p)
    
    For i = 1 To (n - k + 1)    'loop starts at 1, (n-k+1) ensures the loop is the correct length
        binomial(i) = (WorksheetFunction.Fact(n)) / (WorksheetFunction.Fact(k + i - 1) * WorksheetFunction.Fact(n - (k + i - 1))) * p ^ (k + i - 1) * (1 - p) ^ (n - (k + i - 1)) '(k+i-1) is used to ensure the correct position of k and allow the matrix created to start adding in position (1,1)
    Next i

End Function

Basically I don't know how to create an array in VBA, populate with it the values from the loop and then sum the array like I did in MATLAB.