Event macro error w/ 2nd wb open

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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...
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, :cool: = 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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
Hello just_jon,

... and welcome to the board!

On what line are you getting the error??
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791

ADVERTISEMENT

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

Legend
Joined
Sep 3, 2002
Messages
10,473
wb #1 is TADS_PNVS-11.xls, wb #2 can be anything.

just_jon in huntsville
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339

ADVERTISEMENT

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

Legend
Joined
Sep 3, 2002
Messages
10,473
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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
 

Forum statistics

Threads
1,144,148
Messages
5,722,779
Members
422,457
Latest member
Mrmuskins

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