Hi all...coming for some advice from the community.

I have just finished updating a huge Excel VBA program (38,000+ code lines). I uncovered a mixture of coding practices as the project is 10 years old. I was hoping to get some advice from people.

There are 30+ individual workbooks with references to one master workbook. This master workbook contains dozens of public variable declarations and constants for use by all workbooks.

Data and variable storage
Data is stored and called using a variety of methods. I would like to migrate everything to one method so that redundant approaches can be removed and procedures / functions can be reduced to a common set :

- INI files used for data storage (paths to be used for file open save)
- Registry used for data storage (paths to be used for file open save)
- CustomDocumentProperty used for data storage (relating to the file)
- Public Constant / Variables declarations for sharing values between workbooks
- Class modules used for storage related to the program
- Types used for storage related to program

The use of INI should be replaced with Registry. The use of Class modules vs type vs public declaration...what is the best method to use?

Referencing ranges and objects

Referencing is also a very mixed bag :

- With, End With used for referencing objects
- Range("Worksheet!Hello") string used for referencing
- Workbooks().Worksheets().Range() used for direct referencing
- Set x as Range used for referencing objects
- [A1] short referencing methods used
- .ActiveCell usage throughout

I would say that Setting of objects is the best method for multiple operations. And the use of full Workbook().Worksheet().Range() method instead of constructing a string within Range(). The use of active object should be removed.

Any advice on all of the above appreciated...