Run-time error "9" Subscript out of range ---- please help

[aatel]

Hi I am new to excel vb.
I am trying to import some values from one software (STAAD.Pro), to excel. It works for small quantity. But when the no of values are more, means number of items are more, it shows the error Runtime error - 9, subscript out of range.
But for small quantity of numbers it works perfectly.


******
The code I used is given below.
I suspect the problem with array. It may be problem with array stack overflow? Or it may be problem due to not defining delimit or ubound?
I dont know how to use ubound or delimit also.
Could anyone suggest me, how it can be resolved.
******
Please help.............



Private Sub CommandButton1_Click()
Dim objOpenSTAAD As Object
Dim supp_Scount As Long
Dim supp_nodes() As Long
Dim loadcase As Long
Dim loadcase1 As Long
Dim loadcase2 As Long
Dim Noloadcase As Long
Dim dReactionArray(0 To 10) As Double
Dim Scount As Long
Dim Scount1 As Long
Dim LCScount As Long
'Dim strstring
''strstring = "Ubound of arrdReactionArray is :" & UBound(arrdReactionArray)
'MsgBox strstring
' opening staad file
Set objOpenSTAAD = GetObject(, "StaadPrpenSTAAD")
' first load case
loadcase1 = Cells(4, 14).Value
' second load case
loadcase2 = Cells(5, 14).Value
' no of load cases
Noloadcase = Cells(5, 14).Value - Cells(4, 14).Value + 1
' no of supports from staad file
supp_count = objOpenSTAAD.Support.GetSupportcount
' defining no of support nodes
ReDim supp_nodes(0 To (supp_count - 1)) As Long
' get support nodes
objOpenSTAAD.Support.GetSupportNodes supp_nodes
' 1 to no of supports in staad
' for each load case
' load case
For Scount = 1 To supp_count


Cells(3 + (Scount - 1) * Noloadcase, 1).Value = supp_nodes(Scount - 1)

' For LCScount = 1 To Noloadcase

For loadcase = loadcase1 To loadcase2
Cells(3 + (Scount - 1) * Noloadcase + loadcase - 100, 2).Value = loadcase

For Scount1 = 1 To 6
objOpenSTAAD.Output.GetSupportReactions supp_nodes(Scount - 1 + loadcase - loadcase1), loadcase, dReactionArray
Cells(3 + (Scount - 1) * Noloadcase + loadcase - 100, Scount1 + 2).Value = dReactionArray(Scount1 - 1)
Next
Next
Next
'Next

Set objOpenSTAAD = Nothing
End Sub

 

[mrmmickle1]

When you run the code what line does it error on? It should be highlighted in yellow....

Most of the time this error means exactly what the description is saying.... It can't find the range your looking for.

A good way to find out if something is "off" or out of place is to add a watch by using the watch window or two use the immediate window to query variables. I see in one of your lines of code you have scount -1....

So for example if scount becomes negative it would give a subscript out of range error because..... there are no negative cells...

Cells(1,-1) etc...

 

[aatel]

It is yellowed on the line
objOpenSTAAD.Output.GetSupportReactions supp_nodes(Scount - 1 + loadcase - loadcase1), loadcase, dReactionArray
second last line in the loop.

It works perfectly for small no of quantities. But when I increase the number of quantities to be imported, it shows the error.
Hope it is clear...

 

[aatel]

I assume the reason may be

" The subscript may be larger or smaller than the range of possible subscripts, or the array may not have dimensions assigned at this point in the application. Check the declaration of the array to verify its upper and lower bounds. Use the UBound and LBound functions to condition array accesses if you're working with arrays that are redimensioned. If the index is specified as a variable, check the spelling of the variable name."

as found in internet. But I dont know how to fix it. I tried my best. but couldn't.

 

[aatel]

Thanks but Scount is always positive. it is the number of supports. it is 1 to any number.
the program works perfectly if I test with small data to be imported.
I am really confused.

 

[mrmmickle1]

When your code hits that line and is highlighted yellow:

When the error window pops up:

Click Debug

Use Key Combination Ctrl + G

Type this into the immediate window:

[COLOR=#333333]?Scount - 1 + loadcase - loadcase1[/COLOR]

and press enter.

What value do you get?


You may also want to try...

?Scount

and

?loadcase

and

?loadcase1

Depending on your values. This may shed some light on your issue.

Does scount ever exceed supp_count - 1?

Also if you organize/indent your code like this its easier to read:

[COLOR=#0000ff]For[/COLOR] Scount = 1 [COLOR=#0000ff]To [/COLOR]supp_count
    Cells(3 + (Scount - 1) * Noloadcase, 1).Value = supp_nodes(Scount - 1)
   [COLOR=#0000ff] For[/COLOR] loadcase = loadcase1 [COLOR=#0000ff]To[/COLOR] loadcase2
        Cells(3 + (Scount - 1) * Noloadcase + loadcase - 100, 2).Value = loadcase
       [COLOR=#0000ff] For[/COLOR] Scount1 = 1 [COLOR=#0000ff]To[/COLOR] 6
            objOpenSTAAD.Output.GetSupportReactions supp_nodes(Scount - 1 + loadcase - loadcase1), loadcase, dReactionArray
            Cells(3 + (Scount - 1) * Noloadcase + loadcase - 100, Scount1 + 2).Value = dReactionArray(Scount1 - 1)
    [COLOR=#0000ff]    Next[/COLOR] Scount1
   [COLOR=#0000ff] Next[/COLOR] loadcase
[COLOR=#0000ff]Next[/COLOR] Scount

 

[aatel]

they are 44
1
144
100 respectively
all positive numbers

 

[aatel]

I arranged as you said.
Scount never exceed Supp_count , but it would become equal to supp_count.
as Scount = 1 to supp_count

Supp_count - no of total supports in a structure
Scount - from one support to the last support

It works perfectly if I have 100 supports, but one loadcase
or two loadcase

when I have three loadcase, then the program imports values correctly for some say 145 lines, then it shows error
if I have say 4 load cases, then the program imports values correctly for some 120 lines and shows error

really confused

I think the program array has no capacity? array has limited stack? I dont know

 

[aatel]

can you please tell me how to increase the array size

 

[mrmmickle1]

What about the variables:

dreactionarray

Or

dReactionArray(Scount1 - 1)

If those values aren't something unexpected I'm not quite sure what would be going wrong.

Not familiar with this type of Structural Analysis File Type or its object library either.

Maybe another board member can jump in and provide some assistance.

To increase Array size use a variable in the Redim statement. SOmething like this in a loop.... :

    [COLOR=#0000ff]ReDim Preserve[/COLOR] YourArr(1 To [COLOR=#0000ff]ArrCounter[/COLOR])

      YourArr([COLOR=#0000ff]UBound[/COLOR](YourArr)) = YOURVARIABLE[COLOR=#008000] 'Assign value to new array component[/COLOR]

    ArrCounter = ArrCounter + 1