Why is the macro window opening when I try to pass an array from one sub to another?

[Silverjman]

OK, from all I've read passing an array from one sub to another is easy, but when I try to do it here the VBA Macro window pops open and my code stops.


I have tried to read everything else out there that tries to address this but no amount of defining or deleting of `()` helps.

Sub Costs__Max_Legal_Physical_Possession_Period(arrCosts__Physical_Possession(), arrCosts__Legal_Possession())
        arrQuarters = Range("Quarters_1to40")
        arrPhysical_Possession_Expenses_From_Quarter = Range("Costs.Physical_Possession_Expenses_From_Quarter")
        arrPhysical_Possession_Expenses_To_Quarter = Range("Costs.Physical_Possession_Expenses_To_Quarter")
        
        arrLegal_Possession_Expenses_From_Quarter = Range("Costs.Legal_Possession_Expenses_From_Quarter")
        arrLegal_Possession_Expenses_To_Quarter = Range("Costs.Legal_Possession_Expenses_To_Quarter")
        
        ReDim arrCosts__Max_Legal_Possession(1 To UBound(arrLegal_Possession_Expenses_To_Quarter, 1), 1 To UBound(arrQuarters, 2))
        
        For I = LBound(arrLegal_Possession_Expenses_To_Quarter, 1) To UBound(arrLegal_Possession_Expenses_To_Quarter, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrCosts__Max_Legal_Possession(I, J) = WorksheetFunction.Max(arrCosts__Physical_Possession(I, J), arrCosts__Legal_Possession(I, J))
            Next
        Next
    End Sub

FULL CODE

Sub Costs__Legal_Possession()    
        Dim arrQuarters, arrLegal_Possession_Expenses_From_Quarter, arrLegal_Possession_Expenses_To_Quarter, arrCosts__Legal_Possession, I, J
        
        arrQuarters = Range("Quarters_1to40")
        arrLegal_Possession_Expenses_From_Quarter = Range("Costs.Legal_Possession_Expenses_From_Quarter")
        arrLegal_Possession_Expenses_To_Quarter = Range("Costs.Legal_Possession_Expenses_To_Quarter")
        
        ReDim arrCosts__Legal_Possession(1 To UBound(arrLegal_Possession_Expenses_To_Quarter, 1), 1 To UBound(arrQuarters, 2))
        
        For I = LBound(arrLegal_Possession_Expenses_To_Quarter, 1) To UBound(arrLegal_Possession_Expenses_To_Quarter, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrCosts__Legal_Possession(I, J) = (arrQuarters(1, J) >= arrLegal_Possession_Expenses_From_Quarter(I, 1)) * (arrQuarters(1, J) <= arrLegal_Possession_Expenses_To_Quarter(I, 1)) * 1
            Next
        Next
    End Sub
    
    Sub Costs__Physical_Possession()
        Dim arrQuarters, arrPhysical_Possession_Expenses_From_Quarter, arrPhysical_Possession_Expenses_To_Quarter, arrCosts__Physical_Possession, I, J
        
        arrQuarters = Range("Quarters_1to40")
        arrPhysical_Possession_Expenses_From_Quarter = Range("Costs.Physical_Possession_Expenses_From_Quarter")
        arrPhysical_Possession_Expenses_To_Quarter = Range("Costs.Physical_Possession_Expenses_To_Quarter")
        
        ReDim arrCosts__Physical_Possession(1 To UBound(arrPhysical_Possession_Expenses_To_Quarter, 1), 1 To UBound(arrQuarters, 2))
        
        For I = LBound(arrPhysical_Possession_Expenses_To_Quarter, 1) To UBound(arrPhysical_Possession_Expenses_To_Quarter, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrCosts__Physical_Possession(I, J) = (arrQuarters(1, J) >= arrPhysical_Possession_Expenses_From_Quarter(I, 1)) * (arrQuarters(1, J) <= arrPhysical_Possession_Expenses_To_Quarter(I, 1)) * 1
            Next
        Next
    End Sub
    
    Sub Costs__Max_Legal_Physical_Possession_Period(arrCosts__Physical_Possession(), arrCosts__Legal_Possession())
        arrQuarters = Range("Quarters_1to40")
        arrPhysical_Possession_Expenses_From_Quarter = Range("Costs.Physical_Possession_Expenses_From_Quarter")
        arrPhysical_Possession_Expenses_To_Quarter = Range("Costs.Physical_Possession_Expenses_To_Quarter")
        
        arrLegal_Possession_Expenses_From_Quarter = Range("Costs.Legal_Possession_Expenses_From_Quarter")
        arrLegal_Possession_Expenses_To_Quarter = Range("Costs.Legal_Possession_Expenses_To_Quarter")
        
        ReDim arrCosts__Max_Legal_Possession(1 To UBound(arrLegal_Possession_Expenses_To_Quarter, 1), 1 To UBound(arrQuarters, 2))
        
        For I = LBound(arrLegal_Possession_Expenses_To_Quarter, 1) To UBound(arrLegal_Possession_Expenses_To_Quarter, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                arrCosts__Max_Legal_Possession(I, J) = WorksheetFunction.Max(arrCosts__Physical_Possession(I, J), arrCosts__Legal_Possession(I, J))
            Next
        Next
    End Sub

 

[rlv01]

This may help

http://www.cpearson.com/excel/passingandreturningarrays.htm

 

[Silverjman]

Mate, that is not helpful at all and it takes my question off the "zero replies" list. I had already read that as it's the first result from google.

I've decided to just run this as one big Sub() and it works great.

 

[rlv01]

Ouch. I see you have put me in my place for trying to help and coming up short. The "mate" was a nice condescending touch. Glad you found a solution to your problem.

 

[Silverjman]

I’m sorry, I was quite cranky yesterday. My reply should have been more civil.


The lifespan of attention that a question has on any site seems so low, and I feel like I’ve had numerous queries regulated to the dust bin lately and have consequently gotten too touchy.


Sorry for bringing negativity to an otherwise very positive forum.


These chaps’ suggestions weren’t what I was looking for, but maybe they will help someone else.


https://stackoverflow.com/questions...to-anot?noredirect=1#comment96221134_54712568

 

[rlv01]

No worries. I've been known to have a few cranky days myself.