VBA Function to Parse MSISDN Number for Country and State

This document outlines a function written in VBA to parse Mobile Station International Subscriber Directory Number (MSISDN) into its respective country and state. The function will include input validation and thorough documentation.

Function Overview

Function Name

ParseMSISDN

Purpose

The function aims to take an MSISDN number as input and return the corresponding country and state based on predefined mappings.

Parameters

• msisdn As String: The MSISDN number to be parsed.
• countryMap As Object: A dictionary that maps country codes to country names.
• stateMap As Object: A dictionary that maps area codes to state names.

Return Type

The function returns a String containing the country and state in the format "Country, State".

Exceptions

• Raises an error for invalid MSISDN formats.
• Raises an error if the country or state cannot be found in the provided mappings.

Code Implementation

' Required Libraries: None
' The function uses built-in VBA functionalities.

Function ParseMSISDN(msisdn As String, countryMap As Object, stateMap As Object) As String
    ' Validate the input MSISDN format (should start with "+" followed by digits only)
    If Not msisdn Like "+[0-9]*" Then
        Err.Raise Number:=1000, Description:="Invalid MSISDN format"
    End If

    ' Extract country code and area code from MSISDN
    Dim countryCode As String
    Dim areaCode As String
    countryCode = Left(msisdn, InStr(2, msisdn, " ") - 1)  ' Assume space follows country code
    areaCode = Mid(msisdn, Len(countryCode) + 2, 3)         ' Assume next 3 digits are area code

    ' Lookup country and state using dictionaries
    Dim country As String
    Dim state As String

    On Error Resume Next ' Handle potential errors in dictionary lookup

    country = countryMap(countryCode) ' Lookup country
    state = stateMap(areaCode)        ' Lookup state

    On Error GoTo 0 ' Reset error handling

    ' Check if country and state were found
    If country = "" Then
        Err.Raise Number:=1001, Description:="Country not found for the given code"
    End If
    If state = "" Then
        Err.Raise Number:=1002, Description:="State not found for the given area code"
    End If

    ' Return the formatted result
    ParseMSISDN = country & ", " & state
End Function

Explanation of the Code

1. Input Validation: The function checks if the MSISDN format is valid. It raises an error if not.
2. Extraction of Codes: The function extracts the country and area codes from the MSISDN.
3. Dictionary Lookup: It uses dictionaries to map country codes and area codes to their respective names.
4. Error Handling: It raises errors when the country or state is not found, ensuring robustness.
5. Return Format: Finally, it concatenates and returns the country and state as a single string.

Usage Example

To use the ParseMSISDN function effectively, create the dictionaries for country and state mappings, then call the function.

Sub ExampleUsage()
    Dim countryMap As Object
    Dim stateMap As Object
    Dim result As String

    ' Initialize dictionaries
    Set countryMap = CreateObject("Scripting.Dictionary")
    countryMap.Add "+1", "United States"
    countryMap.Add "+44", "United Kingdom"

    Set stateMap = CreateObject("Scripting.Dictionary")
    stateMap.Add "123", "California"   ' Area code examples
    stateMap.Add "456", "London"

    ' Example MSISDN
    Dim msisdn As String
    msisdn = "+1 123 4567890" ' US MSISDN

    ' Parse the MSISDN
    On Error GoTo ErrorHandler
    result = ParseMSISDN(msisdn, countryMap, stateMap)

    ' Display result
    MsgBox result ' Expected Output: "United States, California"
    Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description
End Sub

Conclusion

This function is scalable, efficient, and includes robust error handling, making it suitable for parsing MSISDN numbers accurately. For further learning in data manipulation and VBA, consider exploring courses offered by Enterprise DNA.