Constants and Formulas

How to detect a formula or a constant ?

Does the refers to contain an exclamation mark (!) if it does then it must refer to a cell range.
When you use a named range to store a numeric or string value you should not prefix the RefersTo parameter with an equal sign (=).
If you do, it is assumed to be a formula and not a constant.

Workbook Constant

Dim lnumber As Long 
lnumber = 200 
ActiveWorkbook.Names.Add Name:="RandomNo", _ 
                         RefersTo=lnumber 

Worksheet Constant

Dim lnumber As Long 
lnumber = 200 
ActiveSheet.Names.Add Name:="RandomNo", _ 
                      RefersTo=lnumber 

You can retrieve this value by using the Evaluate method:

lnumber = [RandomNo] 

Workbook Formulas

Application.Names.Add Name = "Formula", _ 
                      RefersTo:="=COUNT($D:$D)" 

This named range can then be used on any worksheet to return the total number of items in column D

Worksheet Formulas

Worksheets("Sheet1").Names.Add Name = "Formula", _ 
                     RefersTo:="=COUNT($E:$E)" 

This named range can only be used on the Sheet1 worksheet to return the total number of items in column E.

Important

You text strings cannot exceed more than 255 characters.