Named Constants
Using named constants is an element of good spreadsheet design. By having a constant in one place, i.e. a defined name, you only need to change it in one place, should the need arise. This can help prevent errors in your spreadsheets.
To define a named constant, select Insert>Name>Define from the menu. Here, I’ve created a defined name called SalesTax and set it equal to 7%.
I can use this named constant in formulas. In the example below, I extend line amounts adding the sales tax.
Now when the government decides to increase the sales tax, I need only to change the named constant, not every formula on the sheet. In the example below, I’ve redefined SalesTax to =.075 to represent a 1/2% increase in the sales tax.
If you don’t know what sales tax is, you probably pay VAT. I think they’re roughly the same.
Jamie Collins:
Dick, You’ve been coming up with some interesting topics lately!
I too use ‘named constants’ but I’ve found them problematic when accessing their values in VBA code because they require a distinct approach from a name that refers to a range object.
For example, in a recent project my defined names were EITHER range objects OR long integers. To simplify (at a higher level) my VBA code, I wrote my own Name class to wrap my Excel.Name objects. My Value property was as follows:
Public Property Get Value() As Variant
Select Case m_NameType
Case jcNameTypeRange
Value = m_oExcelName.RefersToRange.Value
Case jcNameTypeLong
‘ Value = Application.Evaluate(m_oExcelName) ‘Error 2015
Value = CLng(Mid$(m_oExcelName.RefersTo, 2))
End Select
End Property
The above requires each name to be flagged, indicating whether the underlying Excel.Name object refers to a range or a constant (Long). The code would have been more complex if I had data types other than Longs! The commented out line shows my attempt to use the Application.Evaluate to do the job but I kept getting errors.
Is there a more elegant solution?
Jamie.
–
11 June 2004, 5:32 amshades:
John Walkenbach has this in Power Programming, p. 306-7. Will this move in the right direction?
Function CellType(Rng)
11 June 2004, 8:43 am‘ Returns the cell type of the upper left
‘ cell in a range
Application.Volatile
Set Rng = Rng.Range(”A1″)
Select Case True
Case IsEmpty(Rng): CellType = “Blank”
Case Application.IsText(Rng): CellType = “Text”
Case Application.IsLogical(Rng): CellType = “Logical”
Case Application.IsErr(Rng): CellType = “Error”
Case IsDate(Rng): CellType = “Date”
Case InStr(1, Rng.Text, “:”) <> 0: CellType = “Time”
Case IsNumeric(Rng): CellType = “Value”
End Select
End Function
Dick:
Jamie: I use this
Function IsRangeName(nmInput As Name) As Boolean
On Error Resume Next
IsRangeName = Not Range(nmInput.Value) Is Nothing
On Error GoTo 0
End Function
All it tells me is if the name refers to a range. I’ve never had cause to know what data type it is. What’s a situation where that would matter?
11 June 2004, 9:44 amJamie Collins:
Dick,
I could’ve explained better I suppose.
It matters to know what the data type is because the value of a named constant is always held as a string.
Here’s an example using code in the Immediate Window of a new blank workbook.
Set up my names, one referring to a range, the other a constant, both equal to 1.
Sheet1.Range(”A1″).Value = 1
ThisWorkbook.Names.Add “MyName1″, Sheet1.Range(”A1″)
ThisWorkbook.Names.Add “MyName2″, 1
If I didn’t know better I would expect to be able to use their Value property:
? ThisWorkbook.Names(”MyName1″).Value
=Sheet1!$A$1
? ThisWorkbook.Names(”MyName2″).Value
=1
Neither has returned the answer of 1! Another approach is required. Actually two approaches, one for the range and one for the constant.
For the range:
? ThisWorkbook.Names(”MyName1″).RefersToRange.Value
1
That’s good.
For the constant:
? Mid$(ThisWorkbook.Names(”MyName2″).RefersTo,2)
1
However, it’s the wrong data type i.e.
? TypeName(Mid$(ThisWorkbook.Names(”MyName2″).RefersTo,2))
String
So I have to convert, using prior knowledge that the value is an integer:
? CLng(Mid$(ThisWorkbook.Names(”MyName2″).RefersTo,2))
This is the basis of the code I posted earlier i.e. to get a consistent Value property for ranges and constants. There must be an easier way! I wish I remember why Application.Evaluate didn’t work out…
Jamie.
–
11 June 2004, 10:23 amPaul Mayfield:
I’ve found that the typical user of my spreadsheets doesn’t like this approach. For them, this information is perceived as “hidden” and should instead be set up on an “inputs”or “assumptions” sheet.
The less proficient excel users need to be able to easily see the value of any constant. Most of them wouldn’t know how to find the value of your constant to check it.
11 June 2004, 10:44 amPaul Mayfield:
I’ve found that the typical user of my spreadsheets doesn’t like this approach. For them, this information is perceived as “hidden” and should instead be set up on an “inputs” or “assumptions” sheet.
The less proficient excel users need to be able to easily see the value of any constant. Most of them wouldn’t know how to find the value of your constant to check it.
11 June 2004, 10:44 amPaul Mayfield:
Oops, sorry about the double post…
11 June 2004, 10:46 amDick:
“why Application.Evaluate didn’t work out…”
Just messing around with it a little. I get the same error. If you Evaluate the Value property of the Name object, no error. Evaluate always seems to return Double for numerics, which doesn’t really surprise me. Try this code
Sub test()
Dim i As Long
Sheet1.Range(”A1″).Value = 1
ThisWorkbook.Names.Add “Nm1″, Sheet1.Range(”A1″)
ThisWorkbook.Names.Add “Nm2″, 1
ThisWorkbook.Names.Add “Nm3″, “String1″
For i = 1 To 3
With Application
Debug.Print Names(”Nm” & i).Name, _
.Evaluate(Names(”Nm” & i).Value), _
.Evaluate(Names(”Nm” & i)), _
TypeName(.Evaluate(Names(”Nm” & i).Value))
End With
Next i
End Sub
11 June 2004, 11:57 amJuan Pablo:
Evaluate(”Nm” & i)
works too…
11 June 2004, 4:04 pmJamie Collins:
“If you Evaluate the Value property of the Name object, no error.”
I think I have my answer! Thanks, Dick.
Jamie.
–
14 June 2004, 4:49 amGeorge Gruber:
I just spent about half an hour pulling out my hair. I used Evaluate to return the result of a constant (Insert -> Name -> Define). I was trying to determine what the result would be if the name didn’t exist. If you do not have a On Error Goto within the function of the Evaluate, it hangs up something (probably VBA). I would have to close the spread sheet and open it again. I am guessing that there is an issue with the error message processing in VBA or Excel when the Evaluate failed.
This is Excel 2003.
I picked your page because of the reference of using named constants.
8 December 2006, 6:52 amIvan Camac:
Looking at the help topic for ‘Evaluate’, you can also simply put the name of the constant / range in square brackets to use the value. Dick’s example would therefore become:
Sub test()
Sheet1.Range(”A1″).Value = 1
ThisWorkbook.Names.Add “Nm1″, Sheet1.Range(”A1″)
ThisWorkbook.Names.Add “Nm2″, 1
ThisWorkbook.Names.Add “Nm3″, “String1″
Debug.Print [Nm1]
Debug.Print [Nm2]
Debug.Print [Nm3]
End Sub
19 November 2007, 8:57 pmPaul:
I have a constant array, at least a group of 3 columns, with constants in each cell that I want to use for lookup. I created a name, ObsInstances, for the array to include the range. I have a cell used for the lookup value, H3, and a cell with the formula “=VLOOKUP($H3,ObsInstances,2,FALSE)”. The formula didn’t produce anything, the cell always shows the formula. When I copied the cell to other cells the relative reference did not increment, second clue of trouble. I copied the spreadsheat to “play” because one is a model with no data. So in the copy I entered data and it still showed the formula instead of the search result but when I used Tool/Evaluate Formula it showed the formula correctly but said, “The cell currently being evaluated contains a constant”. I played around deleting blanks and such and the highlighting started to work then the formula worked. Trouble is I can’t figure out whats wrong because the formula does look any different but now works and I can’t get repeat the miracle in the model spreadsheet. Anybody have any ideas about what I can do here?
6 June 2008, 5:02 pmRob van Gelder:
Paul
9 June 2008, 5:26 amIt could be that your cell is formatted as Text.
Right-click the cell, choose Format Cells, then from the Number tab, set the format to General.
Jon Peltier:
After convertig the cell to a numeric format, you still may need to convert the contents of that cell to a number.
9 June 2008, 6:03 am