UDF to reference a specific element in an array

[GH]

I am atempting to reference a specific element in a one-diminsional
array. The array is created on a worksheet using the Bloomberg BLP
fuctionality. It essentially enters a number in each of five columns.
Previously, I have been cell referencing the desired item (the 5th)
from the returned array as it is the only item I want. This requires
that the array elements be on the worksheet.

I would rather have a user-defined function that returns just the 5th
item. Something like:

=ReturnedItem("Bloomberg Created Array",5)

Obviously the syntax isn't right but to illustrate.
Any suggestions?

GH

 

[Peter T]

An array of cells in columns is two dimensional, even if it's only in one
column. Try:

MsgBox Range("b5:b14")(5, 1), , Range("b5:b14")(5, 1).Address

As there is only one column you could also use Range("b5:b14")(5) which
returns the 5th cell in the range.

But:
vArr = Range("b5:b14")
msgbox varr(5,1)

Regards,
Peter T

 

[Peter T]

An array of cells in columns is two dimensional, even if it's only in one
column. Try:

MsgBox Range("b5:b14")(5, 1), , Range("b5:b14")(5, 1).Address

As there is only one column you could also use Range("b5:b14")(5) which
returns the 5th cell in the range.

But:
vArr = Range("b5:b14")
msgbox varr(5,1)

Regards,
Peter T

 

[Peter T]

I gave a misleading reply having misread the post. I said:

An array of cells in columns is two dimensional, even if it's only in one
column.

but should have said:
An array of cells in rows is two dimensional, even if it's only in one
column

and doesn't directly relate to the question. Instead try

Sub test()
Dim vArr
MsgBox Range("a2:j2")(1, 5), , Range("a2:j2")(1, 5).Address
vArr = Range("a2:j2")
MsgBox vArr(1, 5)
End Sub

Regards,
Peter T

PS sorry my earlier double post, not aware of sending twice.

 

[Peter T]

I gave a misleading reply having misread the post. I said:

An array of cells in columns is two dimensional, even if it's only in one
column.

but should have said:
An array of cells in rows is two dimensional, even if it's only in one
column

and doesn't directly relate to the question. Instead try

Sub test()
Dim vArr
MsgBox Range("a2:j2")(1, 5), , Range("a2:j2")(1, 5).Address
vArr = Range("a2:j2")
MsgBox vArr(1, 5)
End Sub

Regards,
Peter T

PS sorry my earlier double post, not aware of sending twice.