VBA Express : Excel - Store Values in a Defined Name

Excel

Store Values in a Defined Name

Ease of Use

Easy

Version tested with

2000, 2002

Submitted by:

Justinlabenne

Description:

Stores a value in a defined name

Discussion:

Alot of times it is useful to store a value for later use in your Excel workbook or application. Instead of storing these values in cells on a sheet, you can use defined names to store them. The example code creates the defined name with a starting value of 1 if it does not exist. If it does, the value is incremented by 1 every time the code is called. This example is based on Worbook version numbering, but defined names can be used to store practically any value.

Code:

instructions for use

			Option Explicit 
 
Sub SaveValueInDefinedName() 
     '   ============================================
     '   Save a value in a Defined Name
     '   ============================================
     '   Constant string for the defined name we are adding
    Const szVersion As String = "WorkbookVersion" 
     
     '   Constant string for the equal sign
    Const szEqual As String = "=" 
     
     
    Dim nmVersionName As Name 
    Dim szReferVal As String 
     
     '   ========================================================================
     '   If the name doesn't exist, we create it and set the initial value to 1
    On Error Resume Next 
    Set nmVersionName = ThisWorkbook.Names(szVersion) 
     
    If Err.Number > 0 Then 
         
        Names.Add szVersion, 1 
         '   ========================================================================
         
    Else 
         
         '   ========================================================================
         '       if our name exists, we need to increment the value in it by 1
         '       to do this, we parse the name's RefersTo value:
        szReferVal = Replace(Names(szVersion).RefersTo, szEqual, Empty) 
         
         
         '       Reset the name to refer to our new value
        Names(szVersion).RefersTo = szEqual & CLng(szReferVal) + 1 
         '   ========================================================================
         
    End If 
     
     '   Explicitly clear memory
    Set nmVersionName = Nothing 
End Sub

How to use:

Open an Excel Workbook
Copy the code
Press Alt + F11 to open the Visual Basic Editor (VBE)
Select INSERT > MODULE from the menubar
Paste code into the right pane
Press Alt+Q to return to Excel
Save workbook before any other changes

Test the code:

Go to TOOLS > MACRO > MACROS
When the dialog appears, select {PrintEmbeddedChartsOnEachSheet}
Press Run

Sample File:

SaveValueInDefinedName.zip 9.4KB

Approved by mdmackillop

This entry has been viewed 294 times.