Leading the way in Microsoft Office Development
 Home|

Excel

|Word|PowerPoint|VBA|SharePoint|Consultancy|Newsletter|Contact 
 Excel > Named Ranges > VBA Code > Constants and Formulas< Previous | Next > 
 

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

 
 
1
2
3
Dim lnumber As Long
lnumber = 200
Activeworkbook.Names.Add Name:="RandomNo", RefersTo=lnumber
   


 

Worksheet Constant

 
 
4
5
6
Dim lnumber As Long
lnumber = 200
Activesheet.Names.Add Name:="RandomNo", RefersTo=lnumber
   

 

You can retrieve this value by using the Evaluate method:

 
 
7
lnumber = [RandomNo]
   


 

Workbook Formulas

 
 
8
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

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

 

 

Things to Remember

 
 

  • You text strings cannot exceed more than 255 characters.

    		 

     Copyright © 2011 Better Solutions Limited. All Rights Reserved.< Previous | Top | Next >