Event macro error w/ 2nd wb open


Sep 3, 2002
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, :cool: = 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, :cool:
Avgunit_06 = Worksheets("Instant Order").Cells(20, 9)
' Begin calculations...
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
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
' 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, :cool: = Unival_05
Worksheets("Instant Order").Cells(22, 9) = Unival_06
GoTo Not_Abend
End If
' Restore event handling...
Application.EnableEvents = True
End Sub</pre>
This message was edited by just_jon on 2003-01-06 13:55

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
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
Upvote 0
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
Upvote 0
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.
Upvote 0
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
Upvote 0
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
Upvote 0

Forum statistics

Latest member

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