I already use Rob Bovey's excellent code cleaner [
http://www.appspro.com/Utilities/CodeCleaner.htm ] but I was wondering
whether something exists that checks for duplicate Set statements and
unnecessarily declared variables in a project?

I have a project that has evolved over a couple of weeks, and I've refined
it to be a lot smaller than it once was. The code is spread across 12
modules and I declare all the variables as Public in a module on their own.
The variables are mostly Workbooks, Worksheets and Ranges, but there are
also Integers and Longs, and there are a lot of them (100+). I know that
some of the variables aren't actually used in any of the modules, after the
refining that I've done to the project over the weeks.

The code is made up of one or two Sub procedures per module - some very
long - with a master Sub calling the other Subs in sequence. During testing
of the separate procedures I've had to copy in a lot of the Set statements
(to define Workbook, Worksheet and Range variables) at various points in
each procedure, so that I can test each Sub in isolation. But now that the
Project is finished I've put all these Set statements at the beginning of
the master Sub, which means that they're not now needed in the individual
Subs.

Short of using the Find tool in the VBE to look for unused variables and
duplicate Set statements, is there a quick of doing this?

TIA

Ian

Check out this addin. It will make your coding life a whole pile easier,
inclucing a feature to review your source code looking for unused variables
and such...

http://www.mztools.com/index.aspx
--
HTH...

Jim Thomlinson


"IanKR" wrote:

I already use Rob Bovey's excellent code cleaner [
http://www.appspro.com/Utilities/CodeCleaner.htm ] but I was wondering
whether something exists that checks for duplicate Set statements and
unnecessarily declared variables in a project?

I have a project that has evolved over a couple of weeks, and I've refined
it to be a lot smaller than it once was. The code is spread across 12
modules and I declare all the variables as Public in a module on their own.
The variables are mostly Workbooks, Worksheets and Ranges, but there are
also Integers and Longs, and there are a lot of them (100+). I know that
some of the variables aren't actually used in any of the modules, after the
refining that I've done to the project over the weeks.

The code is made up of one or two Sub procedures per module - some very
long - with a master Sub calling the other Subs in sequence. During testing
of the separate procedures I've had to copy in a lot of the Set statements
(to define Workbook, Worksheet and Range variables) at various points in
each procedure, so that I can test each Sub in isolation. But now that the
Project is finished I've put all these Set statements at the beginning of
the master Sub, which means that they're not now needed in the individual
Subs.

Short of using the Find tool in the VBE to look for unused variables and
duplicate Set statements, is there a quick of doing this?

TIA

Ian

Check out this addin. It will make your coding life a whole pile easier,
inclucing a feature to review your source code looking for unused
variables
and such...

http://www.mztools.com/index.aspx
--
HTH...

Jim Thomlinson

Many thanks for your very fast reply, Jim. I've tried the VBA add-in - it's
just what I needed!

Ian

There are pile of great features in that one. It is definitly worth spending
a bit of time investigating... It will save you hours if you do much coding.
--
HTH...

Jim Thomlinson


"IanKR" wrote:

Check out this addin. It will make your coding life a whole pile easier,
inclucing a feature to review your source code looking for unused
variables
and such...

http://www.mztools.com/index.aspx
--
HTH...

Jim Thomlinson

Many thanks for your very fast reply, Jim. I've tried the VBA add-in - it's
just what I needed!

Ian

On Thu, 31 Jul 2008 20:47:27 +0100, "IanKR" wrote:

I already use Rob Bovey's excellent code cleaner [
http://www.appspro.com/Utilities/CodeCleaner.htm ] but I was wondering
whether something exists that checks for duplicate Set statements and
unnecessarily declared variables in a project?

Short of using the Find tool in the VBE to look for unused variables and
duplicate Set statements, is there a quick of doing this?

Try http://www.mztools.com/index.aspx
--
Dick