VBA help with a conditional Subscript out of Range

Visual Basic for Applications (VBA)

Robbie Deal


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.

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.

Code Snippet

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

Top

Re: VBA help with a conditional Subscript out of Range

Andy Pope


Hi,

Could you make use of the LBound() and or UBound() functions

For i = lbound(ResultsTrend2) to ubound(ResultsTrend2)





Top

Re: VBA help with a conditional Subscript out of Range

Robbie Deal

I am not sure if i could have gone that route or not. But I actually did find a way to "bypass" it so to speak. I used the error to my advantage, through a suggestion on another forum. heres the code that I used to get around it.

Code Snippet

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



Top