Re: Array Function Returning Only a Value Error

[Govert van Drimmelen]

Hi Faraz,

When trying to understand #VALUE errors, it helps to simplify a bit
and wrap your function in an exception handler.
I remove the IsVolatile and IsMacroType attributes (you should only
set them if they really are required and you understand exactly why
you need them).

So the function I tested looked like this:

Function GetArrayItem(theArray As Object, itemIndex As Integer) As
Object
Try
Return theArray(itemIndex)
Catch e As Exception
Return "!!! Error: " & e.ToString()
End Try
End Function

When called from a worksheet as "=GetArrayItem({73,"asd", 34}, 1)" I
got the following error:

"!!! Error: System.RankException: Attempted to operate on an array
with the incorrect number of dimensions. [...]"

This is our clue to what is going on. The Excel C API only supports 2-
dimensional arrays, so our input value, which we type as "Object" will
come in as a 2D object array. Excel converts a 1D array into a 2D
array with a single row. So the fixed up function looks like this:


Function GetArrayItem(theArray As Object, itemIndex As Integer) As
Object
Try
Return theArray(0, itemIndex)
Catch e As Exception
Return "!!! Error: " & e.ToString()
End Try
End Function

Regards,
Govert


* * * * * * * * * * * * * * * * * * * * * * * * * * * *

Ensure that the Excel-DNA project continues by
making your donation - http://excel-dna.net/support/

* * * * * * * * * * * * * * * * * * * * * * * * * * * *



On May 24, 3:55 am, FAQ <farazem...@gmail.com> wrote:
> Hi,
>
> While the following VBA edition works good as *
> MyUDF({1,20,89,"B","C",95,"D"},5)* returning the 5th item, i.e. "C" and *
> MyUDF({1,20,89,"B","C",95,"D"},2)* returning the 2nd item i.e. 20 what
> might be wrong with the DNA Version?
>
> VBA Version:
>
> *Function MyUDF(ArrayVariable As Variant, PositionVariable As Long) As
> Variant*
> *    MyUDF = ArrayVariable(PositionVariable)*
> *End Function*
>
> Excel DNA Version:
>
> *<ExcelFunction(DESCRIPTION:="A Test Function.", CATEGORY:="Test
> Functions", ISVOLATILE:=True, ISMACROTYPE:=True)> Public Function
> CheckArrays(MyArray As Object, MyItem As Long)*
> *    CheckArrays = MyArray(MyItem)*
> *End Function*