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.


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext