Excel: pass array argument to formula through cell

Code-wise this seems so simple, but I still get stuck on a #VALUE.

I made a custom Excel function taking an array as an argument, which in itself is working fine when used just like that: =countArray({3,5}) would give 2 (for illustration, actual formula is different).

However, the actual array I'd like to use as the argument is contained in another cell (e.g. ={3,5}). As such, I'd like to call the function as =countArray(A1), using a reference to the cell containing the array I'd like to use as the argument instead of directly including the array, so I could use one and the same formula.

Is there a way to do this?

Cell formulas:
A1: ={3,5}
B1: =countArray({3,5})
B2: =countArray(A1)

Function countArray(arr()) As Integer
countArr = UBound(arr)
End Function

The formula in B1 works, the one in B2 doesn't. As for the contents of A1, when putting it in like this Excel only shows 3 for the cell value -- because of this I assumed the cell therefore does actually contain an array. Is this not the case then? Is it not possible for cells to contain array as their value (or have these referred to from other cells) without storing it as a delimited string and converting it to an Array?

Edit: it's been a few years, so maybe I could comment a bit on roads I've tried since I posted this question. Initially I tried to go in the direction of doing operations on in-cell JSON structures, with a certain amount of success. Since then I'd also found that the recent Power Query (since Excel 2016 natively integrated) does support list structures (among others) in its 'cells' though, and does in fact supported related functions. I don't use any of these as much anymore (now more interested in Spark), but hope this might help others stumbling upon this question.

回答1:

I don't think so you have a direct way... as you would want to Convert Cell Value to Array

Hope it helps!!!

回答2:

You could try this:

Function countarray(arr As Variant) As Long
    If typename(arr) = "Range" Then
        countarray = UBound(Evaluate([arr].Formula))
    Else
        countarray = UBound(arr)
    End If
End Function