-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
is there a way to store a value in a VBA permanently
- Thread starter jkpd2000
- Start date
Damon Ostrander
MrExcel MVP
- Joined
- Feb 17, 2002
- Messages
- 4,239
Hello jkpd2000,
There is no way I know of to store data in a VBAProject. However, you can store data in a workbook in an Excel Name object, and you can also store data in a CustomDocumentProperty object. Say the value you want to store is a version number, like 7. To store the data in a Name object:
Names.Add Name:="VersionNumber", RefersTo:=7
and to change it:
Names("VersionNumber").Value = 8
This has the advantage that the name can be referred to in a cell formula (i.e., =VersionNumber will yield 8 in the cell)
Similarly, to create a new CustomDocumentProperty:
ThisWorkbook.CustomDocumentProperties.Add _
Name:="Version Number", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=7
and to change it:
ThisWorkbook.CustomDocumentProperties("Version Number").Value = 12
This has the advantage of being viewable outside of Excel by the File Explorer and searchable outside of Excel using the Advanced Find.
There is no way I know of to store data in a VBAProject. However, you can store data in a workbook in an Excel Name object, and you can also store data in a CustomDocumentProperty object. Say the value you want to store is a version number, like 7. To store the data in a Name object:
Names.Add Name:="VersionNumber", RefersTo:=7
and to change it:
Names("VersionNumber").Value = 8
This has the advantage that the name can be referred to in a cell formula (i.e., =VersionNumber will yield 8 in the cell)
Similarly, to create a new CustomDocumentProperty:
ThisWorkbook.CustomDocumentProperties.Add _
Name:="Version Number", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=7
and to change it:
ThisWorkbook.CustomDocumentProperties("Version Number").Value = 12
This has the advantage of being viewable outside of Excel by the File Explorer and searchable outside of Excel using the Advanced Find.
Upvote
0
zacemmel
Well-known Member
- Joined
- Apr 29, 2002
- Messages
- 636
Another way you can do this is use the wonderful thing in Windows called the registry 
Of course it might be a pain to write registry values just for one variable, but it can come in handy sometimes. There are just a few API calls you have to make in order to do this. Simple, really.
This message was edited by zacemmel on 2002-05-20 14:16
Of course it might be a pain to write registry values just for one variable, but it can come in handy sometimes. There are just a few API calls you have to make in order to do this. Simple, really.This message was edited by zacemmel on 2002-05-20 14:16
Upvote
0
stevebausch
Well-known Member
- Joined
- May 11, 2002
- Messages
- 810
SaveSetting, GetSetting
You don't need no stinkin API calls
You don't need no stinkin API calls
Upvote
0
Damon Ostrander
MrExcel MVP
- Joined
- Feb 17, 2002
- Messages
- 4,239
Hi again jkpd2000,
The value is actually stored internally to the workbook, but is nevertheless accessible without having to open the workbook. For example, if you right-click on the workbook file in the file explorer and select Properties, then go to the Custom tab, you will see the new Version Number custom property displayed there.
There used to be an Office Find File utility that would enable one to search for files with certain document property values, but it is no longer provided with Office as a separate utility. This capability has been built into the Office application (including Word, PowerPoint, Access, etc.) Open menu advanced search capability, but will not let you search for Custom property values, only standard built-in DocumentProperty values.
I believe that there is a Windows API routine that also allows you to access the document properties, including custom document properties, without opening the file. One advantage with this is that this would enable certain properties of a file to be accessed even if the file contains macros without having to enable macros because the file is never opened by the application, and this should also make the search very fast.
Damon
The value is actually stored internally to the workbook, but is nevertheless accessible without having to open the workbook. For example, if you right-click on the workbook file in the file explorer and select Properties, then go to the Custom tab, you will see the new Version Number custom property displayed there.
There used to be an Office Find File utility that would enable one to search for files with certain document property values, but it is no longer provided with Office as a separate utility. This capability has been built into the Office application (including Word, PowerPoint, Access, etc.) Open menu advanced search capability, but will not let you search for Custom property values, only standard built-in DocumentProperty values.
I believe that there is a Windows API routine that also allows you to access the document properties, including custom document properties, without opening the file. One advantage with this is that this would enable certain properties of a file to be accessed even if the file contains macros without having to enable macros because the file is never opened by the application, and this should also make the search very fast.
Damon
Upvote
0
My spreadsheet has multiple sheets and keyboard shortcuts to navigate among them, and I'm using the above trick to store the name of the sheet the user just navigated from. So if for example I set "ReturntoSheet" = activesheet.name prior to switching, I can go back by retrieving the name. BUT: because of how the name is saved, worksheets(Names("ReturntoSheet").Value).select does not work. This instead works: Worksheets(Mid(Names("ReturnPriorSheet").Value, 3, Len(Names("ReturnPriorSheet").Value) - 3)).Select
Upvote
0
