Declaring Constants

[Dowsey1977]

Hi,

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?

The value in the cell may change, but the location won't vary.

Any help greatly appreciated.

[TonyJollans]

To do this you must name the cell in Excel (as opposed to VBA).

On sheet "Form 6", select "Insert > Names > Define" and name the cell in the dialog.

In code you can do Sheets("Form 6").Range("D89").Name = "InstType" which sets the name on the sheet, or you can set a Range variable to the cell and use that instead. The main difference between the two is lifetime - the Range variable lasts as long as the code is running, the cell name sticks with the cell.

[Bob Phillips]

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.

.

[Cyberdude]

To xld: Your advice to break up the "massive" macro into subordinate parts is exactly what I have the habit of doing, and I was wondering if it might be better to consolidate all the parts into one macro. Why do you think having a lot of smaller parts is advisable. Why would having multiple modules be a good thing?? I'm not challenging, just wondering.

[Bob Phillips]

To xld: Your advice to break up the "massive" macro into subordinate parts is exactly what I have the habit of doing, and I was wondering if it might be better to consolidate all the parts into one macro. Why do you think having a lot of smaller parts is advisable. Why would having multiple modules be a good thing?? I'm not challenging, just wondering.

Dude,

It's all about maintainability.

By breaking into separate macros, you create more discreet, more maintainable functions. This makes it easier to debug, and easier to amend at some future point.

More modules is taking that a step further, especially when you can identify functional categories as I suggested. In addition, modules break when they get too big, so it is best to avoid that problem by breaking them diown.

IMO, the correct paradigm is the class paradigm, but I know many people just can't see the rationale in that.

Continue what you are doing, don't regress

[TonyJollans]

... more discreet ... functions.

You mean one that don't tell all your secrets?