2

I'm using Excel-DNA to create UDFs within Excel and NetOffice for version-independent automation calls. Within a macro-type function, I'm able to use the following to get the formula from a cell:

ExcelReference cellRef; // of course in reality this is assigned
var formula = (string)this.excelCall(XlCall.xlfGetFormula, cellRef);

Additionally though, I'd like to know whether this is part of an array formula, and if so what its extent is. Using Excel automation, I can use something like:

Range("A1").HasArray
Range("A1").CurrentArray.Address

However, Microsoft discourage the use of automation within UDF calls: https://support.microsoft.com/en-us/kb/301443

So, is there a way to get the the HasArray and CurrentArray properties via the C API, or does anyone know if it's okay (in the context of a UDF declared as macro-type) to use automation?

Jon G
  • 4,083
  • 22
  • 27
  • `HasArray` is allowed inside a UDF. It returns the correct result. `Function arr(r As Range): arr = r.HasArray: End Function` is valid as a UDF. What is discouraged (and generally prevented) is making _modifications_ to any other part of the `Workbook` or `Worksheet` from within a UDF. You are generally allowed to pull out information inside a UDF (with some exceptions). – Byron Wall Jun 24 '15 at 16:08
  • Thanks Byron - testing on this using automation has shown that for me, HasArray seems to work and hasn't caused any problems, but that CurrentArray can return the wrong answer when called within a UDF, returning individual cell even if it is part of a larger array – Jon G Jun 25 '15 at 10:33

1 Answers1

1

The GET.CELL information function, with information type_num 49 will return whether the cell is part of an array. From your Excel-DNA (macro-type) function:

bool isArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, cellRef);
Govert
  • 16,387
  • 4
  • 60
  • 70
  • Thanks, this definitely works as a replacement for IsArray. Is there a way to get the shape of the array formula (i.e. CurrentArray) as well, do you know? I would need to be able to tell, given two adjacent cells which are both part of an array and both have the same formula, whether or not they are both part of the same array? – Jon G Jun 25 '15 at 10:32
  • 1
    I think you'd have to select the cell, then use SELECT.SPECIAL with 6 to select the current array (like pressing CTRL+/). – Govert Jun 25 '15 at 11:02
  • Thanks - I'll give that a try – Jon G Jun 26 '15 at 08:15