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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Why don't you create a function to find out the size of the array returned from myFunc?
 

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
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).
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961

ADVERTISEMENT

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
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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.
 

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
Thanks a lot.

I tried

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

and it works perfectly.
Very useful.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

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