Event macro error w/ 2nd wb open

[just_jon]

I have a workbook with an event macro attached to the first sheet. Works great until a second - any second - workbook is opened up. Then I get "Run-time error '9': Subscript out of range". The code is attached below, and any help will be greatly appreciated. I've lurked this board a while and found a bunch of nuggets. Hope I can return the favor someday.
Thank you - just_jon in huntsville

Event macro:<pre>

Private Sub Worksheet_Calculate()
' Turn off event handling while macro is running..
Application.EnableEvents = False
Dim Counter, Lotsize, First, Last, i, j, Pass As Integer
Dim b, s, Curve, Midpt As Variant
Dim Avgunit_02, Avgunit_03, Avgunit_04, Avgunit_05, Avgunit_06 As Variant
Dim Funitval_02, Funitval_03, Funitval_04, Funitval_05, Funitval_06 As Variant
Dim Unival_02, Unival_03, Unival_04, Unival_05, Unival_06 As Variant
' Set any existing curved labor to 0...
Worksheets("Instant Order").Cells(22, 5) = 0
Worksheets("Instant Order").Cells(22, 6) = 0
Worksheets("Instant Order").Cells(22, 7) = 0
Worksheets("Instant Order").Cells(22, = 0
Worksheets("Instant Order").Cells(22, 9) = 0
' If instant order qty< 31 terminate execution...
If (Worksheets("Instant Order").Cells(5, 5)<= 30) Then
GoTo Not_Abend
End If
' Negotiated starting values, assuming 1300 Eq. units already built...
Curve = 89.1
First = 1300
Last = 1300
' Assign Avg Unit labor values from baseline data...
Avgunit_02 = Worksheets("Instant Order").Cells(20, 5)
Avgunit_03 = Worksheets("Instant Order").Cells(20, 6)
Avgunit_04 = Worksheets("Instant Order").Cells(20, 7)
Avgunit_05 = Worksheets("Instant Order").Cells(20,
Avgunit_06 = Worksheets("Instant Order").Cells(20, 9)
' Begin calculations...
Begin_Calc:
Pass = Pass + 1
b = (Application.WorksheetFunction.Ln(Curve / 100)) / Application.WorksheetFunction.Ln(2)
Lotsize = (Last - First) + 1
' Calculate midpoint...
If Lotsize > 1000 Then
Midpt = (((Last + 0.5) ^ (1 + b) - (First - 0.5)) ^ (1 + b) / Lotsize / (1 + b)) ^ (1 / b)
ElseIf First = Last Then
Midpt = First
Else
s = 0
For i = First To Last
s = s + (i ^ b)
Next i
Midpt = (s / Lotsize) ^ (1 / b)
End If
' Calculate First Unit Values...
If (Pass = 1) Then
Funitval_02 = Avgunit_02 / Midpt ^ b
Funitval_03 = Avgunit_03 / Midpt ^ b
Funitval_04 = Avgunit_04 / Midpt ^ b
Funitval_05 = Avgunit_05 / Midpt ^ b
Funitval_06 = Avgunit_06 / Midpt ^ b
End If
' Get instant order fab sequence...
First = 1300 + 1
Last = 1300 + (Worksheets("Instant Order").Cells(5, 5) - 0)
Unival_02 = Funitval_02 * Midpt ^ b
Unival_03 = Funitval_03 * Midpt ^ b
Unival_04 = Funitval_04 * Midpt ^ b
Unival_05 = Funitval_05 * Midpt ^ b
Unival_06 = Funitval_06 * Midpt ^ b
If (Pass = 1) Then
GoTo Begin_Calc
Else
' Re-seed curved values here...
Worksheets("Instant Order").Cells(22, 5) = Unival_02
Worksheets("Instant Order").Cells(22, 6) = Unival_03
Worksheets("Instant Order").Cells(22, 7) = Unival_04
Worksheets("Instant Order").Cells(22, = Unival_05
Worksheets("Instant Order").Cells(22, 9) = Unival_06
GoTo Not_Abend
End If
Not_Abend:
' Restore event handling...
Application.EnableEvents = True
End Sub</pre>
This message was edited by just_jon on 2003-01-06 13:55

 

[Jim North]

Hello just_jon,

... and welcome to the board!

On what line are you getting the error??

 

[just_jon]

The 10th line [below] is highlighted when I go into debug.

Worksheets("Instant Order").Cells(22, 5) = 0

Of course, there is no "Instant Order" sheet in wb #2. I'm running w/ calculation enabled, so what I'm thinking is the opening of the 2nd wb is forcing calculation of both open workbooks - but what I can't understand is why the app blows. Again, many thanks.

just_jon in huntsville

 

[Jim North]

Our of curiosity... what is the name of the workbook this macro is in?

 

[Jim North]

I just found this on Google:

"Hi Andrei,

An Excel recalculation event recalculates all open workbooks rather than
just the active one.

Charles Williams
______________________
Decision Models
The Excel Calculation Site
http://www.DecisionModels.com"

 

[just_jon]

wb #1 is TADS_PNVS-11.xls, wb #2 can be anything.

just_jon in huntsville

 

[Mudface]

You might try referencing the worksheet "Instant Order" explicitly each time you use it in your code eg use: -

ThisWorkbook.Worksheets("Instant Order").Cells(22, 5) = 0

rather than

Worksheets("Instant Order").Cells(22, 5) = 0

Might help.

 

[just_jon]

I'm pretty sure what's going on, now that I think about it: wb #1 works as long as it is the current workbook; once a second wb is opened, auto-calc kicks in for both wb's but the current wb is wb #2, the one "on top". And as wb #2 does not contain a sheet of the specified name, the event macro in wb #1 abends at the first line calling that sheet, in my case the 10th line including comments. Switching calculation to manual pretty much defeats the whole idea of event macros, so I suppose I'm looking for a vb stmt that would limit the macro to A] events on sheet1, and B] only if the workbook containing the macro is the active workbook. So far, I can't think of a solution. Sorry to take up so much of your time.

just_jon in huntsville

 

[just_jon]

Mudface - Thank you! Works just dandy. I'm still learning Excel/VB, but I'll be here often and if I can ever be of service I'll put my two cents in. If I could, I'd grep beer fridge for you...

just_jon in huntsville