Jeanette wrote to ask,
I need a counter in my procedure that keeps its value even after I exit the application, so next time when I open the program, the value will still be the same.
I tried this, but it didn't work.
Static Sub Counter()
counter=counter + 1
End Sub
When I close Excel and reopen it, Counter is back to zero. ... How can I solve this?
-------------------
I'm glad you mentioned "Excel" near the end of your question, Jeanette. If you were just working in VB.NET by itself, you would probably have to store the value in a file between executions. That's what configuration files, used in virtually all significant systems, really are. They're necessary because, in general, there isn't a way to save a variable value without using a datastore of some kind.
Since you're using an Office application, however, the VBA program you're using isn't being saved as an executable, it's actually part of the document (or "workbook" in the case of Excel, but that's still a type of document). Because the a document is being saved anyway, there is a way of doing this with Excel, and other Office applications too. (Of course, you can always just add the information to the actual document. In Excel, it's popular to put it in a cell on an additional worksheet.
Office documents have "properties" that you can use in your VBA programs. Usually, people only use the "Builtin" properties such as the document title or the last modified date. But you can add your own custom properties too. In Excel 2010, it looks like this:
--------
Click Here to display the illustration
--------
You can handle the whole thing in code as well. In this example, I've added a Button control to an Excel worksheet. This code updates a counter whenever the button is clicked.
Sub Button1_Click()
Dim theCounter As DocumentProperty
On Error Resume Next
Set theCounter = _
ThisWorkbook.CustomDocumentProperties("Counter")
' If Counter does not exist,
' then it must be added to CustomDocumentProperties
' Simply adding it manually is
' an alternative to code here
If Err.Number > 0 Then
ThisWorkbook.CustomDocumentProperties.Add _
Name:="Counter", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=1
Else
theCounter = theCounter + 1
ThisWorkbook.CustomDocumentProperties("Counter") _
= theCounter
End If
End Sub
VB.Net creates assemblies, not documents. In VB.NET, metadata takes the place of properties in an Office document. An assembly is a combination of metadata and the executable code. The Attribute class will allow you add metadata to your assemblies but it's hard to make this work for individual data items like this. So .NET has provided an alternative way: Settings.
Like everything else in .NET, Settings is a whole system, not just a way of saving one value. You can create Settings by Application (for example, a database connection string) or by User. You can save them for individual controls. The first illustration shows how to do it using the project properties. The second illustration shows a setting for the Text property of a Label control.
--------
Project Properties
Label Control Text Property
--------
To do what Jeanette wants to do, the scope has to be set to User. Application level scope makes the setting read only to program code. (It has to be changed at design time.) Here's the VB.NET code that will do it:
My.Settings.Item("Counter") += 1
Label2.Text = My.Settings.Item("Counter")
An interesting fact, however, is that settings are still saved as a file, not as part of the project. .NET just makes the process automatic. Here's the file that settings are saved in:
--------
Click Here to display the illustration
--------
And, another limitation seems to be that there still seems to be no way of saving a value for both all users and updating it under program control at the same time. Application scope won't let you update it. User scope won't update it for all users. (The setting is persisted for each user independently.) So if you want to do that, you still need to create a file.
(Thanks to: Timothy, Teresa, Vince, and Ron for reminding me that there IS a way to persist values in VB.NET.)