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:
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:
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:
Header_PortfolioName=PORTFOLIO_NAME=AC TOTAL PORTFOLIO
Header_Fields=FIELDS=ID_TYPE ID YELLOW_KEY SIZE USER_PX COST
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