Passing large number of variables to function

[RB Smissaert]

Trying to find the best solution to the following:
I have a number of Subs in different modules that are using the same
function.
These Subs have to pass a large number of variables of different types
(boolean, byte, long, string) to this function.
I can't use these variables directly as arguments in the function as there
will be an error, number of arguments too large.
I could solve this by making a UDT, but the problem is that this UDT then
has to be declared publicly to be accessible
from the different modules. This seems to be against the general principle
that the number of public variables should be as small as possible.
Another option would be to use a number of arrays, one for each datatype,
but this involves a lot more coding.
Any suggestions what the best approach is for this?

RBS

[Jon Peltier]

I'd say you should either do the array coding, or perhaps investigate
passing all the variables within a collection.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


RB Smissaert wrote:

> Trying to find the best solution to the following:
> I have a number of Subs in different modules that are using the same
> function.
> These Subs have to pass a large number of variables of different types
> (boolean, byte, long, string) to this function.
> I can't use these variables directly as arguments in the function as
> there will be an error, number of arguments too large.
> I could solve this by making a UDT, but the problem is that this UDT
> then has to be declared publicly to be accessible
> from the different modules. This seems to be against the general
> principle that the number of public variables should be as small as
> possible.
> Another option would be to use a number of arrays, one for each
> datatype, but this involves a lot more coding.
> Any suggestions what the best approach is for this?
>
> RBS

[RB Smissaert]

Thanks, maybe the different arrays are indeed the best option. At least it
will keep all the variables local.
If I want to keep the meaningful variable names though it would involve
quite a bit of coding like:

In the Subs:

arr1(0) = var0
arr1(1) = var1
arr2(0) = var2
etc.

and the reverse process in the function.

Would it somehow be possible to loop through a number of variables, I mean
the usual variables declared
like: dim strVar1 as String?

RBS


"Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
news:OjSPV5glFHA.3656@TK2MSFTNGP09.phx.gbl...
> I'd say you should either do the array coding, or perhaps investigate
> passing all the variables within a collection.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> RB Smissaert wrote:
>
>> Trying to find the best solution to the following:
>> I have a number of Subs in different modules that are using the same
>> function.
>> These Subs have to pass a large number of variables of different types
>> (boolean, byte, long, string) to this function.
>> I can't use these variables directly as arguments in the function as
>> there will be an error, number of arguments too large.
>> I could solve this by making a UDT, but the problem is that this UDT then
>> has to be declared publicly to be accessible
>> from the different modules. This seems to be against the general
>> principle that the number of public variables should be as small as
>> possible.
>> Another option would be to use a number of arrays, one for each datatype,
>> but this involves a lot more coding.
>> Any suggestions what the best approach is for this?
>>
>> RBS

[RB Smissaert]

I could automate the coding of all this (the array method) in the Sheet and
then paste to the VBE, that should be simple and quick.

RBS

"Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
news:OjSPV5glFHA.3656@TK2MSFTNGP09.phx.gbl...
> I'd say you should either do the array coding, or perhaps investigate
> passing all the variables within a collection.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> RB Smissaert wrote:
>
>> Trying to find the best solution to the following:
>> I have a number of Subs in different modules that are using the same
>> function.
>> These Subs have to pass a large number of variables of different types
>> (boolean, byte, long, string) to this function.
>> I can't use these variables directly as arguments in the function as
>> there will be an error, number of arguments too large.
>> I could solve this by making a UDT, but the problem is that this UDT then
>> has to be declared publicly to be accessible
>> from the different modules. This seems to be against the general
>> principle that the number of public variables should be as small as
>> possible.
>> Another option would be to use a number of arrays, one for each datatype,
>> but this involves a lot more coding.
>> Any suggestions what the best approach is for this?
>>
>> RBS

[Niek Otten]

You can declare the Type Publicly, but Dim the variables of that type
Locally

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:OyDV%23vglFHA.3936@TK2MSFTNGP10.phx.gbl...
> Trying to find the best solution to the following:
> I have a number of Subs in different modules that are using the same
> function.
> These Subs have to pass a large number of variables of different types
> (boolean, byte, long, string) to this function.
> I can't use these variables directly as arguments in the function as there
> will be an error, number of arguments too large.
> I could solve this by making a UDT, but the problem is that this UDT then
> has to be declared publicly to be accessible
> from the different modules. This seems to be against the general principle
> that the number of public variables should be as small as possible.
> Another option would be to use a number of arrays, one for each datatype,
> but this involves a lot more coding.
> Any suggestions what the best approach is for this?
>
> RBS

[RB Smissaert]

Yes, I can now see the pros and cons of both methods.
The array method will involve more code, but it looks a bit cleaner (less
dots as well) and I will try that first.

RBS

"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:Oot%23nkhlFHA.2156@TK2MSFTNGP14.phx.gbl...
> You can declare the Type Publicly, but Dim the variables of that type
> Locally
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:OyDV%23vglFHA.3936@TK2MSFTNGP10.phx.gbl...
>> Trying to find the best solution to the following:
>> I have a number of Subs in different modules that are using the same
>> function.
>> These Subs have to pass a large number of variables of different types
>> (boolean, byte, long, string) to this function.
>> I can't use these variables directly as arguments in the function as
>> there will be an error, number of arguments too large.
>> I could solve this by making a UDT, but the problem is that this UDT then
>> has to be declared publicly to be accessible
>> from the different modules. This seems to be against the general
>> principle that the number of public variables should be as small as
>> possible.
>> Another option would be to use a number of arrays, one for each datatype,
>> but this involves a lot more coding.
>> Any suggestions what the best approach is for this?
>>
>> RBS
>
>