Change Events with multiple instances of Excel

[efsaoge]

I am using the multithread tool by Tomasz Kacprowicz at http://analystcave.com/excel-vba-multithreading-tool/

Usually this tool works great but is failing for a new model in which a Sheet_Change event is used to automate the goal seek function. When the model is run with only 1 instance of Excel the code works fine, goal seek runs as it should. However when I try to run multiple instances in parallel I get a Run-time error '1004' reference is not valid. I can't share the workbook but the goal seek related code is provided below, error occurs on red line, however if I step through the loop the error will occur for all 4 goal seek calls.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C1:T65")) Is Nothing Then

Call Answer​

End If
End Sub

Sub Answer()


With Application
.MaxIterations = 400
.MaxChange = 0.000001
End With


Worksheets("HeatingSystem(Main)").Range("H86").GoalSeek _
Goal:=0.001, _
ChangingCell:=Worksheets("HeatingSystem(Main)").Range("C12")


Worksheets("HeatingSystem(Main)").Range("E106").GoalSeek _
Goal:=Worksheets("HeatingSystem(Main)").Range("E105"), _
ChangingCell:=Worksheets("HeatingSystem(Main)").Range("E107")

Worksheets("HeatingSystem(Main)").Range("H111").GoalSeek _
Goal:=0.1, _
ChangingCell:=Worksheets("HeatingSystem(Main)").Range("C36")

Worksheets("Insulation").Range("D31").GoalSeek _
Goal:=0.05, _
ChangingCell:=Worksheets("Insulation").Range("B8")

End Sub


I think the code gets confused when multiple instances are all triggering a sheet_change events. I tried to expand the range calls by adding various Application, workbook, and worksheet additions to the range call but nothing seems to work.

For example ThisWorkbook.Worksheets("HeatingSystem(Main)").Range("E106").GoalSeek _

Since the code runs fine when the parallel tool is not used I do not think there is a calculation error causing the problem.

Thanks,

 

[Jaafar Tribak]

Hi,

Does that mean each sperate instance of excel has its own copy of the workbook ? and if so, does that mean the workbook is opened as Read Only ?
Regards.

 

[efsaoge]

Hi,

Does that mean each sperate instance of excel has its own copy of the workbook ? and if so, does that mean the workbook is opened as Read Only ?
Regards.

Yes the parallel program creates multiple copies of the program and opens each in a new instance of Excel. The copies have unique filenames. The program worked before I added the sheet_change event to automate the goal seek function so it is not a read only issue.