Search code examples
vbaexcelsubroutine

Excel Not Responding When Passing Objects To Subroutine

This macro runs fine on most of our work computers, but NOT all of our work computers. Everyone is running Excel 2013 and on newer computers with plenty of RAM (4-8 GB).

Here is the subroutine that is being called:

Public Sub EmailApprovalP1(GeneralSection As Object, ItemSection As
   Object, VendorSection As Object, MarketSection As Object, Part1Section
   As Object, wbLog As Workbook, wbPCD As Workbook, resubmission As Boolean)

And, here is the Call to that subroutine:

Call EmailApprovalP1(GeneralSection, ItemSection, VendorSection, 
   MarketSection, Part1Section, wbLog, wbPCD, resubmission)

When the VBA code reads the Call above, Excel starts to not respond and either will recover or, in some cases, close completely :(.

I've search other questions and answer here, but can't find anything similar to my experience.

Any ideas on how to fix this OR suggestions on how to work around this would be greatly appreciated.

Solution

  • I was able to get this to work on computer's that were having issues by explicitly stating 'ByRef' in the parameters of the Subroutine. 

    Public Sub EmailApprovalP1(ByRef GeneralSection As Object, ByRef 
   ItemSection As Object, ByRef VendorSection As Object, ByRef 
   MarketSection As Object, ByRef Part1Section As Object, ByRef wbLog As 
   Workbook, ByRef wbPCD As Workbook, ByRef resubmission As Boolean)

    I'm extremely surprised that this fixed the issue, since passing 'ByRef' is implicit if left out.