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