How to write a UDF that returns variable length of data?

[risilamp]

If you have used Excel to download data from Bloomberg, you probably noticed that the function "BDH" can return an array of variable length. I was trying to design such a function but could not find a way.

The basic feature of a function like this is that it can be entered like an ordinary formula (not an array formula), but it returns multiple values. The values will be displayed in the range that has the formula as its top left corner.

Any pointers would be appreciated.

 

[xenou]

Hi,
I can't answer your specific questions about bloomberg. However, this is the strategy I would use to write an array to a worksheet, --first, use UBound functions to determine the size of the array, then write the array to a worksheet range of the same size.

Your array must be two-dimensional. I normally use 1-based arrays when working between arrays and worksheets, so if your array is 0-based you may need to adjust accordingly.

Sub Test()
Dim a() As Variant

'Create an array
ReDim a(1 To 2, 1 To 3)
a(1, 1) = "a"
a(1, 2) = "b"
a(1, 3) = "c"
a(2, 1) = "d"
a(2, 2) = "e"
a(2, 3) = "f"

'Write array to worksheet
'Use RESIZE to size the range first, then write the array to the sheet
Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a

End Sub

I declared my variable as a variant so that you can place any type of value in the array. I think there is no practical difference here between:

Dim a() as Variant
Dim a as Variant

Although the former would presumably be explicitly an array that could contain elements of different datatypes (numbers, dates, strings), and the latter could be anything, including an array.

------------------------------------------------------------------------

Excel Gurus:
I tried to create my array with the Array function but it did not work. I.e.:

Dim a as Variant
a = Array({"a","b","c";"d","e","f"})

The function did not like either my braces or semi-colons...
Is there a way to create an array with more than one dimension using the Array function?

Best wishes to all.

 

[Joe Was]

This will get random data from your data range and output it to your function's cell as a data string.
Like: =RandR("A",7,22,5)
For data in column "A", starting in Row 7 and ending in Row 22, return 5 data elements.

Function RandR(DataCol As String, DataStartRow As Long, DataEndRow As Long, NumToDo As Long) As String
'Custom User Defined Function, UDF.
'You can use this like any SheetFunction.
'Store in Standard Module, like: Module1.

Dim lngColX As Long, lngMySeedRow As Long, sw1a As Long
Dim strOut As String, strDataPick As String, strDataAddr As String

Application.Volatile
Randomize

sw1a = 1

For lngColX = 1 To NumToDo
'Get random data from data range, do this "NumToDo" times!
lngMySeedRow = Int((DataEndRow - DataStartRow + 1) * Rnd + DataStartRow)
strDataAddr = ActiveSheet.Range(DataCol & lngMySeedRow).Address
strDataPick = ActiveSheet.Range(strDataAddr).Value

If sw1a = 1 Then
strOut = strDataPick
sw1a = 0
Else
strOut = strOut & ", " & strDataPick
End If

Next lngColX

RandR = strOut
End Function

 

[GMorris]

Would it be possible to just write a normal UDF and ask it return a RANGE object, Or is that not what you had in mind? That's just the first thing that I thought of after reading the question...

 

[mortgageman]

If you have used Excel to download data from Bloomberg, you probably noticed that the function "BDH" can return an array of variable length. I was trying to design such a function but could not find a way.

The basic feature of a function like this is that it can be entered like an ordinary formula (not an array formula), but it returns multiple values. The values will be displayed in the range that has the formula as its top left corner.

Any pointers would be appreciated.

I'm no expert, but I'm pretty sure that you cannot write a UDF that behaves in the manner you describe. I'm amazed that this BDH function manages to do this. (return results in a range without entering CSE)

 

[xenou]

This support article might be useful - not sure - perhaps a workaround strategy?
http://support.microsoft.com/kb/286258
AB

 

[mortgageman]

This will get random data from your data range and output it to your function's cell as a data string.
Like: =RandR("A",7,22,5)
For data in column "A", starting in Row 7 and ending in Row 22, return 5 data elements.

Function RandR(DataCol As String, DataStartRow As Long, DataEndRow As Long, NumToDo As Long) As String
'Custom User Defined Function, UDF.
'You can use this like any SheetFunction.
'Store in Standard Module, like: Module1.

Dim lngColX As Long, lngMySeedRow As Long, sw1a As Long
Dim strOut As String, strDataPick As String, strDataAddr As String

Application.Volatile
Randomize

sw1a = 1

For lngColX = 1 To NumToDo
'Get random data from data range, do this "NumToDo" times!
lngMySeedRow = Int((DataEndRow - DataStartRow + 1) * Rnd + DataStartRow)
strDataAddr = ActiveSheet.Range(DataCol & lngMySeedRow).Address
strDataPick = ActiveSheet.Range(strDataAddr).Value

If sw1a = 1 Then
strOut = strDataPick
sw1a = 0
Else
strOut = strOut & ", " & strDataPick
End If

Next lngColX

RandR = strOut
End Function

Joe - when I try your example (I put the function in cell A1) all I get is a string of 4 commas.

 

[Joe Was]

Put your data column in "A", I used A7:A22 [the data was just random key strokes, per cell], then put the function in cell C2 as:

=RandR("A",7,22,5)

I get:
a, 77, G, Sue, 3

each time I select the function and hit "Enter" I get a new string returned.

 

[mikerickson]

Coupling it with a Change event, you can get similar actions.
Put =MagicTranspose(A1:C5) in F2 (no CSE) and f2:j4 will be filled with the transpose of a1:c5, yet only F2 will have a formula. Yes, it will react to changes in A1:C5 and will accept dynamic named range as its argument.

in a normal module

Public CellsToFix As New Collection
Public ArgumentsForCells As New Collection

Function magicTranspose(inRange As Range) As Variant
    If TypeName(Application.Caller) = "Range" Then
        On Error Resume Next
            CellsToFix.Add Item:=Application.Caller.Range("a1"), key:=Application.Caller.Range("A1").Address(, , , True)
            ArgumentsForCells.Add Item:=inRange, key:=Application.Caller.Range("A1").Address(, , , True)
        On Error GoTo 0
    End If
    
    magicTranspose = Application.Transpose(inRange)
End Function

In a sheets code module

Private Sub Worksheet_Calculate()
    Dim oneCell As Range, temp As String, xRRay As Variant
    If 0 < CellsToFix.Count Then
        Application.EnableEvents = False
        For Each oneCell In CellsToFix
            temp = oneCell.Formula
            On Error Resume Next
            xRRay = magicTranspose(ArgumentsForCells(oneCell.Address(, , , True)))
            
            oneCell.Resize(1, 1).Value = xRRay
            oneCell.Resize(1, UBound(xRRay, 1)).Value = xRRay
            oneCell.Resize(UBound(xRRay, 1), UBound(xRRay, 2)).Value = xRRay
            On Error GoTo 0
            oneCell.Formula = temp
        Next oneCell
        Application.EnableEvents = True
        Set CellsToFix = Nothing
        Set ArgumentsForCells = Nothing
    End If
End Sub

 

[mortgageman]

Put your data column in "A", I used A7:A22 [the data was just random key strokes, per cell], then put the function in cell C2 as:

=RandR("A",7,22,5)

I get:
a, 77, G, Sue, 3

each time I select the function and hit "Enter" I get a new string returned.

Cool - got it now. Wasn't what the OP asked, but nice anyway.