UDF/array formula: get the size of the array range in VBA?

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
Hello

Assume myFunc(...) is a UDF that return an array.
Assume this UDF is used in a range on a spreadsheet:

range A2:D8

{=myFunc(...)}

Within the VBA code of this UDF, I would like to know the space available on the sheet for the UDF results.
In the example above, I would like to code this kind of thing:

Code:
Function myFunc(...)
   ...
      nRows = ...
      nCols = ...
   ...
End Function

Where I hope to get nRows=7 and nCols=4, since the UDF has been used in the range A2:D8 in this example.

Would be very useful to have that because this would tell me how much results I need to return.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I'm not familiar with functions returning arrays, but activecell.currentregion.rows.count and activecell.currentregion.columns.count would provide the 7 and 4 if the UDF is used contiguously and in isolation from other cells.
 
Upvote 0
Why don't you create a function to find out the size of the array returned from myFunc?
 
Upvote 0
Gates,

Activecell does not return the range where the output of an array formula will be dropped in.


jindon,

The myFunc can easily find out the (potential) size of it result.
What I cannot find out is the size of the range that the enduser of myfunc makes available on the spreadsheet to return the data.
This cause no other problem than returning more data than will be visible or less data (causing then #VALUES? in the array range).
 
Upvote 0
With X potential rows there are up to X potential rectangular solutions to determine available unoccupied cells. For example, if you have a 10 by 10 grid of output to put [its upper-left corner] in cell A1, then if the rectangle A1:J10 is completely unoccupied, you're fine - you can show all 100. But let's say there's a diagonal of occupied cells consisting of A10,B9...J1. Then you could show 9 cells in A1:I1. Or 16 in A1:H2. Or 21 in A1:G3. (etc.)

I'll assume that you want to show the maximum number of cells that form a rectangle. That might not be the case - maybe you only want to show full rows or full columns - but that's where I'm heading below. Adjust to suit.

In the above specific example you might quickly deduce that a 5 by 5 square would show the most results. But now assume the only occupied cell in A1:J10 is I2. Or E9. Or both. I don't think there's a fully generalizable solution for X rows and Y columns, so below I show a scoring routine that establishes the largest available rectangle (up to size X by Y - such as 7 by 4).

It's only partially tested. If you deal with huge ranges you could speed it up further by using .End(xlToRight).Column on each row, and use With Activesheet, etc. Note the statement
iC2 = j - 1
vastly economizes its work so it's already very fast for most data layouts.

Does this or some adaptation help?
Code:
Sub BigRect()
'returns the largest unoccupied rectangle with "Dest" as upper-left cell
'Make a pass through each row to get a largest rectanglar candidate
  Const iMaxRows = 999999, iMaxColumns = 999999  ' the desired 7,4
  Dim s As String, s2 As String, rng As Range
  Dim i As Long, j As Long, iWinRow As Long, iWinCol As Long
  Dim iR1 As Long, iC1 As Long, iR2 As Long, iC2 As Long
  Dim iCount As Long, iWinCount As Long
  
  Set rng = Range("dest")
  If rng.Formula <> "" Then MsgBox "Dest nonempty": Exit Sub
  iR1 = rng.Row
  iC1 = rng.Column
  iR2 = iR1 + iMaxRows - 1
  iC2 = iC1 + iMaxColumns - 1
  i = rng.End(xlDown).Row
  j = rng.End(xlToRight).Column
  If iR2 > i Then iR2 = i
  If iC2 > j Then iC2 = j
  For i = iR1 To iR2
    Application.StatusBar = "row " & i
    For j = iC1 To iC2
      If ActiveSheet.Cells(i, j).Formula <> "" Then
        iC2 = j - 1
        Exit For
      End If
    Next j
    iCount = (i - iR1 + 1) * (j - iC1)
    If iCount > iWinCount Then
      iWinRow = i: iWinCol = iC2: iWinCount = iCount
    End If
  Next i
  s = Mid(rng.Address, 2, InStr(2, rng.Address, "$") - 2)
  Set rng = rng.Offset(0, iWinCol - iC1)
  s2 = Mid(rng.Address, 2, InStr(2, rng.Address, "$") - 2)
  MsgBox "Biggest range is " & s & iR1 & ":" & s2 & iWinRow & _
  vbCrLf & "(" & iWinCount & " cells)"
  Set rng = Nothing
  Application.StatusBar = ""
End Sub
 
Upvote 0
Re: UDF/array formula: get the size of the array range in VB

Good question. Use Application.Caller. Look it up in Excel VBA help. For an application, see
Selecting a random subset without repeating -- using a user defined function (UDF)
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/vba.html#from_worksheet
Hello

Assume myFunc(...) is a UDF that return an array.
Assume this UDF is used in a range on a spreadsheet:

range A2:D8

{=myFunc(...)}

Within the VBA code of this UDF, I would like to know the space available on the sheet for the UDF results.
In the example above, I would like to code this kind of thing:

Code:
Function myFunc(...)
   ...
      nRows = ...
      nCols = ...
   ...
End Function

Where I hope to get nRows=7 and nCols=4, since the UDF has been used in the range A2:D8 in this example.

Would be very useful to have that because this would tell me how much results I need to return.
 
Upvote 0
Thanks a lot.

I tried

nRows = Application.Caller.Rows.Count
nCols = Application.Caller.Columns.Count

and it works perfectly.
Very useful.
 
Upvote 0
You are welcome. Yes, Application.Caller is a very useful and way-too-overlooked capability.
Thanks a lot.

I tried

nRows = Application.Caller.Rows.Count
nCols = Application.Caller.Columns.Count

and it works perfectly.
Very useful.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top