Solved

How to pass 30 arguments to a sub

Posted on 2011-03-03
6
291 Views
Last Modified: 2012-05-11
I have a need to pass multiple arguments to a subroutine. But can't seem to get it to work.
I get "Wrong number of arguments or invalid property assignment"
Dim WToteCnt As Variant
  Dim WToteWt As Variant
  Dim WDrCnt As Variant
  Dim WDrWt As Variant
  Dim FDrCnt As Variant
  Dim FDrWt As Variant
  Dim CDrCnt As Variant
  Dim CDrWt As Variant
  Dim WFiveCnt As Variant       'Number of water fives
  Dim WFiveWt As Variant      'weight of water fives
  Dim FFiveCnt As Variant        'Number of flammable fives
  Dim FFiveWt As Variant        'weight of flammable fives
  Dim CFiveCnt As Variant       'Number of combustible fives
  Dim CFiveWt As Variant       'weight of combustible fives
  Dim CReducerOnesCnt As Variant
  Dim CReducerOnesWt As Variant
  Dim WOnesCnt As Variant        'Number of water ones
  Dim WOnesWt As Variant         'weight of water ones
  Dim FOnesCnt As Variant      'Number of flammable ones
  Dim FOnesWt As Variant       'weight of flammable ones
  Dim FRedOnesCnt As Variant     'Reducer ones   6/cs
  Dim FRedOnesWt As Variant      'Reducer weight
  Dim COnesCnt As Variant        'Number of combustible ones
  Dim COnesWt As Variant         'weight of combustible ones
  Dim FQrtCnt As Variant        'Number of flammable quarts
  Dim FQrtWt As Variant        'weight of flammable quarts
  Dim FPintCnt As Variant         'Number of flammable pints
  Dim FPintWt As Variant         'weight of flammable pints
  Dim WEaCnt As Variant
  Dim WEaWt As Variant
  Dim row As Integer             'Row count for DT
  Dim prow As Integer            'Rpw count for freight bill
  Dim Rname As Variant           'Reducer
  Dim Rlet As Variant            'First letter of flammable 1's(Currently we don't have
  Dim TotalWt As Variant                              ' anything that is not a reducer)Either 231,301
  Dim TotalDrCnt As Variant
  Dim CurSht As String

   Call CheckProd(FFiveCnt)

Open in new window

0
Comment
Question by:CRHIV
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35032222
If the sub supports 30 then do this

Call CheckProd(Arg1,Arg2,Arg3,Arg4,Arg5,......Arg30)

Sid
0
 

Author Comment

by:CRHIV
ID: 35032242
If they go to the sub, do they come back when the sub ends?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35032255
I would like to see the sub before I answer that :)

Sid
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Michael74
ID: 35033397
You could use an array, type or class.

from
http://www.cpearson.com/excel/classes.aspx

A Type is made up of other basic variable types. You may be familiar with Types from other programming languages, in which they are called a struct, structure, or record. For example, we could declare a Type that describes an employee:

Type Employee
    Name As String
    Address As String
    Salary As Double
End Type

This defines a single type named Employee which has three elements: Name, Address, and Salary. You can then create variables of the Employee type and give values to the elements. For example,

Dim Manager As Employee
Manager.Name = "Joe Smith"
Manager.Address = "123 Main Street"
Manager.Salary = 40000

you can then pass the type (eg employee) by reference which is the default for subs and any changes made to the values in the type will be visible to the rest of the application.

Michael
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 35035086
Whether variables can be changed in the called sub depends on your decalration in the arguments list. Variables listed with the ByVal keyword will not return changed, ByRef variables can be changed.
There is an exception: All Object variables are always passed ByRef, so can be modified in the called sub.
0
 
LVL 45

Accepted Solution

by:
patrickab earned 50 total points
ID: 35039196
CRHIV,

Make it simple. Pass an array of all the variables to the sub. That's faster and easier.

Patrick
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question