CODEWITHSUNDEEP
vbaexcel
Part_Time_Nerd
Part_Time_Nerd

Reputation: 1014

Calling VBA subroutine does not work when it is called from inside of another subroutine

I am using VBA to try and call a series of sub routines from the main sub routine. When I combine all of the subroutines with code similar to the following, I get #N/A for the cells that the formula is suppose to be ignoring.

Sub Main()

'Turn off autocalculation
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
'*********************************************************
'A bunch of other code
'*********************************************************

Call Sub_Routine1
Call Sub_Routine2
Call Sub_Routine3
Call Sub_Routine4
Call Sub_Routine5
Call Sub_Routine6
Call Sub_Routine7

'This is the sub routine that is not working correctly
Call Material_Formulas

'Turn back on the autocalculation function
    Application.Calculation = xlAutomatic

'*********************************************************
'A bunch of other code
'*********************************************************  
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Combined_Call

When I remove the Material_Formulas subroutine from the Main sub routine and run it separately using the following script, it executes as it is suppose to and looks like the image bellow.

Private Sub Material_Formulas()

'Turn on manual calculation
Application.Calculation = xlCalculationManual
Dim lRow As Integer
Dim tiesMaterial As String
Dim result As String
lRow = Sheets("Material").Range("A2").End(xlDown).Row
lCol = Sheets("Material").Range("A2").End(xlToRight).Column

'Starts the count at column CU
endCount = lCol - 1    
    For c = 99 To endCount
        For r = 3 To lRow        
        tiesMaterial = Cells(r, 87).Value

        'Looks to see if the cells starting at CU2 contains a number and then iterates through each cell in row 3 to add a formula
        If tiesMaterial = "TIES MATERIAL" Then

            'Defines the unique ID and calendar year cells for the index-match-match function
            materialID = Sheets("Material").Cells(r, "CQ").Address(False, False)
            materialYear = Sheets("Material").Cells(2, c).Address(False, False)

            'Starting in cell CU3 it adds the formula =INDEX(BOM_Summary_Array,MATCH(CQ3,BOM_Summary_ID,0),MATCH(CU2,BOM_Summary_Head,0))
            Sheets("Material").Cells(r, c).Formula = "=INDEX(BOM_Summary_Array,MATCH(Material!" & materialID & ",BOM_Summary_ID,0),MATCH(Material!" & materialYear & ",BOM_Summary_Head,0))"                 
        End If

        Next r
    Next c     
'Turn on the auto calculation function
Application.Calculation = xlAutomatic
End Sub

Separate_Call

What am I doing wrong? How does it run fine when I manually and independently select it but it fails when I combine it with the other sub routines?

Upvotes: 0

Views: 2173

Answers (1)

Brandon Barney
Brandon Barney

Reputation: 2392

Before anything else you need to improve your code. I can pretty much guarantee that this is likely happening because of poorly written code. For example:

materialID = Sheets("Material").Cells(r, "CQ").Address(False, False)
materialYear = Sheets("Material").Cells(2, c).Address(False, False)

Note that materialID and materialYear are never declared. This means they are of the type Variant (you need to add Option Explicit to the top of your code modules for this exact reason). The funny thing about Variants is they, you guessed it, vary. MaterialID could be a string, an int, a long, a decimal, a date, an array, a range, etc. We can assume that the address of the range is going into materialID, but we can't be certain.

Additionally, notice the pesky Sheets("Material"). What this actually is saying is ActiveWorkboook.Sheets("Material")`. Qualify your references otherwise you have pretty much no clue what is actually happening.

What can easily be happening in the case of your code is that the address is correctly going into materialID as a string, but it is the address from another workbook with a sheet named "Material". Unlikely, but possible.

What we do know is quite likely is that the ActiveWorkbook is changing in some capacity, likely in Sub_Routine7 (sidenote here, you must descriptively name subroutines otherwise your code is nowhere near maintainability).

Good luck with this, but I highly suggest not bothering to try to debug the situation until you have qualified your ranges, declared all your variables, and added Option Explicit.

Upvotes: 1

Related Questions