Maximum Number of DIM and Set Usage

[brentjohn]

Greetings,
I am writing a Macro that may end up with 30-40 "DIM" and "Set". Is there a maximum number that can be used, or is there a shortcut to reduce the number. Ex:

Please Login or Register  to view this content.

[Kenneth Hobson]

The only limit is memory. It actually helps as Variant type takes up the most memory. I like it because it keeps your variable's case.

I usually dim r as Range and reuse it. If you have many ranges, you could put string addresses into an array and reuse when needed.

[brentjohn]

Kenneth,
I'll work with your suggestion and see what I come up with.

[Greg M]

Hi there,

For what it's worth, I'd say that a single routine which incorporates 30-40 variables is WAY too large, and is a prime candidate for splitting into several smaller routines.

Regards,

Greg M

[brentjohn]

Greg,
Can you give me direction as to how I can accomplish this.

[Greg M]

Hi again,

It's not really possible for me to make suggestions without sight of your code, but almost certainly your single 30-40 variable routine is performing several tasks (e.g. reading data from somewhere, processing that data, and then sending the processed data to somewhere else), If you look closely at how your routine works you can probably break it down into a small top-level routine which calls a data-reading routine and retrieves the data (e.g. in an array or a range), passes that array/range to a data-processing routine and retrieves an array/range of processed data. The top-level routine then passes that array/range of processed data to a data-output routine.

The advantage of this approach to coding is that it is much easier to test/debug/redesign individual small single-purpose routines than a large routine which incorporates several different tasks. When writing routines, a principle I try to adhere rigidly to is that big is definitely NOT beautiful!

Sorry I can't be more specific, but please feel free to ask about anything you think I might be able to help with.

Regards,

Greg M

[brentjohn]

Greg,
Thank you for your encouragement to research this problem! I came across the following which will reduce the number of Dim Statements. Also, can the number of Set variables be reduced as well?

Please Login or Register  to view this content.

[Greg M]

Hi again,

I think you may be mixing two separate issues here.


Issue one - the one raised in your original post:

Using an array (Tracker) instead of four separate variables reduces the number of Dim statements required, but doesn't really change anything - i.e. the computer still has to work with four variables, BUT, it makes life more complicated for YOU, because when writing your code you now have to deal with the non-intuitive indices 1-4 instead of conveniently-named variables (mt_Re_Obligation etc.)

You can't really change the number of Set statements - if you need to assign a value to a Range object, you need an associated Set statement, in just the same way that you need to use an equals sign if you want to assign a numeric value to a variable (iRowNo = 5). Yes, you can of course assign the values from an entire Range to an array with a single equals sign (vaDataValues = ActiveSheet.Range("A1:E10").Value), but that's quite a separate issue.


Issue two - the one I raised in my original post:

The point is not that there are too many Dim/Set statements in your single routine, but that there are too many variables in your single routine. This is the point I was making and the point I tried to describe (admittedly in very general terms) in my subsequent post.


Hope this helps, but as before, feel free to ask any further questions you might have.

Regards,

Greg M

[brentjohn]

Greg,
Again thank you for your insight and patience. I'll continue to work on it and see what I come up with.

[Greg M]

Hi again,

You're welcome. You know where to find me if you have further questions.

Regards,

Greg M