Mcf

^ Because names do not lose their references between sessions, this is a great way of storing values as opposed to storing values in cells from which the information would have to be retrieved. For example,to track the leading producer between seasons,create a name "Leader." If the new season's leading producer matches the name reference,a special report comparing the seasons could be created.The other option is to create a special sheet to track the values between sessions and then retrieve the values when needed.With names,the values are readily available.

The following procedure shows how cells in a Variable sheet are used to retain information between sessions.

Sub NoNames(ByRef CurrentTop As String)

TopSeller = Worksheets("Variables").Range("A1").Value

If CurrentTop = TopSeller Then

MsgBox ("Top Producer is " & TopSeller & " again.")

Else

MsgBox ("New Top Producer is " & CurrentTop) End If End Sub

The following procedure shows how names are used to store information between sessions: Sub WithNames()

If Evaluate("Current") = Evaluate("Previous") Then

MsgBox ("Top Producer is " & Evaluate("Previous") & " again.")

Else

MsgBox ("New Top Producer is " & Evaluate("Current")) End If End Sub

If "Current" and "Previous" are previously declared names, you access them directly rather than create variables to pass them in. Note the use of the Evaluate method to extract the values in Names.

0 0

Post a comment