Configuring your constants from global ini file

About one year ago I was sitting at my desk and staring out from the window and feeling not so great. I have faced the situation, where I had a lot of different Excel workbooks used in several daily processes with a lot of different constants (filePath names, passwords and other program configuration settings). What actually made this situation especially frustrating was the fact, that the same constants were used in several different workbooks. So, what’s the problem? The problem is all those inevitable changes for these constants what you have to update frequently. Even you would be as careful as you ever can, you will inevitably forget to update some Excel workbook, which then ends up using some wrong configuration parameters. After that, something is going to explode somewhere - near your desk.

Needless to say, opening all Excel workbooks, finding constants and replacing them with the new ones is not very productive activity. After the usual google gymnastics, the solution candidate was found from the deepness of Windows API - GetPrivateProfileString function, which retrieves a string from the specified section in an initialization file. The GetPrivateProfileString function searches the specified initialization file for a key that matches the name specified by the key under the section heading specified by header parameter. If it finds the key, the function copies the corresponding string to the buffer. You can find out some information from here: http://msdn.microsoft.com/en-us/library/windows/desktop/ms724353(v=vs.85).aspx

So, what can you do with this API function? Let us assume for the sake of simplification, that we have only one Excel workbook. Within that workbook, we have two important variables (passwords) what we need to have for the program and what might change in the future.

First, we need to create one global initialization file. This is technically just a text file, which will be saved as .ini file into some specific folder: (C:\Temp\ GlobalConfigurations.ini).

After this, we need to add section headers and keys. Just simply copy-paste the strings below into your text file:

[PASSWORD_1]

password=DedicatedUs3rForODBC

[PASSWORD_2]

password=OCRobot054rForODBC

Now, save that text file into your own specific folder. Use file name “GlobalConfigurations.ini”. Initialization file is now ready for the use.

Next, we need to create the program. Add new VBA Class Module and name it to be WAPIUtility. Then, copy-paste the code below tag WAPIUTILITY into this newly created class module. Since this WAPIUtility class is using Microsoft Scripting Runtime library, we need to declare it: At VB editor (CTRL+ALT11) -- Tools -- References -- Microsoft Scripting Runtime (should be located at: C:\Windows\system32\scrrun.dll). After this, add new VBA Standard Module and copy-paste the code below tag MAINPROGRAM into this newly created standard module. NOTE: Within the main program, do not forget to change the constant CONST_GLOBAL_INI_FILE to correspond the filePathName of your global initialization file. After this, our tester program should print out the following two strings:

DedicatedUs3rForODBC

OCRobot054rForODBC

And that’s it in a nutshell. Now you can replace all potentially changing and dangerous constants in your VBA programs and replace these with ordinary variables, into which the values are going to be read from your new centralized initialization file. Now, whenever there’s any changes to be made, you only need to modify one text file, instead of opening tens of different Excel workbooks and going through all modules in your big hunt for constants.

One more thing about the content of this ini-file. Nothing prevents you to put more keys under the same header. For example, you could do this:

[BLOOMBERG_PORTFOLIO_AC]
Profile=\@PROFILE.DTL
Header_System=SYSTEM=PORTFOLIO
Header_UserNumber=USERNUMBER=
Header_LoginName=LOGIN=
Header_TransferFilePath=DIR=C:\\temp\
Header_PortfolioID=PORTFOLIO_ID=3
Header_Action=ACTION=REPLACE
Header_PortfolioName=PORTFOLIO_NAME=AC TOTAL PORTFOLIO
Header_PortfolioCurrency=PORTFOLIO_CURRENCY=EUR
Header_PortfolioCash=PORTFOLIO_CASH=
Header_PortfolioFilingDate=FILING_DATE=
Header_AssetClass=ASSET_CLASS=BALANCED
Header_Delimiter=DELIMITER=,
Header_Fields=FIELDS=ID_TYPE ID YELLOW_KEY SIZE USER_PX COST
Header_StartOfData=START_OF_DATA
Header_EndOfData=END_OF_DATA
Header_EndOfFile=END_OF_FILE
Header_IDType=8
Header_YellowKey=CORP
DateStringSeparator=/

