Visual Basic for Applications:
Working with TOML

How to:

Working with TOML in VBA involves parsing the TOML file to read configurations or settings into your VBA project. VBA does not have built-in support for TOML, so you’ll typically use a parser or convert TOML data into a format VBA can easily work with, like JSON or XML. Here’s how to manually parse a simple TOML config file:

1. Sample TOML File (config.toml):

title = "TOML Example"

[database]
server = "192.168.1.1"
ports = [ 8000, 8001, 8002 ]
connection_max = 5000
enabled = true

2. VBA Code to Parse TOML:

Assuming the TOML content is read into a string variable tomlStr, the following VBA code demonstrates a simplistic approach to parse the [database] section:

Function ParseTOML(tomlStr As String)
    Dim lines() As String
    lines = Split(tomlStr, vbCrLf)
    
    Dim config As Object
    Set config = CreateObject("Scripting.Dictionary")
    Dim currentSection As String
    currentSection = ""
    
    Dim i As Integer
    For i = 0 To UBound(lines)
        Dim line As String
        line = Trim(lines(i))
        If InStr(line, "[") > 0 And InStr(line, "]") > 0 Then
            currentSection = Mid(line, 2, Len(line) - 2)
            Set config(currentSection) = CreateObject("Scripting.Dictionary")
        ElseIf InStr(line, "=") > 0 Then
            Dim parts() As String
            parts = Split(line, "=")
            Dim key As String
            key = Trim(parts(0))
            Dim value As String
            value = Trim(parts(1))
            config(currentSection)(key) = value
        End If
    Next i
    
    'Example to access parsed data
    Debug.Print "Database Server: "; config("database")("server")
End Function

3. Sample Output (Immediate Window):

Database Server: 192.168.1.1

Deep Dive

The practical acceptance of TOML in the developer community showcases a trend towards simpler, more human-readable configuration files, standing in contrast to the formerly prevalent XML. TOML’s design philosophy emphasizes clear semantics and aims for straightforward parsing with minimal overhead. In VBA, handling TOML directly involves manual parsing or leveraging external tools to convert TOML into a more VBA-friendly format due to the lack of native support. While this manual parsing method showcases a fundamental approach, utilizing external libraries or intermediate formats like JSON may offer more robust and error-resistant parsing strategies. Given VBA’s extensive integration with Microsoft Office, converting TOML to JSON and using VBA’s native JSON parsing capabilities (where applicable) or third-party JSON parsers could provide a more streamlined workflow. Furthermore, with the continuous evolution of data serialization formats, programmers should also consider YAML, which, like TOML, emphasizes human readability but offers different trade-offs in terms of complexity and flexibility.