is there a way to store a value in a VBA permanently

[jkpd2000]

I want to store a value in my VBA script in some place say a variable. i will be incrementing that and storing the value in the next run. is it possible?

 

[Damon Ostrander]

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.

 

[zacemmel]

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

 

[stevebausch]

SaveSetting, GetSetting



You don't need no stinkin API calls

 

[zacemmel]

If you want them in custom folders in the registry, yes, you do need API calls.

 

[jkpd2000]

hi damon,

i used the 2nd version of your code. how to find it outside of excel. will it be a saved as a file ? if so can you tell me how to find

 

[Damon Ostrander]

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

 

[weav8060]

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