Worksheet Based Values

The Setting and Settings classes presented in the last chapter are a prime example of the worksheet-based approach. The advantage of this approach is that it removes literal values from the code. This allows someone to maintain or alter the literal values without using the VBE. You'll find this handy on occasions when the person who needs to modify the values isn't a developer and isn't comfortable working in the VBE. Alternatively, maybe you want to allow someone the ability to change these values but you don't want to give them the ability to view the code (i.e., you locked the VBA project for viewing). In these situations, you can place literal values on a worksheet and have procedures refer to the appropriate location on a worksheet to read the literal value.

NOTE Check out the Setting and Settings classes. They are presented in detail in Chapter 12.

If you don't need the comprehensive functionality provided by the Setting and Settings classes, you could do something as simple as defining a range name that refers to the range where the value is located. For example, let's say you created a range name "FISCAL_YEAR" that refers to a cell that stores the current fiscal year. You could easily retrieve the value in your code using the following statement.

ThisWorkbook.Names("Fisca1_Year").RefersToRange

In order to be a little more robust, you could validate that the name exists before using it in this manner. You may wonder if this really helps manage literal values. After all, isn't the name of the named range (Fiscal_Year) a literal value? Yes, the name of the named range is a literal value, but it's a good literal value if used correctly. The reason that this is a good literal value is that it's a literal value that can be validated before using it in your code. Additionally, it enables us to move the real value (i.e., fiscal year = 2003) into a more easily accessible place where it is easier to manage.

NOTE The use of named ranges is covered extensively in Chapter 8.

0 0

Post a comment