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%.

NameCon1

I can use this named constant in formulas. In the example below, I extend line amounts adding the sales tax.

NameCon2

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.

NameCon3

If you don’t know what sales tax is, you probably pay VAT. I think they’re roughly the same.

17 thoughts on “Named Constants

  1. 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.

    –

  2. John Walkenbach has this in Power Programming, p. 306-7. Will this move in the right direction?

    Function CellType(Rng)
    ‘ 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

  3. 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?

  4. 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.

    –

  5. 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.

  6. 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.

  7. “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

  8. “If you Evaluate the Value property of the Name object, no error.”

    I think I have my answer! Thanks, Dick.

    Jamie.

    –

  9. 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.

  10. 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

  11. 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?

  12. Paul
    It 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.

  13. I wanted to add some information about just the 2015 Error in junction with Application.Evaluate().

    I have a similar error when trying to parse a custom function. This function receives a string parameter and uses it to retrieve, from within a sheet, another string (double quotes not consistent):

    ? Application.Evaluate(“GetExplanationForQuery(“My Query”)”)

    After a lot of testing I have come to the conclusion that the Evaluate() fails with Error 2015 if the length of the returned string is above a determined size, which I estimate being 255 characters (the limit of an Byte data type).

    In fact, each time the function returns a long string, I get Error 2015.

    I have also tried using the “[…]” syntax instead of “Application.Evaluate()” without success:

    ? [GetExplanationForQuery(“My Query”)]

    As a workaround, I now test for “VBA.VarType(vResult) = vbError” and take the entire cell value if true:

    vResult = Application.Evaluate(sFormulaString)
    If (VBA.VarType(vResult) = vbError) Then vResult = rCell.Value
    rCell.Value = vResult

    Continuing to work on it.

    Dutch

  14. Does anyone know how to code a certain column of data under a certain variable, into another column with another variable heading similar to recoding in SPSS. I have a column which is all letters (i.e. L, L , L , E, E, E, ) and I want to make a function that says if the letter in this column is L then it is now 1.

    any help would be great


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.