Understanding the Need for Templates in VB

In programming, the DRY principle—“Don’t Repeat Yourself”—is essential to writing clean, efficient, and maintainable code. This principle becomes particularly critical when working with languages like Visual Basic for Applications (VBA) where redundancy is common due to the limited support for abstraction techniques like templates in C++.

If you find yourself with multiple code snippets that perform similar functions on different object types, it’s time to consider a more efficient solution. The question arises: How can we consolidate similar operations without resorting to repetitive code?

Let’s dive into how you can leverage templates (in a way) within VBA or, preferably, using newer tools when applicable.

The Problem: Repetitive Code Snippets in VBA

Consider the following example where we have two code snippets that essentially do the same thing for different types:

Dim i as Obj1
Set i = RoutineThatReturnsObj1()
i.property = newvalue

Dim i as Obj2
Set i = RoutineThatReturnsObj2()
i.property = newvalue

In this case, it is clear that both routines perform an assignment and a property set operation, just on different types of objects. The programmer wishes to create a more generalized solution that reduces redundancy.

The Intended Solution

Creating a General Routine

To eliminate repetitive code, the programmer suggests a routine that can handle both scenarios:

Sub MyRoutine(o as ObjectType, r as RoutineToInitializeObject, newvalue as value) 
   Dim i as o
   Set i = r
   i.property = newvalue
End Sub

However, it’s important to note that native VBA lacks the sophisticated template capabilities found in languages like C++. Therefore, achieving a similar effect within the limitations of VBA can be challenging.

The Alternative: Using .NET with VBA

While traditional VBA doesn’t support generics or templates like C++, transitioning to the Visual Studio Tools for Office (VSTO) offers a viable alternative. If you’re willing or able to modernize your approach, you can use generics in .NET.

Implementing Generics

Here’s how you can utilize generics in a .NET context:

Function MyRoutine(Of O)(R As Delegate, newvalue As Object) As O
    Dim i As O = CType(r.Method.Invoke(Nothing, Nothing), O)

    ' You need another parameter to specify which property to use 
    ' and then use reflection to set the value'
    i.property = newvalue 
    return i
End Function

In this example:

• Of O represents a type parameter that can be specified at runtime.
• CType is used to cast the return value of the invoked delegate into the desired type.
• To set the property value, reflection is employed, providing dynamic access to the specified properties, which is not possible in traditional VBA.

Conclusion

While traditional VBA lacks the templating features seen in more advanced programming languages like C++, there are alternative methods to minimize code repetition. Using Visual Studio Tools for Office with .NET, you can leverage generics to create dynamic and reusable code structures.

If you find yourself frequently writing similar code across various object types within your VBA projects, consider modernizing your development environment. Embracing these changes not only enhances code clarity but also aligns with best practices in software development.

By understanding the limitations and possibilities within VB, you can write cleaner, more efficient code and adhere to the DRY principle effectively.