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

Question

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

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

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?

When you say "fails" do you mean it does nothing or it throws an error ?? — Gary's Student
– Gary's Student, Commented Jun 21, 2017 at 17:50
What is the formula in the cell, is it correct? if not what should the correct formula be? — Raunak Thomas
– Raunak Thomas, Commented Jun 21, 2017 at 17:53
Are the years across row 2 actual dates formatted as yyyy or integers from 2012 to 2021? — user4039065
– user4039065, Commented Jun 21, 2017 at 17:57
The cells that have #N/A should not be ignored. In those cells the parameter that is suppose to initiate the formula (TIES MATERIAL ) is missing so those rows should be skipped or ignored. They would then have the original value. The second image from above is what it should look like. In both cases they are numbers and not formatted as dates. — Part_Time_Nerd
– Part_Time_Nerd, Commented Jun 21, 2017 at 17:58

Brandon Barney · Accepted Answer · 2017-06-21 18:34:07Z

1

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.

answered Jun 21, 2017 at 18:34

Brandon Barney

2,3921 gold badge11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Part_Time_Nerd Over a year ago

Thanks @Brandon! Sorry for the misunderstanding the numbered subroutines were not their actual names they were for pseudo code illustration purposes. I eventually got it to work by adding a reference to the active workbook page that the material formulas are referencing.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related