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

Question

Is it possible to make the following function returns either multiple values or single value according to how the user enters 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

A good introduction ia available here: http://www.cpearson.com/excel/returningarraysfromvba.aspx — Pieter Geerkens, Jun 13 '15 at 13:25
possible duplicate: http://stackoverflow.com/questions/18185089/excel-passing-array-to-user-defined-function-vba — Pieter Geerkens, Jun 13 '15 at 13:27

score 4 · Accepted Answer · answered Jun 13 '15 at 13:53

4

Just examine 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

answered Jun 13 '15 at 13:53

Gary's Student

95,722
10
59
99

@Gary's_Student - Thanks, I don't know about this one before. – Astaroth Jun 13 '15 at 16:31
@Astaroth See VBA Help................. lots of cool stuff available from *Application.Caller* – Gary's Student Jun 13 '15 at 16:33

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

1 Answers1