Returning Jagged Arrays

KBARAD

New Member
Joined
Mar 31, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
I've got a VBA UDF which extracts (multiple) numbers from a string, cell, range, or array. It's used in a variety of scenarios so it needs that flexibility, and generally worked EXCEPT...when the range has different result sizes in different cells
The function returns a single number, array of results, or array of array of results (if a range/array input). I think the issue is excel doesn't handle returning a jagged array and it is trying to convert it to a 2d array and falling over at the different subarray lengths. Is there a way around this other than normalising to a 2d?

I've attached a watch showing the output variable in the function (dummy line as breakpoint added directly before the end function line). It works, but fails on the spreadsheet side. If the sub arrays are the same size it works. This is one of a set of very similar functions I'm developing - but with the same mechanism.
 

Attachments

  • excel issue.PNG
    excel issue.PNG
    15.9 KB · Views: 5

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Given that the result range is going to have to be rectangular, you'll need to normalise it to a 2d array.
 

KBARAD

New Member
Joined
Mar 31, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Given that the result range is going to have to be rectangular, you'll need to normalise it to a 2d array.
so you confirm it is an issue of requiring a rectangular result? It did take me an hour or so to find that seemed to be the case - I was hoping there was a way around it. Particularly since I will be more likely to be nesting this inside other functional calls
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,593
Members
417,152
Latest member
DayTimeSeby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top