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.
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.
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
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?
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.
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.
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.
Oops, sorry about the double post…
“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
Evaluate(“Nm” & i)
works too…
“If you Evaluate the Value property of the Name object, no error.”
I think I have my answer! Thanks, Dick.
Jamie.
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.
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
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?
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.
After convertig the cell to a numeric format, you still may need to convert the contents of that cell to a number.
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
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