Clever Geek Handbook

Can a custom VBA function know that the formula is being entered as an array formula?

Is it possible for the following function to return either multiple values ​​or a single value according to how the user enters the formula?

Public Function Test(ByVal flNumber As Double) As Variant
  Dim flResult1 As Double
  Dim sResult2 As String
  Dim bArrayFormula As Boolean

  flResult1 = Round(flNumber / 10 ^ 6, 1)
  sResult2 = "million"

  ' How to know that the formula is entered as an array formula?
  If bArrayFormula Then
    Test = Array(flResult1, sResult2)
  Else
    Test = flResult1 & " " & sResult2
  End If
End Function
+3


source to share


1 answer


Just check out Application.Caller



Public Function SmartFunction() As String
    addy = Application.Caller.Address
    If Range(addy).HasArray Then
        SmartFunction = "Array Formula"
    Else
        SmartFunction = "Normal Formula"
    End If
End Function
+4


source






More articles:


All Articles