UDF to reference a specific element in an array

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

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


"GH" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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


"GH" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <peter_t@discussions> wrote in message
news:[email protected]...
> 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
>
>
> "GH" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>

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" <peter_t@discussions> wrote in message
news:[email protected]...
> 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
>
>
> "GH" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>