Problem with size limit of function's argument declared in VBA

[Nut MAO]

Hello,
Actually, I am using Microsoft Excel 2003. I have a problem with size limit
of function's argument declared in Microsoft Visual Basic Editor. Now I start
showing what I have done with my model before I determine my problem.

- In Micro Visual Basic Editor, I wrote functions and expressions. At the
end, I finished by writing the main function as follow:
Function AA(a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14,
a15, a16, a17, a18, a19, a20, a21, a22, a23, a24, a25, a26, a27, a28, a29,
a30) As Double
def
AA = f(a1, ..., a30) 'AA is functioned of a(i) which i varied
from 1 to 30
End Function

- I went to Excel Sheet and choose a Cell B1, for exemple, into which I
wrote (there are 2 cases):
i). =AA(A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, A11, A12, A13, A14, A15,
A16, A17, A18, A19, A20, A21, A22, A23, A24, A25, A26, A27, A28, A29, A30)
ii). = AA(A1:A30)

- I pressed ENTER to validate the formula but an error message "You've
entered too many arguments for this function" appears for the case 1. For the
cas 2, there is no problem after pressing ENTER. However, as i used Solver
and clicked on Solve button, a "Solver Results" window appears with this
message: "Access to VB Project denied at Cell B1. Learn more using the Solver
Model dialog Diagnosis tab

So I could not solve my problem. On the other hand, if i use only A1 to A29
as variables in my model, the problem could be solved with easy. So I could
say that I am having problem with size limit of function's argument declared
in VBA (in my model, it works only when the number of variables is less than
30).
I want to know if you have any suggestions to improve this situation.
Thanks in advance for you help.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...b28a297&dg=microsoft.public.excel.programming

 

[Joel]

the number of arguements in th efunction call on the worksheet must match the
number of arguements inthe declaration statement in VBA ( the first line to
the function). A function must be declared properly to accept multiple
number of arguements. Optional arguements have to be declared OPTIONAL. You
can use ParamArray as the lat arguement to contain multiple numberr of
arguements.

See VBA help "Function Statement"