2 files 2 macros nothing but debug mode.

[howardsternisbatman]

Hello,

I hope this is straightforward enough.

I have 2 spreadsheets (files) that have nothing to do with each other. Both have macros running. However the macros keep bumping in to each other and go in to debug mode. If I only have one of the spreadsheets open everything runs fine, as soon as I open the other BAM! debug mode. It's as if the macro starts to run on the other spreadsheet

What have I missed?

thanks

 

[JLGWhiz]

Hello,

I hope this is straightforward enough.

I have 2 spreadsheets (files) that have nothing to do with each other. Both have macros running. However the macros keep bumping in to each other and go in to debug mode. If I only have one of the spreadsheets open everything runs fine, as soon as I open the other BAM! debug mode. It's as if the macro starts to run on the other spreadsheet

What have I missed?

thanks

I am not a technical expert, but from what I can gather, the VBA application is a single instance although you may have two or more macro enabled workbooks open simultaneously. In view of that, If macro code is written in a fashion that allows default to the active file, it may not be the intended file and would probably error or return erroneous results. Without seeing the code and watching it actually run, I am only guessing. But I think I am pretty close.

 

[howardsternisbatman]

I am not a technical expert, but from what I can gather, the VBA application is a single instance although you may have two or more macro enabled workbooks open simultaneously. In view of that, If macro code is written in a fashion that allows default to the active file, it may not be the intended file and would probably error or return erroneous results. Without seeing the code and watching it actually run, I am only guessing. But I think I am pretty close.

thanks for the reply.

Here is the macro for "Master newcases.xls":

Sub RefreshAllFromSalesforce()
   Dim addin As Office.COMAddIn
   Dim automationObject As Object
  
   For Each addin In Application.COMAddIns
       If (addin.Description = "Enabler for Excel" Or addin.Description = "Enabler4Excel") Then
           Set automationObject = addin.Object
           Debug.Print ("Enabler Lives!")
       End If
   Next addin
   automationObject.Refresh (True)
      
      
End Sub

Sub Dashing()
'
' Dashing Macro
'
'
Application.DisplayAlerts = False
Application.OnTime Now + TimeValue("00:30:00"), "Dashing"
Sheets("FrontEnd").Select
Range("B244:B257").Copy Range("C244:C257")
Range("J199").Value = Range("H199").Value
Sheets("Sheet1").Select
    Range("A2:E65536").Select
    Selection.ClearContents
Sheets("Sheet4").Select
    Range("A2:E65536").Select
    Selection.ClearContents
Call RefreshAllFromSalesforce
Sheets("Team TTA").Select
    Range("A2:E65536").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A2:E65536").Select
       ActiveSheet.Paste
Sheets("Team TTA this week").Select
    Range("A2:E65536").Select
    Selection.Copy
    Sheets("Sheet4").Select
    Range("A2:E65536").Select
       ActiveSheet.Paste
Sheets("Team TTA last week").Select
    Range("A2:E65536").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2:E65536").Select
       ActiveSheet.Paste
    Sheets("FrontEnd").Select
    ActiveWorkbook.RefreshAll
    Cells(4, 9).Value = Format(Now, "dd/mm/yyyy")
Cells(4, 10).Value = Format(Now, "hh:mm ampm")
    ' ActiveWorkbook.SaveAs Filename:="Master newcases.xls"
    ActiveWorkbook.Save
    'ActiveWorkbook.SaveAs Filename:="Master newcases.xls", FileFormat:=xlExcel8
      
    
End Sub

And the macro for "amerunassigned.xlsm"

Sub WWunassigned()

Application.OnTime Now + TimeValue("00:10:00"), "WWunassigned"
   Dim addin As Office.COMAddIn
   Dim automationObject As Object
  
   For Each addin In Application.COMAddIns
       If (addin.Description = "Enabler for Excel" Or addin.Description = "Enabler4Excel") Then
           Set automationObject = addin.Object
           Debug.Print ("Enabler Lives!")
       End If
   Next addin
   automationObject.Refresh (True)
      
      
Cells(2, 8).Value = Format(Now, "dd/mm/yyyy")
Cells(3, 8).Value = Format(Now, "hh:mm ampm")

Application.DisplayAlerts = False
ActiveWorkbook.Save


ActiveWorkbook.SaveAs Filename:="C:\deaddrop\unassignedreport\amerunassigned.htm"

End Sub

 

[JLGWhiz]

Yes, I believe the procedures would definitely conflict with each other if ran simultaneously. Both the Dashing and WWunassigned macros lack specific reference to the workbook that the code should execute on. 'ActiveWorkbook' does not define the host file for the VBA since any other workbook could be the active workbook at the time the line of code executes. Also, the Sheets, Range and Cells references are all unqualified with their parent objects. While the codes would work individually in their host workbooks based on default values, they are not written in the proper syntax for merged operations. My suggestion at this point is to not run them simultaneously.