Embedding arrays within formulas

[insomniac_ut]

Hello,

I have a custom UDF that requires a 2-element array in one of the parameters of the form: {string 1, string 2}.

If I construct the formula such that the array is a 'hard' copy, i.e. =MyUDF(param1, param2, {"abc", "def"}) then it works perfectly. However, I want to be able to pull the actual string values themselves off cells. This where I've become stuck.

I've tried the following,

1. Concatenating { } with the string values such as "{"&string1&","&string2&"}". The problem with this is that the end result is "{"abc", "def"}", so Excel treats the parameter as a string rather than a 2-element array

2. Concatenating string1,string2 in a cell and then wrapping this up in an ADDRESS and INDIRECT call. So the third parameter become INDIRECT(ADDRESS(ROW(cell),COLUMN(cell))). This didn't work either.

3. I also tried "forcing" the result to become an array by wrapping the second solution in an INDEX call, so it becomes INDEX(INDIRECT(ADDRESS(ROW(cell),COLUMN(cell))),1,1). This didn't work either.

Am a bit stuck now and would appreciate any thoughts solutions. My preference would be to achieve this in-sheet without any VBA.

Thanks.

PS - I don't have the ability to change the UDF itself, it's a closed source add-in that's being used.

 

[pgc01]

Hi

Let's say that the strings "abc" and "def" are in A3 and B7. Try:

=MyUDF(param1, param2, CHOOSE({1,2},A3,B7))

 

[insomniac_ut]

Hi

Let's say that the strings "abc" and "def" are in A3 and B7. Try:

=MyUDF(param1, param2, CHOOSE({1,2},A3,B7))

Thank you, I'll try this. In the meantime if "abc" and "def" are in contiguous cells then I can simply do =MyUDF(param1, param2, range1). However, I'll try the suggested method above.