Saturday 11 February 2017

VBA - Brackets around a variable executes default member, useful for worksheet functions

So, you may be aware that if you place round brackets around a variable it will execute the default member. 'So what?' you may say. I have never used this because it requires the reader of the code to know their default members. However today, I can see one good usage.

When one writes a function callable from a worksheet it very often needs to be called from VBA as well. I often would like the function to accept a multi-cell Range object and process the cell values, I often would like to call the same function from VBA passing in a two-dimensional Variant array.

To handle the two different types of input parameter one solution could be to use TypeName to establish if argument is a "Range" and then call Range.Value on it and just pass through if TypeName is "Variant()".

However, using round brackets handles both cases.

Function UseRoundBrackets(v As Variant) As Variant
    Dim v2 As Variant
    v2 = (v)
    
    '* Do some work on v2 safe that is always a Variant()
End Function

If you want some code to analyze what is going on then try this

'* This function is designed to accept either a range or 
'* a variant array containing the content of a range (.Value2)
Function WorksheetAndVBACallable1(v As Variant) As Variant
    '*  didactic/illustrative (not for production)
    Debug.Assert TypeName(v) = "Range" Or TypeName(v) = "Variant()"  
    
    Dim v2 As Variant
    v2 = (v)
    
    '*  didactic/illustrative (not for production)
    Debug.Assert TypeName(v2) = "Variant()"   
    
    '* Do some work on v2
    WorksheetAndVBACallable1 = DoWork(v2)

End Function

'* This function is designed to accept either a range or 
'* a variant array containing the content of a range (.Value2)
Function WorksheetAndVBACallable2(v As Variant) As Variant
    '*  didactic/illustrative (not for production)
    Debug.Assert TypeName(v) = "Range" Or TypeName(v) = "Variant()"  
    
    Dim v2 As Variant
    If TypeName(v) = "Range" Then
        v2 = v.Value2
    Else
        v2 = v
    End If

    '*  didactic/illustrative (not for production)
    Debug.Assert TypeName(v2) = "Variant()"  

    '* Do some work on v2
    WorksheetAndVBACallable2 = DoWork(v2)
    
End Function

No comments:

Post a Comment