variant array subscript error

[jeff rogers]

What have I missed? THANKS for looking!

strTempAry is a 1000 cell list in column H in a worksheet within the workbook.

Private Sub Workbook_BeforePrint(Cancel As Boolean) 
    Dim strTempAry As Variant 
    Dim x As Integer 
     
    strTempAry = Worksheets("PrintList").Range("H1:H1000") 
    For Each wk In Worksheets 
        wk.Calculate 
        wk.Activate 
        x = wk.Range("I3:I3") 
         
        Select  Case wk.Name 
        Case "Compliance Program" 
        Case "Printable Program" 
        Case "PrintList" 
        Case Else 
             'everything works to here, value of x really is equal to 1,
             'used a  msgbox to confirm
             'next line generates subscript  error 9
            If strTempAry(x) = 1 Then PrintOut 
        End Select 
    Next 
End Sub 

[rbrhodes]

Hi,
Note on code tags opening is [ code ] and close is [ /code ] (no spaces)... Good try tho! As for your question, I'm not sure but you're trying to build an array? Try this code:

Option Base 1 
 
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
    Dim strTempAry(1000) 
    Dim x, r, c, i As Integer 
     'This loads the array
    r = 1 
    c = 8 
    For i = 1 To 1000 
        strTempAry(i) =  Worksheets("PrintList"). Cells(r, c).Value 
        r = r + 1 
    Next 
     
    For Each wk In Worksheets 
        wk.Calculate 
        wk.Activate 
        x = wk.Range("I3:I3") 
         
         'I presume the select  case is used for something...
        Select Case wk.Name 
        Case "Compliance Program" 
        Case "Printable Program" 
        Case "PrintList" 
        End Select 
         
         'This jumps if x = nothing, otherwise you'll get a Subscript  error here.
        If x = "" Then Goto phred 
        If strTempAry(x) = 1 Then PrintOut 
phred: 
    Next 
End Sub 

Cheers,

dr

[jeff rogers]

Hi rbrhodes!

You are correct, I am loading an array with a Print/NoPrint code (1 or 0). The program finds each worksheet in the workbook, calculates it, makes it active (to adjust for merged cell row height), examines the unique worksheet number (which ascends numerically from 1 starting with the first worksheet, all 'never print' worksheets contain "" in this worksheet cell), looks that number up in the array, and if it is a '1' it prints the active worksheet.

Thanks! Solved some problems but still have just one ...

Problems solved:
1.) Array loads up fine now. THANKS!
2.) Using your code the Case statements were not needed.
3.) Evidently the wk.Activate is a 'one-time' command so that by the time PrintOut is executed the program no longer knows which sheet is active so I made it 'wk.PrintOut' and that works fine.

Problem that remains:
Last record processing - The last worksheet is specified to Not Print but it prints anyway - I must be missing some kind of special last record code, huh?

Can you help identify it?

[norie]

Jeff

The array you created was a 1000 by 1 array.

So when you tried this you were missing the second dimension.

strTempAry(x) 

[Richie(UK)]

Hi Jeff,

If you are one record out then make sure that you copied the first line of dr's code - Option Base 1. The default lower bound for arrays is 0, the use of the Option Base 1 statement indicates that they should start at 1. (I haven't checked the code - just speculating )

dr,

Do you realise that in the line :

Dim x, r, c, i As Integer

only i is declared as an Integer (the others are Variant types). Each variable must be specifically assigned the desired type.

[jeff rogers]

Hi Norie, Thanks! So....

The following would have worked for the 'As Variant' option of loading the array in the original code?

Dim c As Integer 
c = 8 
strTempAry(x,c) = 1 Then ... 

[jeff rogers]

Hi Richie (UK)!

Thanks for the info. No I did not know that.

regarding the Option Base 1, yes, it is in the code.

Regarding the PrintOut problem - I think that XL 'batches' print output while the procedure is running, and releases all of it when the procedure is complete.

The Print icon was selected to activate the workbook macro, but nothing was manually selected to print. It appears that XL feels that it needs to print something because the icon was selected so it prints the last worksheet that it touched.

I tried setting Cancel to TRUE after the Next statement but only succeeded in canceling all the PrintOut output. (which leads me to believe that output is batched while the procedure is running)

So I guess I am looking for a way to supress XL automatic printing of the last worksheet touched.

Does this help clarify?

Thanks again,

Jeff

[rbrhodes]

Hi,

dr,

Do you realise that in the line :

Dim x, r, c, i As Integer

only i is declared as an Integer (the others are Variant types). Each variable must be specifically assigned the desired type.

Richie, thanks for the reminder! I saw a dim statement like this and presumed it was a 'collective' Dim. I did try it and realized it was not, but I guess I forgot (or got lazy?)!

Jeff,

Regarding the 'LAST' sheet printing when it should not: do you mean the last run of the If..Then..Print is failing the "is 1" test but printing anyway? Is that right?

Cheers,

dr

[jeff rogers]

Hi dr,

Yes, correct.

The last worksheet is set to '0' so that it should not print but it does anyway.
This issue has shown up before (printing the last worksheet touched even when not selected to print) but was not an issue until now.

jeff

[rbrhodes]

Hi Jeff,

Thats very strange because if x = 0 then there should be an error!

This is because I used Option Base 1. So strTempAry(x), with x = zero is an error.

To correct that substitute this line in:

If x = "" Or x = 0 Then GoTo phred

This may correct the print problem as well...let me know as it it worked for me!

Cheers,

dr