MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Subs with a non-predetermined number of arguments?

  Asked By: Rena    Date: Dec 05    Category: MS Office    Views: 1131

I have written a sub which links the cells from two given columns in a
range I call rngSourceTable to a different one rngTargetTable.

The sub takes the inputs (rngSourceTable As Range, rngTargetTable as
Range, SourceColumn1 As Long, SourceColumn2 As Long, TargetColumn1 As
Long, TargetColumn2 As Long)

This works because it just so happens that in the current application
I need to copy only two columns from the source to the target.

But of course I like to generalise my subs as much as possible so I
can use them frequently, so I'd like if possible to write it in a way
such that I could list as many columns as I like. If I wanted three
columns I'd need six numbers, three to specify which columns I'm
reading from in rngSourceTable and three more to specify which columns
I'm writing to in rngTargetTable.

Now an obvious way to achieve that would be just to write the sub to
take a single pair of source column and target column inputs, and call
the sub lots of times if I want to process lots of columns.

Mainly just for the fun of learning how to do something different, can
anyone explain to me whether it is possible or not in VBA to write a
sub which would be smart enough to take as many input numbers as you
feed it (as long as there are an even number of them) and interpret
the first half as the coordinates of the source and the second half as
the target?



1 Answer Found

Answer #1    Answered By: Adanalie Garcia     Answered On: Dec 05

What you want is the ParamArray. This will take your list  of parameters and
put them into an array. Checking for even numbers  and associating source
and target  with individual parameters would be up to you.

Do a Google search for - excel paramarray

An alternative is to provide a comma-separated list as a string, and just
split it at the commas using Split.

Didn't find what you were looking for? Find more on Subs with a non-predetermined number of arguments? Or get search suggestion and latest updates.