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

aatel

New Member
Joined
Jun 19, 2015
Messages
8
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(, "StaadPro_OpenSTAAD")
' 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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
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...
 
Last edited:

aatel

New Member
Joined
Jun 19, 2015
Messages
8
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

New Member
Joined
Jun 19, 2015
Messages
8
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

New Member
Joined
Jun 19, 2015
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
May 11, 2012
Messages
2,456
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:

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

and press enter.

What value do you get?


You may also want to try...

Code:
?Scount

and

Code:
?loadcase

and

Code:
?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:

Code:
[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
 
Last edited:

aatel

New Member
Joined
Jun 19, 2015
Messages
8

ADVERTISEMENT

they are 44
1
144
100 respectively
all positive numbers
 

aatel

New Member
Joined
Jun 19, 2015
Messages
8
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
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
What about the variables:

Code:
dreactionarray

Or

Code:
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.... :

Code:
    [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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,098
Messages
5,599,720
Members
414,329
Latest member
Rich920

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top