SheetAdressing Problem Excell

[Gilske]

dear developers

I have a very strange problem
I use VB to adress a Excel Workbook.
And i use a sheets name to adress it
But it would only work with the first 7 sheets,
after that he would give "subscript out of range"
this is my code

Private Sub ArrayRes()
    Dim strLabonaaam As String
    
    'variabelen initialiseren
    i = 0
    q = 0
    j = 0
    'Enkel het aantal labo's moeten gedaan worden (geen extra werk)
    For i = 1 To intAantalLabos Step 1
        Set oSheet = oWorkbook.Worksheets(Labo(i, 2))
        oSheet.Activate
        For q = 7 To 50 Step 1
           'blablabla
        Next
        
    Next
End Sub

This is the one causing the troubles
Set oSheet = oWorkbook.Worksheets(Labo(i, 2))

 

[TonyJollans]

Hi Gilske,

There are all sorts of possible reasons but it is not possible to tell from the posted code. Your labo array and the count of elements in it, as well as your workbook object, are externally defined. What value does labo(i,2) have when the code fails? Is it a valid sheetname in the workbook? Is there anything in the "blablabla" which changes any variables? etc. etc. Try puttting a breakpoint in the code and watching what is happening.

Enjoy,
Tony

 

[Gilske]

I have to say that sometimes i forget to put that stuff in

But here's my second change

Labo(i,2) is an Array which contains String
The value of Labo when it fails is a valid sheetname.

Blablabla doesn't change any variables that use
or have anything to do with the sheetadressing.

The workbook and sheet are defined like this
Public oWorkbook As Excel.Workbook
Public oSheet As Excel.Worksheet

and the only thing i did before coming to this point of failure is
Set oXls = New Excel.Application
oXls.Visible = True
Set oWorkbook = oXls.Workbooks.Open(strBestand)
Set oSheet = oWorkbook.Worksheets(1)

and getting some values out of the second sheet

 

[TonyJollans]

Hi Gilske,

Based on what you say, it should work. Obviously, as it doesn't, one of the things you say must be false. As it works for the first seven times, the most likely explanation is that you do not actually have a valid sheet name in labo(8,2). Could you have an extra space (or some other typo) either in the sheet name or the array variable?

Enjoy,
Tony

 

[Gilske]

Sorry Tony
but i've checked all that
no typo's, no blancs in front or in the back for both of them
i've broke my head on it last week
Can't figure it out.

 

[RobBroekhuis]

If you change the value of your eighth array element to, say, the value of the second element (for which your code works fine), does the code run? Can you activate (or otherwise access) the worksheet from the immediate window (I'm not familiar with VB itself, but I figure it has an immediate window)?


Rob

 

[RoyVidar]

Hi!

I'm not an Excel VBA xpert, but have come across the "subscript out of range" error couple of times when I've tried to access an element outside the scope of an array. Just putting in couple of cents troubleshooting in addition to the others...

To completely rule out the possibility of a faulty array dimensioning, could you try using:

[tt]debug.print Labo(i, 2)[/tt]

in stead of addressing the worksheet. If this is OK then the array is not what causes the errormsg.

I'm also used to "ending" my for next loops with explicit reference to what variable increments (next i, next q...) Don't know if this has any relevance here.

I've come across a similar problem once in another program, that when reusing/instantiating the same object variable multiple times for different objects (well in my case recordsets), it bombed, even if I did "[tt]set object=nothing[/tt]" inbetween - could perhaps be worth a try to use direct references in stead of oSheet, just to test?

Else, I'm as stumped as you;-(

Roy-Vidar