Static classes in Excel VBA

From StackOverflow I have learnt a trick that allows static classes in Excel VBA, i.e. not having to use the New keyword. Now standard modules (.bas files) can serve the same purpose as a Static module but the VBA compiler will not insist on full qualification of a sub or function in a standard module. So if you want to enforce qualification to meet a coding standard then a Static class is useful.

A static class is also useful for those instances where you want static state, admitted the data will be global but so would a standard module! It would be useful to have a convention where a class called 'Foo' has a companion static class called say 'Foo_shared' or 'Foo_Static' or 'Foo_Common' to house those members which would be declared as static in languages such as C# or Java.

However, this technique requires a trick of exporting the module, editing an attribute, and re-importing. The attribute is VB_PredeclaredId which is by default False, edit this to True and reimport and you will get a Static class, it is the same mechanism by which Forms are static.

Here is example code in its raw form, ready to import

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "XmlFormatter"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public Function FormatString(ByVal sCore As String, ParamArray Args())
    
    Dim lArgMax As Long
    lArgMax = UBound(Args())
    
    Dim lCharLen As Long
    lCharLen = VBA.Len(CStr(lArgMax))
    
    Dim lArgLoop As Long
    For lArgLoop = LBound(Args()) To lArgMax
        Dim sArgLoop As String
        sArgLoop = Right$(String$(lCharLen, "0") & CStr(lArgLoop), lCharLen)
    
        sCore = VBA.Replace(sCore, "%" & sArgLoop, Args(lArgLoop))
        'Debug.Print sCore
    Next lArgLoop
    
    FormatString = sCore

End Function