Solved: Excel 2003 Subscript issue

[r1pt1de]

Hey everyone,
Hopefully this one will be an easy one.
I am asking VBA to pull information from an access 2003 database, and place it in an array.
There are cases when I know that I will get a "Subscript out of Range" Error, and have to ask for a different set of values, due to having 0 values in the database for the parameters I asked for. IE no values in this table for that date range. So I will have to query a second table. The problem is, I dont know when those cases will arise.
Is there a way that I can make VBA catch that with an IF statement? I tried
If(ResultsTrend), and If(Not ResultsTrend). But neither of those worked. Basically I need it to analyze wether the array got populated with anything at all, and if not, go do this instead.
Here is my code so far for this block.

ResultsTrend = GetTrendData(Server, Username, Password, TrendStartDate, TrendEndDate, 0, 0, OverrideTemp(k).Range("F1").Value, Status)


If (Not ResultsTrend) Then
ResultsTrend2 = GetTrendData(Server, Username, Password, TrendStartDate, TrendEndDate, 0, 0, OverrideTemp(k).Range("F2").Value, Status)

For i = 1 To size
OverrideTemp(k).Cells(EventStartTime.Row, EventStartTime.Column).Value = ResultsTrend2(i)

OverrideTemp(k).Cells(EventStartTime.Row, EventStartTime.Column).HorizontalAlignment = xlCenter

OverrideTemp(k).Cells(EventDuration.Row, EventDuration.Column).Value = ResultsTrend2(i + 1)
OverrideTemp(k).Cells(EventDuration.Row, EventDuration.Column).NumberFormat = "0.0"
OverrideTemp(k).Cells(EventDuration.Row, EventDuration.Column).HorizontalAlignment = xlCenter


Set EventStartTime = OverrideTemp(k).Cells(EventStartTime.Row + 1, EventStartTime.Column)
Set EventDuration = OverrideTemp(k).Cells(EventDuration.Row + 1, EventDuration.Column)
Next

Else

[Bob Phillips]

Maybe try



If Not IsEmpty(ResultsTrend) Then

[r1pt1de]

i had tried that, for some reason it wasnt working properly. this is how i fixed it, at the recommendation found on another forum for a similar issue.


If UBound(ResultsTrend) > 0 Then
If Err.Number = 0 Then
testArray = 1
Else
testArray = 0
End If
End If

If (testArray = 0) Then
ResultsTrend2 = GetTrendData(Server, Username, Password, TrendStartDate, TrendEndDate, 0, 0, OverrideTemp(k).Range("F2").Value, Status)
size = (UBound(ResultsTrend2) + 1) / 2
index = 0
For i = 1 To size

OverrideTemp(k).Cells(EventStartTime.Row, EventStartTime.Column).Value = ResultsTrend2(index)

OverrideTemp(k).Cells(EventStartTime.Row, EventStartTime.Column).HorizontalAlignment = xlCenter

OverrideTemp(k).Cells(EventDuration.Row, EventDuration.Column).Value = ResultsTrend2(index + 1)
OverrideTemp(k).Cells(EventDuration.Row, EventDuration.Column).NumberFormat = "0.0"
OverrideTemp(k).Cells(EventDuration.Row, EventDuration.Column).HorizontalAlignment = xlCenter


Set EventStartTime = OverrideTemp(k).Cells(EventStartTime.Row + 1, EventStartTime.Column)
Set EventDuration = OverrideTemp(k).Cells(EventDuration.Row + 1, EventDuration.Column)

index = index + 2
Count = Count + 1
Next

Else