In this case, if you would like to print out string value from the key "Header_Fields", you should use the following syntax:

Debug.Print winUtils.getGlobalInitializationParameter("BLOOMBERG_PORTFOLIO_AC", "Header_Fields", CONST_GLOBAL_INI_FILE)

API function returns the following string:
FIELDS=ID_TYPE ID YELLOW_KEY SIZE USER_PX COST

The program example below might look a bit strange, but just copy-paste it in your VBA. I have tested it and it should be working correctly. Well, I hope that you have found at least some ideas for your own needs. Have a great day.

-Mike

'TAG : WAPIUTILITY
Option Explicit
'
Private Const CONST_FILE_ERROR_NUMBER As Long = VBA.vbObjectError + 1
Private Const CONST_FILE_ERROR_SOURCE As String = "Global file error"
Private Const CONST_FILE_ERROR_DESCRIPTION As String = "Source file cannot be found."
'
Private Const CONST_PARAMETER_ERROR_NUMBER As Long = VBA.vbObjectError + 2
Private Const CONST_PARAMETER_ERROR_SOURCE As String = "Global parameter error"
Private Const CONST_PARAMETER_ERROR_DESCRIPTION As String = "Section header or value cannot be found."
'
Private Const CONST_BUFFER_SIZE As Long = 2000
Private fileSystem As Scripting.FileSystemObject
'
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
'
Public Function FileExists(ByVal fileName As String) As Boolean
    '
    Set fileSystem = New Scripting.FileSystemObject
    FileExists = fileSystem.FileExists(fileName)
End Function
'
Public Function getGlobalInitializationParameter(ByVal sectionTag As String, _
ByVal sectionKey As String, ByVal filePathName As String) As Variant
    '
    ' exit immediately if source file is not found
    If Not (FileExists(filePathName)) Then VBA.Err.Raise CONST_FILE_ERROR_NUMBER, CONST_FILE_ERROR_SOURCE, CONST_FILE_ERROR_DESCRIPTION
    '
    getGlobalInitializationParameter = ReadINI(sectionTag, sectionKey, filePathName)
    '
    ' exit if initialization parameter is empty: there is an error with file section or section key input
    If (getGlobalInitializationParameter = VBA.CStr(0)) Then VBA.Err.Raise CONST_PARAMETER_ERROR_NUMBER, CONST_PARAMETER_ERROR_SOURCE, CONST_PARAMETER_ERROR_DESCRIPTION
End Function
'
Private Function ReadINI(ByVal astrSection As String, ByVal astrKey As String, ByVal astrFilePath As String) As String
    '
    Dim lstrBuffer As String: lstrBuffer = String(CONST_BUFFER_SIZE, vbNull)
    Dim lstrLong As String: lstrLong = GetPrivateProfileString(astrSection, astrKey, Chr(1), lstrBuffer, CONST_BUFFER_SIZE, astrFilePath)
    Dim lstrValue As String
    '
    If VBA.Left(lstrBuffer, 1) <> Chr(1) Then
        lstrValue = VBA.Left(lstrBuffer, lstrLong)
        If (VBA.Len(lstrValue) > 0) Then ReadINI = lstrValue
    End If
    If (VBA.Len(lstrValue) = 0) Then ReadINI = VBA.CStr(0)
End Function


'TAG : MAINPROGRAM
'
Const CONST_GLOBAL_INI_FILE As String = "C:\Users\mka\Desktop\GlobalConfigurations.ini"
'
Option Explicit
'
Public Sub tester()
    '
    Dim winUtils As New WAPIUtility
    Debug.Print winUtils.getGlobalInitializationParameter("PASSWORD_1", "password", CONST_GLOBAL_INI_FILE)
    Debug.Print winUtils.getGlobalInitializationParameter("PASSWORD_2", "password", CONST_GLOBAL_INI_FILE)
    Set winUtils = Nothing
End Sub