Read a config file to a Dictionary

Tested in Excel 365 (16.8730.2046) 64-bit

When you need to have certain parameters established without asking the user every time you run a macro, you can create a file containing all these parameters and read it at runtime. A good example is when you need to fill in the name and email of the person generating the report. Or when you call external apps and you need to instruct VBA to wait a certain amount of time, allowing user to modify the wait period.

Here is the code and below you’ll find a config file example.

Code:

 
 
  1. Public Const ConfigPathAndName = "C:\TEMP\ConfigSample.txt"
  2. Public Const ConfigDelimiter = ":"
  3. Public Const ConfigComment = "'"
  4. '
  5. Function GetConfigSettings() As Dictionary
  6.     ' needs reference to Microsoft Scripting Runtime
  7.    Dim f
  8.     Dim strFile, tempArray
  9.     Dim dSettings As New Dictionary
  10.    
  11.     f = FreeFile
  12.     strFile = ""
  13.        
  14.     Open ConfigPathAndName For Input As #f
  15.     ' parse the file
  16.    Do Until EOF(f)
  17.         Line Input #f, strFile
  18.         If Left(strFile, 1) <> ConfigComment Then
  19.             tempArray = Split(strFile, ConfigDelimiter)
  20.             ' add to dictionary
  21.            If Not dSettings.Exists(tempArray(1)) Then _
  22.                 dSettings.Add tempArray(0), Trim(tempArray(1))
  23.         End If
  24.     Loop
  25.    
  26.     Close #f
  27.    
  28.     Set GetConfigSettings = dSettings
  29.     ' discard the dictionary
  30.    Set dSettings = Nothing
  31.    
  32. End Function
  33. '
  34. Sub main()
  35.     Dim MySettings As New Dictionary
  36.     Set MySettings = GetConfigSettings
  37.    
  38.     For Each dkey In MySettings
  39.         Debug.Print dkey, MySettings.Item(dkey)
  40.     Next
  41.    
  42.     Set MySettings = Nothing
  43. End Sub

Note: Always discard a dictionary after use. They tend to slow down Excel if they stay in the memory.

More about dictionaries here.

Config:

Click to expand the code snippet
 
  1. ' This is a config file sample.
  2. ' I assigned the following logic:
  3. ' - every line starting with a ' is considered a comment (as in VBA, for convenience)
  4. ' - every non-comment line should represent a setting with this template: setting_name:setting_value
  5. ' - if you intend to use spaces after the delimiter, make sure you trim the setting_value in VBA
  6. ' - a blank line (eg. for readability purpose) should start too with the comment character like this:
  7. '
  8. ' You can define your own comment character and setting template and pass it to the VBA function
  9. ' ================================================================================================
  10. '
  11. ' --------------------- GENERAL SETTINGS ---------------------------------------------------------
  12. HeaderRow:25
  13. HeaderColumn: 7
  14. MaxNumberOfItems: 15
  15. Bold_Header:No
  16. Italic Header:Yes
  17. '
  18. ' Note the space in value for HeaderColumn and MaxNumberOfItems: " 7" and " 15" respectively
  19. ' or in setting name "Italic Header". Remember to use the setting name as is.
  20. '
  21. ' --------------------- RUNTIME SETTINGS ---------------------------------------------------------
  22. MaximumWaitTime:100
  23. '
  24. ' --------------------- OTHER SETTINGS -----------------------------------------------------------
  25. ReportDoneBy:Sanda Vladescu

 

Add a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.