Storing Values in Names

The use of names to store data items has already been mentioned in Chapter 2, specifically under the Evaluate method topic. Now it's time to look at it in a bit more detail.

When you use a name to store numeric or string data, you should not precede the value of the RefersTo parameter with an equals sign (=). If you do, it will be taken as a formula. The following code stores a number and a string into StoreNumber and StoreString, respectively:

Dim v As Variant

v = 3.14159

Names.Add Name:='

'StoreNumber",

, RefersTo:=v

v = "Sales"

Names.Add Name:='

'StoreString",

, RefersTo:=v

This provides you with a convenient way to store the data you need in your VBA code from one Excel session to another, so that it does not disappear when you close Excel. When storing strings, you can store up to 255 characters.

You can retrieve the value in a name using the Evaluate method equivalent, as follows: v = [StoreNumber]

You can also store formulas into names. The formula must start with an equals sign (=). The following places the COUNTA function into a name:

Names.Add Name:="ItemsInA", RefersTo:="=COUNTA($A:$A)"

This name can be used in worksheet cell formulas to return a count of the number of items in column A, as shown in Figure 5-4.

(i Hei | In=f Pit | Fcr

P # - w *

C3

-Item s 1 ni

*

A

B

c

D

1

2

3

Dale

11!

4

Jan 01, 2007

£

Jan 03. 2007

6

Jan 15, 2007

7

Jan 22 2007

S

Jan 29 2007

a

Feb 05.2007

10

Feb 12,2007

11

Feb 19 2007

12

Feb 26 2007

13

Mar 05. 2007

15

«i-ii'^il Formula in IJanie

it:«1! mi

iferaM f i

= il

Figure 5-4

Once again, you can use the Evaluate method equivalent to evaluate the name in VBA: MsgBox [ItemsInA]

0 0

Post a comment