Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with size limit of function's argument declared in VBA
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/comm...el.programming |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with size limit of function's argument declared in VBA
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" "Nut MAO" wrote: 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/comm...el.programming |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I get around 30 argument limit of SUM function? | Excel Worksheet Functions | |||
Does excel VBA programme has a size limit? Or some other problems caused this problem? | Excel Programming | |||
Redesign for hitting limit argument in a UDF | Excel Programming | |||
Argument limit in Excel Function Wizard | Excel Programming | |||
Argument limit on user Function? | Excel Programming |