Solved

How to pass 30 arguments to a sub

Posted on 2011-03-03
6
287 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

760 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

16 Experts available now in Live!

Get 1:1 Help Now