Solved

How to pass 30 arguments to a sub

Posted on 2011-03-03
6
288 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now