Nothing like a good old fashioned configuration file to make you feel like you’re programming in 1995. But you know what? Sometimes you just need to do it. And it still amazes me how many off-the-shelf applications still rely on INI files to store their settings (I’m looking at you, LotusNotes!).
Personally, I like INI files. I find them easy to work with and easy to deploy. And, of course, it doesn’t hurt that you can read and write to them using VBA.
If you Google reading or writing to INI files using VBA, you’ll get a bunch of different results, all pointing you in the same basic direction. Specifically, you need to use two different Windows APIs in order to do this “GetPrivateProfileString”, and “WritePrivateProfileString”. One reads from an INI file, the other writes to it. I’m going to assume you can figure out which is which.
Invariably, the examples you uncover on Google show that these routines are always separate. One routine fo reading, one for writing.
I hate that.
I like my code as concise as possible while still being as modular as possible. What do I mean by that? I mean that I want a routine (or function in this case) that I can call from another point in the code and choose whether I’m reading or writing to the configuration file. And I want the routine portable enough so I can drop it into any application I want and not have to worry about customizing it too terribly much.
And so, I give you the code below, commented for your pleasure:
'*******************************************************************************
' Declaration for Reading and Wrting to an INI file.
'*******************************************************************************
'++++++++++++++++++++++++++++++++++++++++++++++++++++
' API Functions for Reading and Writing to INI File
'++++++++++++++++++++++++++++++++++++++++++++++++++++
' Declare for reading INI files.
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
' Declare for writing INI files.
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
'++++++++++++++++++++++++++++++++++++++++++++++++++++
' Enumeration for sManageSectionEntry funtion
'++++++++++++++++++++++++++++++++++++++++++++++++++++
Enum iniAction
iniRead = 1
iniWrite = 2
End Enum
'*******************************************************************************
' End INI file declaratin Section.
'*******************************************************************************
Function sManageSectionEntry(inAction As iniAction, _
sSection As String, _
sKey As String, _
sIniFile As String, _
Optional sValue As String)As String
'*******************************************************************************
' Description: This reads an INI file section/key combination and
' returns the read value as a string.
'
' Author: Scott Lyerly
' Contact: scott.c.lyerly@gmail.com
'
' Notes: Requires "Private Declare Function GetPrivateProfileString" and
' "WritePrivateProfileString" to be added in the declarations
' at the top of the module.
'
' Name: Date: Init: Modification:
' sManageSectionEntry 26-Nov-2013 SCL Original development
'
' Arguments: inAction The action to take in teh funciton, reading or writing to
' to the INI file. Uses the enumeration iniAction in the
' declarations section.
' sSection The seciton of the INI file to search
' sKey The key of the INI from which to retrieve a value
' sIniFile The name and directory location of the INI file
' sValue The value to be written to the INI file (if writing - optional)
'
' Returns: string The return string is one of three things:
' 1) The value being sought from the INI file.
' 2) The value being written to the INI file (should match
' the sValue parameter).
' 3) The word "Error". This can be changed to whatever makes
' the most sense to the programmer using it.
'*******************************************************************************
On Error GoTo Err_ManageSectionEntry
' Variable declarations.
Dim sRetBuf As String
Dim iLenBuf As Integer
Dim sFileName As String
Dim sReturnValue As String
Dim lRetVal As Long
' Based on the inAction parameter, take action.
If inAction = iniRead Then ' If reading from the INI file.
' Set the return buffer to by 256 spaces. This should be enough to
' hold the value being returned from the INI file, but if not,
' increase the value.
sRetBuf = Space(256)
' Get the size of the return buffer.
iLenBuf = Len(sRetBuf)
' Read the INI Section/Key value into the return variable.
sReturnValue = GetPrivateProfileString(sSection, _
sKey, _
"", _
sRetBuf, _
iLenBuf, _
sIniFile)
' Trim the excess garbage that comes through with the variable.
sReturnValue = Trim(Left(sRetBuf, sReturnValue))
' If we get a value returned, pass it back as the argument.
' Else pass "False".
If Len(sReturnValue) > 0 Then
sManageSectionEntry = sReturnValue
Else
sManageSectionEntry = "Error"
End If
ElseIf inAction = iniWrite Then ' If writing to the INI file.
' Check to see if a value was passed in the sValue parameter.
If Len(sValue) = 0 Then
sManageSectionEntry = "Error"
Else
' Write to the INI file and capture the value returned
' in the API function.
lRetVal = WritePrivateProfileString(sSection, _
sKey, _
sValue, _
sIniFile)
' Check to see if we had an error wrting to the INI file.
If lRetVal = 0 Then sManageSectionEntry = "Error"
End If
End If
Exit_Clean:
Exit Function
Err_ManageSectionEntry:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Clean
End Function
Some thoughts on what’s going on here:
I’ve taken the two APIs that you would call to read and write to a configuration file and put them into a single function. I’m choosing which action to take based on the inbound inAction parameter.
That parameter, inAction, you may have noticed is an enumeration set up at the top of this module. I like the enumeration in this case because it lets me be very specific in my choice of action to take with regard to the INI file. Yes, I could have saved four lines of code by using a boolean instead. I could have set the function to “read” if TRUE and “write” if FALSE. Except I hate that option. I like enumerations for explicitly spelling out what my options are for value for a specific parameter. That way there’s no confusion.
So what does the implementation of this function look like? Glad you asked! I’ve included a sample routine below that you easily use to test this function out.
Sub SampleINIFunctionImplementaion()
Const sINI_FILE As String = "C:\Users\scott\Desktop\fruits & veggies.ini"
Dim sReturn As String
' Read the ini file
sReturn = sManageSectionEntry(iniRead, "Produce", "Fruit", sINI_FILE)
MsgBox sReturn
sReturn = sManageSectionEntry(iniRead, "Produce", "Vegetable", sINI_FILE)
MsgBox sReturn
' Write to the ini file
sReturn = sManageSectionEntry(iniWrite, "Produce", "Fruit", sINI_FILE, "banana")
sReturn = sManageSectionEntry(iniWrite, "Produce", "Vegetable", sINI_FILE, "squash")
End Sub
That’s about it. Feel free to copy and paste and use for your own Excel applications.
(Fine print: use at your own risk, blah blah blah…)
Scott C Lyerly 
Hi Scott,
User settings and other data are not always best saved directly to the workbook file. Your solution of saving to an INI file is great, especially for arrays and larger data volumes.
Since Excel 97, VBA has had an inbuilt function to save data directly to the registry. They are useful for storing data that is not complex or large. The choice is up to the developer whether to use an INI file or the registry, whichever would work best in a given situation.
John Walkenbach (http://www.j-walk.com/ss/excel/tips/tip60.htm) has a basic explanation of these functions “SaveSeting”, “GetSetting”, and “DeleteSetting”.
And the MS Excel Help pages have a reference for them at:
http://office.microsoft.com/client/helppreview.aspx?AssetId=HV805571839990&lcid=2057&NS=EXCEL.DEV&Version=12
Hi David-
It’s funny you say that, because I am using an INI file to save some data. Things like database path and database name. That way I can point users to any instance of the database I want/need.
The way I’m differentiating between data to store in the database versus data to store in an INI file is that anything that will require a list, such as data validation tables, will be database driven, while settings such as database directories or template version numbers will be INI file driven.
I love how easy Excel makes saving to the registry, but I have honestly yet to find a use for it. If I had a user form in the template workbook, I might save last selected values, or user form position, or something like that in the registery, but otherwise, no real use for it.
Hi Scott,
It sounds like we are both on the same wavelength regarding what information is best stored where. Other than user preferences or “picking up where I left off” I haven’t used the registry for storage. It is mostly stored in the workbook, support file, or the database.
I was surprised that you took the time to respond on Christmas Day. It is now 5:30 pm on 26 December here in Queensland, Australia. Enjoy the rest of the holiday period with your family and have a bountiful New Year.
Christmas Day ended up being the quietest day of the whole vacation! Thanks for the input, I appreciate it.
Many thanks ! Great piece of code saved me a lot of time!
Always a pleasure to write code that saves somebody time!
Hi, I assume that something in the API handles avoidance of duplicate settings when the code is run more than once with iniWrite. Is there some documentation on how this API function performs its steps? Thank you for the wrapper by the way, very helpful. For my part, I preferred to allow creation if an ini file flow from this function rather than elsewhere, thus many conditions which in your function result in a value of Error, for me require me to create a path, a file, etc… because I want to be able to call this function (only) on first need, since I do not have a lot of ini files to set up.
Thought I would mention also that with all these VBA solutions, where the “application” is not really a program and therefore does not have its own space in the same area that other programs register themself, it is challenging to have settings follow the workbook from local path to local path if they move it using windows explorer. That is for me a reason to consider the registry instead of an ini file, because possibly the user fails to keep the ini file in a path the workbook knows where to find it… On the other hand, the registry — if you rely on the same section and key of the VBA registry area — will always be whatever it is, unless the user or another application deletes or overwrites it.
I’m not immediately familiar with formal documentation about the API. You can try this link to see if it helps you, it was put together and maintained by one guy over the years and it’s the best resource I’ve seen: http://www.jasinskionline.com/windowsapi/ref/funca.html
As for the registry, if your Excel/VBA project is going to store settings on a local machine, then yes, absolutely, the registry is the way to go. But, if you are distributing a file across a network with multiple users using the file on multiple machines, then I’ve found the best way (outside of using a database) to store settings that you want that one file to use no matter who is using it on what machine, then a single INI file in a shared network location is a great method to use (IMHO).
Is there a reason you wouldn’t write a class that you instantiate with a .ini file then have methods that are a bit more parallel to other VBA objects like a Dictionary? I feel like that is the intuitive way of working with configuration files, particularly ini files since they’re so one-to-one.
Now if only we could have something like YAML in VBA!
As I wrote this comment, I found out about VBData (http://www.xtremevbtalk.com/showthread.php?t=310614). It looks interesting, particularly for tree-structured data.
Used the code and save works fine but read returns “Error”
I haven’t encountered an error in the “read” part, in fact that’s what I use the most. Can you post your code here and we can take a look at it?
Great idea and good job, I’ll use it in my Access project with mentioning the source (code courtesy …). Reading and Writing work both well. I have personalized the Function by vetoing the writing if section don’t exists in ini file (with a recursive call to this inimanager itself).
As in:
If sManageSectionEntry(iniRead, “Produce”, “Fruit”, sINI_FILE) “Error” Then
lRetVal = WritePrivateProfileString(sSection, sKey, sValue, sIniFile)
Else
lRetVal = 0
End If
‘ Check to see if we had an error wrting to the INI file.
If lRetVal = 0 Then sManageSectionEntry = “Error”
Sorry for my English, I’m italian, so Google Translate is my friend 🙂
Very useful, many thanks Scott! Used it with one change — create ini if it not yet exists and fill it with default values.
Been using this for awhile now, and have to say I love it (nearly a year now?). I updated the code to allow use on 64-bit computers. I also like to save tons of options through a userform, so wrote a module to save everything from a form into the .ini (or at least everything I’ve run into).
Placed the website in the “Leave a Reply” form. Feel free to share if you feel warranted.
Thanks again for this useful code!
You’re welcome!
I just used this for an Outlook VBA program. It works great! Also, I appreciate your documentation of the code. Much appreciated. Aloha from Oahu!
Thanks Scott. This helped !
With users moving to 64 bit machines now, May be you would want to add PtrSafe in your function decalare statements “Private Declare PtrSafe Function WritePrivateProfileString “. Users would get an error otherwise.
Cheers , Pramod
Very clear code and easy to use. Thanks a lot!
Hi Scott
first, thanks a lot !
are you still available to help me on this code ?
Best Regards
David (France)
Depends on the complexity of the question, but ask away!
I have put your exact code in a module1 of Outlook
with this kind of declaration (the same adapted for excel) :
#If VBA7 Then
Public Declare PtrSafe 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 Declare PtrSafe 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
#Else
Public 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 Declare Function GetPrivateProfileString Lib “kernel32” Alias _
“GetPrivateProfileStringA” (ByVal lpApplicationName As Any, ByVal lpKeyName As Any, _
ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
#End If
In outook, when I call the macro SampleINIFunctionImplementaion, I have this error (translated) :
“453: GetPrivateProfileStringA entry point for a DLL not found in Kernel32”
Hi David. I’ve done a little bit of digging but haven’t found much on ini files and Outlook. In truth, I’ve never used them in that way, and so haven’t had any experience trying to implement it in Outlook. Sorry I don’t have more info on you, but maybe one of the many VBA forums could guide you?
More explanation : it’s work’s perfectly in Excel… but not in Outoolk ?
Hi Scott,
Absolutely an amazing piece of code. Thank you for sharing
I’m currently using it to make changes to some scripts I use in Outlook for sending daily reports.
One thing I’ve been trying to do though is change the value of an entry to empty or vbNullString however I’m having trouble doing so.
I can set a value to nothing by putting a space but eventually the ini file just fills up with spaces.
I’m pretty much a novice when it comes to coding.
ManagersTo = sManageSectionEntry(iniWrite, "Managers", "To", sINI_FILE, Me.TextBox1.Value)
ManagersCc = sManageSectionEntry(iniWrite, "Managers", "Cc", sINI_FILE, Me.TextBox3.Value)
When the textbox value is nothing it doesn’t write the value to the ini file.
My method of implementing this is probably poor but like I said I’m pretty novice.
Any advice you or others can offer would be greatly appreciated.
When saving, you could save a special character or string for an empty field. When loading, you’d just reverse the logic. So save like @$BLANK$@ in the ini file, then when reading from the ini file, you can replace that notation with just a blank field.
I that was going to be my next idea.
I ended up finding another bit of code to handle writing to the ini file.
Option ExplicitPublic 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 Function Write_Ini_String(ByVal sIniPath As String, ByVal sSection As String, ByVal sEntry As String, ByVal sValue As String)
Dim lRetVal As Long
If sEntry = "" Then ' clear the section
lRetVal = WritePrivateProfileString(sSection, vbNullString, vbNullString, sIniPath)
Else
lRetVal = WritePrivateProfileString(sSection, sEntry, sValue, sIniPath)
End If
End Function
Call Write_Ini_String("full_path_and_name_of_ini_file", "Section_Title", "", "")https://www.tek-tips.com/viewthread.cfm?qid=44983
It’s messy but it works.