Read INI File in VBA

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.

Option Explicit

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 msVersion As String
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 gclsApp As CApp

Public Const gsINIFILENAME As String = “C:Documents and SettingsdickMy DocumentsMyApp.ini”

Step 5:Create a standard module in your project called MOpenClose

Sub Auto_Open()
   
    ‘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.

Posted in Uncategorized

7 thoughts on “Read INI File in VBA

  1. 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

  2. Thanks for sharing this.

    How about a self-contained class? The below is lightly tested.

    In a class module named CApp:

    Option Explicit

    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:

    Option Explicit

    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

  3. 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

  4. 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.

  5. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *