Checking contents of arrays

[Bloomberg]

I have a problem with checking the contents of an array in a loop. The array
looks originally like this:

Dim varWorksheetInfoArray(15) As Variant
varWorksheetInfoArray(0) = "Avkdata"
varWorksheetInfoArray(1) = "Date"
etc…

I then call another sub and send the array along:
Call chartMaker(varWorksheetInfoArray)

Private Sub chartMaker(ByRef varWorksheetInfoArray() As Variant)
Dim i, j, k As Integer
ReDim rng(1 To UBound(varWorksheetInfoArray)) As Range

I then put the info from the varWorksheetInfoArray and put it in a new
array. I do this because I need the addresses of the cells containing the
text the was defined in varWorksheetInfoArray.

Do While k < UBound(rng)
Set rng(k) =
Worksheets(varWorksheetInfoArray(0)).Cells.Find(varWorksheetInfoArray(k),
LookIn:=xlValues)
k = k + 1
Loop

I then get some values for the integers i, j etc. and make some other stuff
that is not important here. The problem occurs in the following piece of code:
k = 2 'startvärde

Do While k < UBound(rng) - 1
If Not rng(k) Is Nothing Then
ActiveChart.SeriesCollection(k - 1).Values =
Sheets(varWorksheetInfoArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
rng(k).Offset(j, 0).Address)
Else: End If
k = k + 1

Loop

The problem is that the varWorksheetInfoArray has to have a “general”
dimension. I use this array many times but sometimes I just need three spaces
and sometimes ten spaces in the array. Thus sometime some spaces will be
empty, or undefined. When declaring the contents of the array I write:
varWorksheetInfoArray(0) = "Avkdata"
varWorksheetInfoArray(3) = "Price"
but some spaces will not be mentioned e.g.
varWorksheetInfoArray(2) is never mentioned. I don’t know what “value” that
space gets. The problem is that when loop through the rng array I need to
check if there are contents in the spaces in the array. But it just pops out
of the loop as it is now if there is some space that does not have any
contents. If you know how to solve this please help me out. Thank you very
much!!

[Tom Ogilvy]

if not rng(k) is empty then

--
Regards,
Tom Ogilvy

"Bloomberg" wrote:

> I have a problem with checking the contents of an array in a loop. The array
> looks originally like this:
>
> Dim varWorksheetInfoArray(15) As Variant
> varWorksheetInfoArray(0) = "Avkdata"
> varWorksheetInfoArray(1) = "Date"
> etc…
>
> I then call another sub and send the array along:
> Call chartMaker(varWorksheetInfoArray)
>
> Private Sub chartMaker(ByRef varWorksheetInfoArray() As Variant)
> Dim i, j, k As Integer
> ReDim rng(1 To UBound(varWorksheetInfoArray)) As Range
>
> I then put the info from the varWorksheetInfoArray and put it in a new
> array. I do this because I need the addresses of the cells containing the
> text the was defined in varWorksheetInfoArray.
>
> Do While k < UBound(rng)
> Set rng(k) =
> Worksheets(varWorksheetInfoArray(0)).Cells.Find(varWorksheetInfoArray(k),
> LookIn:=xlValues)
> k = k + 1
> Loop
>
> I then get some values for the integers i, j etc. and make some other stuff
> that is not important here. The problem occurs in the following piece of code:
> k = 2 'startvärde
>
> Do While k < UBound(rng) - 1
> If Not rng(k) Is Nothing Then
> ActiveChart.SeriesCollection(k - 1).Values =
> Sheets(varWorksheetInfoArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
> rng(k).Offset(j, 0).Address)
> Else: End If
> k = k + 1
>
> Loop
>
> The problem is that the varWorksheetInfoArray has to have a “general”
> dimension. I use this array many times but sometimes I just need three spaces
> and sometimes ten spaces in the array. Thus sometime some spaces will be
> empty, or undefined. When declaring the contents of the array I write:
> varWorksheetInfoArray(0) = "Avkdata"
> varWorksheetInfoArray(3) = "Price"
> but some spaces will not be mentioned e.g.
> varWorksheetInfoArray(2) is never mentioned. I don’t know what “value” that
> space gets. The problem is that when loop through the rng array I need to
> check if there are contents in the spaces in the array. But it just pops out
> of the loop as it is now if there is some space that does not have any
> contents. If you know how to solve this please help me out. Thank you very
> much!!
>