Search code examples
excelscopesubroutinevba

Excel VBA: Error Calling Subroutine


I have a problem with the following code:

My main code is "autofill_DSR", the subroutine I am trying to call is "algorithm". "autofill_DSR" is in Module1, "algorithm" is in Module4. Before, I did not separate the two codes and I just had the bulk of whats in "algorithm" at the line where I write: Call Module4.algorithm, and the program did what I wanted it to.

After creating this subroutine, it only goes through one iteration of the code, however, because it performs the subroutine once but it either does not return or there is a problem with iteration in the for loop. I can't figure it out.

I use a "Sheet2" activation command because I am switching between sheets when I enter the subroutine, that could have something to do with it, could it be public/private variable declarations?

Any help is appreciated, thanks.


Sub autofill_DSR()

' Variable Declarations:

Dim x_count As Integer
Dim n As Integer
Dim item_a As String
Dim item_b As String
Dim test_string As String

' Variable Initializations:

test_string = "NN"
x_count = 0
Process_Control_NumRows = 16

' Main Data Transfer Code:

Sheets(Array("Sheet1", "Sheet2")).Select        'Create Array of all Sheets

' Process Control Sheet:

    For n = 0 To (Process_Control_NumRows - 1)  'Cycle 16 times for each
                                                'item in process controls tab
        Sheets("Sheet2").Activate       'Choose specific sheet
        Range("D1").Select              'Choose specific cell

        Call Module4.algorithm      'Call on subroutine (see algorithm code)

    Next n                  'increment index to account for offset

End Sub

Sub algorithm()

        'If an "x" or "X" is marked in the "Yes" column,
        'at descending cells down the column offset by the for loop index, n

        If (ActiveCell.Offset(n, 0) = "x" Or ActiveCell.Offset(n, 0) = "X") Then

            item_a = ActiveCell.Offset(n, -3).Value     ' Store Letter value
            item_b = ActiveCell.Offset(n, -2).Value     ' Store number value
            x_count = x_count + 1                       ' increment the total x count

            If (x_count > 5) Then

                Sheets("Sheet3").Activate               ' Switch over to Sheet 1
                Range("A1").Select                      ' Choose "Item" column, first cell
                ActiveCell.Offset((x_count - 6), 0).Value = (item_a & item_b)

                'Insert cocatenated value of item_a and item_b
                '(for example "A" & "1" = "A1")
                'at the cells under the "Item" column, indexed by x_count

            Else

                Sheets("Sheet1").Activate
                Range("A1").Select
                ActiveCell.Offset((x_count - 1), 0).Value = (item_a & item_b)

            End If

        End If

End Sub

Solution

  • change Sub algorithm() to Sub algorithm(n as long) and then call it using

    Call Module4.algorithm(n)