CODEWITHSUNDEEP
arraysexcelvba
tbischel
tbischel

Reputation: 6477

Skip column in an array

I have a VBA function that returns an array to be displayed in Excel. The array's first two columns contain ID's that don't need to be displayed.

Is there any way to modify the Excel formula to skip the first two columns, without going back to create a VBA helper to strip off the columns?

The formula looks like this, where the brackets let the array be displayed across a span of cells:

{=GetCustomers($a$1)}

Upvotes: 2

Views: 5835

Answers (4)

jtolle
jtolle

Reputation: 7113

The closest thing Excel has to built-in array manipulation is the 'INDEX' function. In your case, if the array returned by your 'GetCustomers' routine is a single row, and if you know how long it is (which I guess you do since you're putting it into the sheet), you can get what you want by doing something like this:

=INDEX(GetCustomers($A$1),{3,4,5})

So say GetCustomers() returned the array {1,2,"a","b","c"}, the above would just give back {"a","b","c"}.

There are various ways to save yourself having to type out your array of indices, though. For example, 

=COLUMN(C1:E1)

will return {3,4,5}, and you can use that instead:

=INDEX(GetCustomers($A$1),COLUMN(C1:E1))

This trick doesn't work with a true 2-D array, though. 'INDEX' will return a whole row or column if you pass in a zero in the right place, but I don't know how to make it return a 2-D subset. EDIT: You can do it, but it's cumbersome. Say your array is 2x5, and you want the last three columns. You could use:

=INDEX(GetCustomers($A$1), {1,1,1;2,2,2}, {3,4,5;3,4,5})

(FURTHER EDIT: chris neilsen provides a nice way to compute those arrays in his answer.)

Charles Williams has a link on his website that explains more about using arrays like this here:

http://www.decisionmodels.com/optspeedj.htm

He posted that in response to this question I asked:

Is there any documentation of the behavior of built-in Excel functions called with array arguments?

Personally, I use VBA helper functions for things like this. With the right library routines, you can do something like:

=subseq(GetCustomers($A$1),3,3)

in the 1-D case, or:

=hstack(subseq(asCols(GetCustomers($A$1)),3,3))

in the 2-D case, and it's much more clear.

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53136

You can use the INDEX function to extract items from the return array
- formula is in an range starting at cell B2 

{=INDEX(getcustomers($A$1),ROW()-ROW($B$2)+1,COLUMN()-COLUMN($B$2)+3)}

Upvotes: 0

Fink
Fink

Reputation: 3436

I suggest modifying the 'GetCustomers' function to include an optional Boolean variable to tell the function to return all the columns, or just the single column. That would be the cleanest solution, instead of trying to handle it on the formula side.

Public Function GetCustomers(rng as Range, Optional stripColumns as Boolean = False) as Variant()

    If stripColumns Then  'Resize array to meet your needs

    Else  'return full sized array

    End If
End Function

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53136

simplest solution is to just hide the first two columns

another may be to use OFFSET to resize the returned array
syntax is 

OFFSET(reference,rows,cols,height,width)

Upvotes: 0

Related Questions