Quote Originally Posted by Dowsey1977
I have a massive macro, that I am now looking at consolidating and improving. One way I have decided to do this is to declare some Public Constants, but I can't really work out how.

For example, one of the cells that I need to declare is D89, on the 'Form 6' sheet, which I want to be called InstType. So what would the code be to have to let me do this?
I am not sure I get you, but if you variables cannot refer to a worksheet cell within their declaration, the best you can do is load them within code. So you cannot do anything like

Dim myVar As String = Worksheets("Sheet1").Range("A1")
you have to do it as a declaration and a bit of code.

If you have massive macro, a couple of suggestions:
- add a separate module for you your public variables and constants
- break the macro down into many functionally separate macros
- maybe even create separate modules for separate functional categories and spread the macros over these.

.