Simulate namespaces in VBA and access functions like if you had a Framework setup or at least that was what I understood...


I was inspired by this piece of code: Framework.Strings.StartsWith in this post


  1. As I'm basically coding against the predeclared instance, is there a risk of a memory leak?
  2. I see benefits while coding and accessing the functions, do you see downsides?

Code / File structure:



Class: Framework (simplified version)


Option Explicit

' Copywrite (C) 2019 Ricardo Diaz
' This file is distributed under the GPL-3.0 license
' Obtain a copy of the GPL-3.0 license <http://opensource.org/licenses/GPL-3.0>

Private Type TFramework
    Collection As CollectionUtilities
End Type

Private this As TFramework

Public Property Get Collection() As CollectionUtilities
    Set Collection = this.Collection
End Property

Public Property Set Collection(ByVal Value As CollectionUtilities)
    Set this.Collection = Value
End Property

Private Sub Class_Initialize()
    Set Collection = New CollectionUtilities
End Sub

Class: CollectionUtilities


Option Explicit

'Credits: https://jkp-ads.com/Articles/buildexceladdin02.asp

'@Ignore ProcedureNotUsed
Public Function IsIn(ByVal Collection As Variant, ByVal Name As String) As Boolean
' Procedure : IsIn Created by Jan Karel Pieterse
' Company   : JKP Application Development Services (c) 2005
' Author    : Jan Karel Pieterse
' Created   : 28-12-2005
' Purpose   : Determines if object is in collection
    Dim newObject As Object

    Set newObject = Collection(Name)

    IsIn = (newObject Is Nothing)

    If IsIn = False Then
        Set newObject = Collection(Application.WorksheetFunction.Substitute(Name, "'", vbNullString))
        IsIn = (newObject Is Nothing)
    End If
End Function

'@Ignore ProcedureNotUsed
Public Sub ClearCollection(ByRef Container As Collection)
    Dim counter As Long
    For counter = 1 To Container.Count
        Container.Remove counter
End Sub

'@Ignore ProcedureNotUsed
Public Function HasItem(ByVal Container As Collection, ByVal ItemKeyOrNum As Variant) As Boolean
    Dim temp As Variant
    On Error Resume Next
    temp = IsObject(Container.Item(ItemKeyOrNum))
    On Error GoTo 0
    HasItem = Not IsEmpty(temp)
End Function

'@Ignore ProcedureNotUsed
Public Function ArrayToCollection(ByVal evalArray As Variant) As Collection
' Credits: https://stackoverflow.com/a/12258926/1521579
    Dim tempCollection As Collection
    Dim Item As Variant

    Set tempCollection = New Collection

    For Each Item In evalArray
        tempCollection.Add Item
    Next Item

    Set ArrayToCollection = tempCollection
End Function

'@Ignore ProcedureNotUsed
Public Sub AddArrayItemsToCollection(ByVal evalCollection As Collection, ByVal evalArray As Variant)

    Dim Item As Variant

    For Each Item In evalArray
        evalCollection.Add Item
    Next Item

End Sub

'@Ignore ProcedureNotUsed
Public Sub DebugCollectionValues(ByVal evalCol As Collection)
    Dim counter As Long
    For counter = 1 To evalCol.Count
        Debug.Print evalCol(counter).Name, evalCol(counter).Value
    Next counter
End Sub

'@Ignore ProcedureNotUsed
Public Function TableRowToCollection(ByVal sourceCell As Range) As Collection

    Dim EvalCell As Range
    Dim evalTable As ListObject
    Dim evalListRow As ListRow

    Dim evalCollection As Collection

    Dim evalRow As Long

    Set evalTable = sourceCell.ListObject

    evalRow = sourceCell.Row - evalTable.HeaderRowRange.Row

    Set evalListRow = evalTable.ListRows(evalRow)
    Set evalCollection = New Collection

    For Each EvalCell In evalListRow.Range.Cells
        evalCollection.Add EvalCell.Value2, evalTable.HeaderRowRange.Cells(EvalCell.Column - evalTable.HeaderRowRange.Column + 1).Value2
    Next EvalCell

    Set TableRowToCollection = evalCollection

