The “preferred” method for storing application parameters is the registry. But I’m a maverick. I still use ini files. What I didn’t know was that there are Windows API functions for reading and writing to an ini file. Oh, the time I could have saved.
This an example of reading and writing to an ini file for a custom Excel application. In the app, there is a class module called CApp. CApp stores all my application-wide settings and any application events. In this case, it will store the version and revision that are being kept in MyApp.ini.
Step 1:Make a text file that looks like this:
Step 2:Create a standard module in your project call MAPI to house your API declarations.
Declare Function GetPrivateProfileString Lib “kernel32” Alias “GetPrivateProfileStringA” _
(ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
Declare Function WritePrivateProfileString Lib “kernel32” Alias “WritePrivateProfileStringA” _
(ByVal lpApplicationName As String, ByVal lpKeyName As Any, _
ByVal lpString As Any, ByVal lpFileName As String) As Long
Step 3:Create a class module in your project called CApp
Private msRevision As String
Private Const msSEC As String = “VersionInfo”
Private Const msVER As String = “version”
Private Const msREV As String = “revision”
Public Property Get Revision() As String: Revision = msRevision: End Property
Public Property Let Revision(ByVal sRevision As String): msRevision = sRevision: End Property
Public Property Get Version() As String: Version = msVersion: End Property
Public Property Let Version(ByVal sVersion As String): msVersion = sVersion: End Property
Private Sub Class_Initialize()
Dim sReturn As String * 255
Dim lLen As Long
lLen = GetPrivateProfileString(msSEC, msVER, “”, sReturn, 255, gsINIFILENAME)
Me.Version = Left(sReturn, lLen)
lLen = GetPrivateProfileString(msSEC, msREV, “”, sReturn, 255, gsINIFILENAME)
Me.Revision = Left$(sReturn, lLen)
End Sub
Private Sub Class_Terminate()
WritePrivateProfileString msSEC, msVER, Me.Version, gsINIFILENAME
WritePrivateProfileString msSEC, msREV, Me.Revision, gsINIFILENAME
End Sub
Step 4:Create a standard module in your project called MGlobals
Public Const gsINIFILENAME As String = “C:Documents and SettingsdickMy DocumentsMyApp.ini”
Step 5:Create a standard module in your project called MOpenClose
‘Read ini file
Set gclsApp = New CApp
Debug.Print gclsApp.Version, gclsApp.Revision
‘change revision
gclsApp.Revision = “1B”
‘call terminate to write ini file
Set gclsApp = Nothing
‘reread ini file
Set gclsApp = New CApp
Debug.Print gclsApp.Version, gclsApp.Revision
End Sub
When CApp is instantiated, it reads the ini file and set its own properties. When it’s terminated, it writes the properties back to the ini file. These two properties would probably best be read-only, but that wouldn’t make for a very interesting example. Also, the reading and writing should be methods of the class outside of the events. There are definitely times when you’d want to write changes back to the file. If the app were to crash, you wouldn’t want to lose all the settings changes.
I always used to start with Karl Peterson’s kpIni library (http://vb.mvps.org/samples/kpIni/)
Dick,
I wouldn’t claim nowadays that reading/writing to Windows Registry is the “preferred” approach for storing data. Both Windows Vista/7 have an improved security model that, when activated, prevent end-users from system/program folders and the Registry.
From my point of view; as long as we use some kind of text files for settings etc it’s the “preferred” method. It’s also well supported on the .NET platform :-)
Kind regards,
Dennis
Thanks for sharing this.
How about a self-contained class? The below is lightly tested.
In a class module named CApp:
Private msVersion As String, msRevision As String, xINIFilename As String
Private Const msSEC As String = “VersionInfo”, _
msVER As String = “version”, _
msREV As String = “revision”
Private Declare Function GetPrivateProfileString _
Lib “kernel32” Alias “GetPrivateProfileStringA” ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, ByVal lpFileName As String) As Long
Private Declare Function WritePrivateProfileString _
Lib “kernel32” Alias “WritePrivateProfileStringA” ( _
ByVal lpApplicationName As String, ByVal lpKeyName As Any, _
ByVal lpString As Any, ByVal lpFileName As String) As Long
Public Property Get Revision() As String
If xINIFilename <> “” Then Revision = msRevision
‘Should return an error if INIFilename is uninitialized
End Property
Public Property Let Revision(ByVal sRevision As String)
If xINIFilename <> “” Then msRevision = sRevision
End Property
Public Property Get Version() As String
If xINIFilename <> “” Then Version = msVersion
End Property
Public Property Let Version(ByVal sVersion As String)
If xINIFilename <> “” Then msVersion = sVersion
End Property
Public Property Get INIFilename() As String
INIFilename = xINIFilename
End Property
Public Property Let INIFilename(ByVal uINIFilename As String)
If xINIFilename = “” Then xINIFilename = uINIFilename
‘Else return error?
Dim sReturn As String * 255
Dim lLen As Long
lLen = GetPrivateProfileString(msSEC, msVER, “”, sReturn, 255, xINIFilename)
Me.Version = Left(sReturn, lLen)
lLen = GetPrivateProfileString(msSEC, msREV, “”, sReturn, 255, xINIFilename)
Me.Revision = Left$(sReturn, lLen)
End Property
Private Sub Class_Terminate()
If xINIFilename <> “” Then
WritePrivateProfileString msSEC, msVER, Me.Version, xINIFilename
WritePrivateProfileString msSEC, msREV, Me.Revision, xINIFilename
End If
End Sub
Now, to use the above, in a standard module:
Public gclsApp As CApp
Const gsINIFILENAME As String = _
“C:TusharTempMyApp.ini”
‘”C:Documents and SettingsdickMy DocumentsMyApp.ini”
Sub Auto_Open()
‘Read ini file
Set gclsApp = New CApp: gclsApp.INIFilename = gsINIFILENAME
‘Ideally, overload Class_Initialize, as in _
Set gclsApp = New CApp (gsINIFILENAME)
Debug.Print gclsApp.Version, gclsApp.Revision
‘change revision
gclsApp.Revision = “1B”
‘call terminate to write ini file
Set gclsApp = Nothing
‘Might it not be better to use a more explicit writeINIfile method?
‘reread ini file
Set gclsApp = New CApp: gclsApp.INIFilename = gsINIFILENAME
Debug.Print gclsApp.Version, gclsApp.Revision
End Sub
Dick, I blogged about a year ago on an app that I built using INI files to store the data, http://msmvps.com/blogs/xldynamic/archive/2010/03/03/more-sp-autogen.aspx.
XL-Dennis suggested using XML files, and I did a follow-up post using XML http://msmvps.com/blogs/xldynamic/archive/2010/03/11/the-xml-is-on-the-kid.aspx
Hi Dick,
Thanks for sharing. From .Net I’m used to creating xml configuration files, with nested levels of configuration. Then write a configuration class to read and parse the xml file. That should be possible in VBA I know for sure, but I haven’t yet found it neccesary to create a configuration file for any project in Excel VBA.
There can be different reasons for that:
1) I haven’t yet done projects of size and complexity to justify a configuration file.
2) I’ve been too lazy, since VBA do not produce assembly libraries which are hard to patch/partly upgrade to new version, so all configuration is simply hard coded into class or standard modules, or stored in hidden worksheets.
I was wondering. What are the pros/cons for configuration files in VBA projects? And second, do you think xml configuration parsing is overkill with VBA projects? (can’t seem to find any references on the net, only references to INI-file reading and hidden worksheets)
Regards
Thomas Ellebæk
The pro is that you have user-specific data that doesn’t follow the code around. The con is that it’s harder to code reading a config file than just hardcoding stuff. I see no problem with xml files for config files. I use xml on my sample data add-in.
Sure, so far I haven’t created any projects for multiple “different” users. That would justify a config file! Just occured to me that INI-files was discussed several places (including this site), like some kind a best practice. Thanks for your reply Dick.