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.

__________________
Keep Excelling.

Damon

VBAexpert Excel Consulting
(My other lives: http://members.tripod.com/playitagaindamon ,
http://community.webshots.com/user/piadamon )

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