Thursday, May 23, 2013

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.DTLHeader_System=SYSTEM=PORTFOLIOHeader_UserNumber=USERNUMBER=Header_LoginName=LOGIN=Header_TransferFilePath=DIR=C:\\temp\Header_PortfolioID=PORTFOLIO_ID=3Header_Action=ACTION=REPLACEHeader_PortfolioName=PORTFOLIO_NAME=AC TOTAL PORTFOLIOHeader_PortfolioCurrency=PORTFOLIO_CURRENCY=EURHeader_PortfolioCash=PORTFOLIO_CASH=Header_PortfolioFilingDate=FILING_DATE=Header_AssetClass=ASSET_CLASS=BALANCEDHeader_Delimiter=DELIMITER=,Header_Fields=FIELDS=ID_TYPE ID YELLOW_KEY SIZE USER_PX COSTHeader_StartOfData=START_OF_DATAHeader_EndOfData=END_OF_DATAHeader_EndOfFile=END_OF_FILEHeader_IDType=8Header_YellowKey=CORPDateStringSeparator=/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.

The programs, which are presented in this blog, can be freely used, but without warranty or support of any kind. By using the programs presented in this blog, you accept to bear the entire risk, concerning quality or performance of any programs used. In no event, will I be liable to you for the damages, including any general, special, incidental or consequential damages arising out of the use or inability to use the programs presented in this blog. By using the programs presented in this blog, you are accepting the content of this disclaimer.