Excel VBA Worksheet Array UDF Which Returns a Scalar?

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?

Excel VBA Worksheet Array UDF Which Returns a Scalar?

0 Answers