Go Premium for a chance to win a PS4. Enter to Win


How to pass 30 arguments to a sub

Posted on 2011-03-03
Medium Priority
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

Question by:CRHIV
LVL 30

Expert Comment

ID: 35032222
If the sub supports 30 then do this

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


Author Comment

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

Expert Comment

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 23

Expert Comment

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


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.

LVL 11

Expert Comment

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.
LVL 45

Accepted Solution

patrickab earned 200 total points
ID: 35039196

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


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

879 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