End Function

And call it like this:

How to call the framework utilities

Code has annotations from Rubberduck add-in


1 Answer 1


It's tradeoffs. On one hand you get useful static methods in a toolbox, functions that don't accidentally show up in Excel's IntelliSense in the formula bar. On the other hand, you could get the very same out of a standard module with Option Private Module specified, minus the possibility of client code mistakenly attempting to create a New instance of the class... and since class modules are either Private or PublicNotCreatable, the best way to implement this Framework namespace would be to have an Excel add-in VBA project named Framework, exposing these predeclared instances to whoever wants to consume its members. The cost for using classes is a LongPtr per instance - the actual size of that pointer depends on the bitness of the host application... and it's negligible.

The main difference between a class and a standard module is that you can pass an object reference to a procedure, but you can't pass a module; you can't declare a variable As that module "type", New it up (in the same VBA project, accidentally or not), or make it handle events or implement interfaces.

I think framework-level static/shared functions, in VBA, feel right at home in a standard module.

So you would reference the Framework add-in project, and then you'd do ?Framework.Collections.HasItem(items, item), and ?Collections.HasItem(items, item) would also be legal, and yes, ?HasItem(items, item) would be as well! ...unless the identifier is shadowed by a public member with the same name in a higher-priority referenced library, or in the referencing VBA project itself.

You would use classes in a Framework add-in to encapsulate some state - you could have a ProgressIndicator class exposed, for example, that registers a worker method that never needs to know anything about any ProgressIndicatorForm, and even less about any MSForms.Label control's Width. You'd have a StringBuilder class, a FileWriter class, a SqlCommand class - you want classes that represent & encapsulate some data and implement some behavior, and modules that group related procedures together... but why not have a custom collection class (some List perhaps) that exposes all the things you ever dreamed a Collection could do, instead of a CollectionUtilities class/module?

Maybe it's just the "utilities" name ringing a "bag of whatever" ring to it, but having Utilities in the name of every module feels like redundant suffixing, I find.

You want an @IgnoreModule ProcedureNotUsed annotation at module level here, rather than having an annotation on every individual member - that way it's much easier to toggle it back on if you choose to leave that code as a bunch of modules to import in every new project (rather than an Excel add-in you'd reference): these Rubberduck inspection results are telling you which members can be removed from this particular project; no need to bloat up a project you're distributing with a whole framework!

It's not clear what the difference is between IsIn and HasItem functions - both feel like just slightly different solutions to the same problem (although I suspect you removed an On Error Resume Next from JKP's code, since that function can't return False without throwing error 9), and that makes a confusing API. I'd keep HasItem but swap the IsObject check for an Err.Number = 0 check.

Variables could be declared closer to where they're used in a few places, especially in TableRowToCollection.

Since this is a framework, there's an opportunity for every public member to have a short @Description annotation comment, and for modules to have a @ModuleDescription.

Side note, it's "copyright", ...and this Q&A post is licensed under CC-BY-SA as per Stack Exchange terms of service ;-)

  • \$\begingroup\$ Thank you Mathieu! I have more clarity about the pros, cons and alternatives. Also good suggestions to implement along my solution. and about the copyright...just blushed ;( \$\endgroup\$ Jan 11, 2020 at 14:06
  • 1
    \$\begingroup\$ I did this...although I suspect you removed an On Error Resume Next you saved me like 2 hours of debugging! \$\endgroup\$ Jan 11, 2020 at 21:09
  • \$\begingroup\$ @RicardoDiaz if there was a OERN without a OEG0, there should have been a result for UnhandledOnErrorResumeNext - with a RestoreErrorHandling quickfix offered ;-) \$\endgroup\$ Jan 11, 2020 at 21:23
  • \$\begingroup\$ Yes, that I catched with the help of Rubberduck, but the problem was that I added OEG0 before err.number was evaluated, so it always returned 0. Thank you! \$\endgroup\$ Jan 11, 2020 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.