Re-sizing Array Functions

The recent updates to the ConBeamU spreadsheet contained two new functions for entering and re-sizing array functions, and since they will be useful on any spreadsheet containing array functions (which here means almost all of them) I thought they deserved a blog post of their own.

I have added the new functions to the CSplineA spreadsheet, which can be downloaded from:

The code for the new functions was taken from: Technicana .  The only changes I have made are:

  1. If the original function failed for any reason the array function being re-sized was deleted.  I have added a couple of lines so it will be re-written back to the spreadsheet.
  2. I have changed the shot-cut codes as detailed below.

To use the functions start by entering any function that returns an array in the top-left corner of the desired output range:


To expand the function to display the full extent of the array, press Ctrl-Shift-S:


To re-size the array select the required range and press Ctrl-Shift-R:


To move the array to a range overlapping with the original range (with the new range to the right and/or below the original), select the top left cell of the new range then press Ctrl-Shift-S (for the full array), or select the output range required and press Ctrl-Shift-R:


If the destination range is either to the left of, or entirely outside, the original range then the array function must first be copied to the top-left cell of the destination range, then proceed as above.

A word of warning: the functions clear the undo stack, and will write over any data in the output range without warning, so if there is any danger of the output range writing over any data you need, save your work before proceeding.

This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

4 Responses to Re-sizing Array Functions

  1. Jeff Weir says:

    Hi Doug. I’ve made quite a few changes to the Technicana code for my book…I’ll email it through to you.


    • dougaj4 says:

      Thanks Jeff. Using these two functions is one of those things that once you’ve done it, you wonder why you didn’t do it years ago (another thing to add to the Excel “not-quite-finished” list as well).

      I’ll be interested to see how you have improved them🙂


  2. Jose Colen says:

    Hello. I love this spreadsheet. Tk you so much.
    It is possible to move all the functions to the same sheet that the calcs? What I want was to join only the calcs sheet with other spreadsheets.
    Thnak you
    Best Regards
    Jose Colen


    • dougaj4 says:

      Jose – The functions should work from any file if you have the CSplineA file open.
      If you want to use the functions in another file without opening CSplineA then:
      Open both files
      In the Visual Basic Editor Project Explorer window drag the mResizeArrays module down to the header for the other file.
      Save as a type that allows macros (xlsb, xlsm, or xls)

      Or if you want a file that just has the Array resize functions you can save CSplineA with a new name and delete all the worksheets and in the VB Editor delete all the code modules other than mResizeArrays.

      Please ask if anything isn’t clear.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s