2

I can enter any built-in scalar Excel worksheet function with CTRL-ALT-ENTER, and get an array result. Under the hood, are such functions designed to return an array? Or is it a scalar function called repeatedly for each element in the input-array, returning a scalar for each one?

I'd like to write a UDF which returns a scalar, but can be called by a range-array with CTRL-ALT-ENTER.

Public Function MyScalar(MyInput As Double) As Double
          Application.Volatile
          MyScalar = MyInput * 2
End Function

The only examples i've seen of this return an array, rather than a scalar. Is returning an array the only way to do this?

johny why
  • 2,047
  • 7
  • 27
  • 52
  • It would be cool if you could add a marker at the top of any scalar function so that VBA will treat it like an array functionEg `ArrayFunction`, similar fashion as `Application.Volatile`. Under the hood, VBA would call the scalar repeatedly for each input-cell. A potential drawback is that a function designed to handle arrays may give better performance that a scalar called repeatedly. – johny why Apr 16 '21 at 18:27
  • 1
    Possibly of some interest to c.f. further readings [here](https://stackoverflow.com/questions/58534445/how-to-distinguish-if-sumproduct-needs-to-be-inserted-with-cse-or-not/58536210#58536210) and [how to add dynamic array formula](https://stackoverflow.com/questions/61138029/excel-vba-how-to-add-dynamic-array-formula/61138478#61138478) and [CSE Entering of array formulae](https://stackoverflow.com/questions/67038391/issue-with-array-formula-entered-into-cells-by-vba-is-not-behaving-as-expected) @johnywhy – T.M. Apr 17 '21 at 17:24

0 Answers0