|
|
|
|
|
|
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()
Const szVersion As String = "WorkbookVersion"
Const szEqual As String = "="
Dim nmVersionName As Name
Dim szReferVal As String
On Error Resume Next
Set nmVersionName = ThisWorkbook.Names(szVersion)
If Err.Number > 0 Then
Names.Add szVersion, 1
Else
szReferVal = Replace(Names(szVersion).RefersTo, szEqual, Empty)
Names(szVersion).RefersTo = szEqual & CLng(szReferVal) + 1
End If
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.
|
|