Tuesday, 20 December 2011

Function Overloading – VBA

Function overloading, another wonders of the object oriented world where by the same name functions with different signatures provide the respective functionality based on the invoking arguments passed and their types.

Picking upon this and some google’ing I attempted to implement a function overloading mechanism via the help of “Variant” within VBA, as sadly function overloading is not inherently supported by VBA and it screams error of “Ambiguous names detected …”

Thus in order to implement the function overloading, we would have first construct a wrapper function which evaluates the signature of arguments sent and match to the respective function call and based upon which it executes and return the respective function.

Not very optimistic and delightful way of doing things but it’s something good to have in the repository for just one day when everything else fails.

'This enforces all the variables used within this module mush have their declartions explicitly
Option Explicit

'This enforces all the arrays implemented within this module starts from 0 'Zero'
Option Base 0

'This prevents the avilabiltiy of the function within this module to external refrencing projects
'and UDF's
Option Private Module

'Constant declaration
Private Const ConstIntVal As Integer = 10
Private Const ConstStrVal As String = "Hello"

'----- Overloading Implementation ------
Public Function addValue(Optional args As Variant) As Variant
    'Function signature identification and call relevant function component
    If IsMissing(args) Then
        addValue = addValue_Null
        Exit Function
    End If
    If Not IsArray(args) Then
        If TypeName(args) = "Integer" Then
            addValue = addValue_Int_Const(CInt(args))
            Exit Function
        ElseIf TypeName(args) = "String" Then
            addValue = addValue_Str_Const(CStr(args))
            Exit Function
        End If
        If TypeName(args(0)) = "Integer" Then
            addValue = addValue_Int(CInt(args(0)), CInt(args(1)))
            Exit Function
        ElseIf TypeName(args(0)) = "String" Then
            addValue = addValue_Str(CStr(args(0)), CStr(args(1)))
            Exit Function
        End If
    End If
End Function

'------ OverLoad Components ------

'Return 0 'Zero' for no arguments
Private Function addValue_Null() As Integer
    addValue_Null = 0
End Function

'Returns the constant added value to the argument (Integer)
Private Function addValue_Int_Const(val As Integer) As Integer
    addValue_Int_Const = val + ConstIntVal
End Function

'Returns the constant added value to the argument (String)
Private Function addValue_Str_Const(val As String) As String
    addValue_Str_Const = ConstStrVal & val
End Function

'Returns addtion of two integers
Private Function addValue_Int(val1 As Integer, val2 As Integer) As Integer
    addValue_Int = val1 + val2
End Function

'Returns concatenation of two strings
Private Function addValue_Str(val1 As String, val2 As String) As String
    addValue_Str = val1 + val2
End Function

Attached file illustrates the function overload mechanism.

Download Solution
Download solution

Link: http://stackoverflow.com/questions/64436/function-overloading-in-excel-vba

No comments: