How to carry a variable's value to another routine?

[rizzo93]

How do I carry a variable forward so that its value can be used in another sub routine?

I have several routines that are identical except for the column they're affecting. Just before each routine comes to an end, I assign a value to a variable (the same variable exists in all routines) and I then call another routine.

Dim x as Integer
x = [COLOR=#ff0000]21[/COLOR]
Call ClearAll

Another routine will look like this:

Dim x as Integer
x = [COLOR=#ff0000]25[/COLOR]
Call ClearAll

This value is the column number I am focusing on and fills in the missing piece of the following code:

Sub ClearAll()
 Sheet2.Range("B10:X194").AutoFilter Field:=[COLOR=#ff0000]x[/COLOR]
End Sub

 

[Nine Zero]

perhaps make the variable public

at the very top of your editor before the first sub routine simply put

Public x as Integer

and you no longer need to Dim x in your sub routines just simply call the x = 21 and x = 25 then run the other

so something like this

Public x as Integer

Sub One()
x = 21
Call ClearAll
End Sub

Sub Two()
x = 25
Call ClearAll
End Sub

 

[Scott Huish]

In this case, I would write your sub to accept an argument then call the sub with that:

Sub test1()
Call ClearAll(21)
End Sub

Sub test2()
Call ClearAll(25)
End Sub

Sub ClearAll(x As Long)
Sheet2.Range("B10:X194").AutoFilter Field:=x
End Sub

 

[Nine Zero]

Yup also overlooked the parameter thing.... my fault you could also do it like the two previous people have said it, which would make more sense lol

 

[rizzo93]

Thanks, Scott and Nine Zero. It works.


I had no idea it could be used for that. Very cool!

 

[rizzo93]

Scott or Nine Zero,

I've had a few successes with setting the arguments in the parameters, but as I'm fine-tuning my code, I've found myself hitting an obstacle.

The issue is that I'm getting an error when I select any value from my pull-down menu which initiates a new filter, "AutoFilter method of Range class failed". When hitting Debug, it highlights the line you see below in red.

Sub ReportList_Change()
    If Worksheets("Calculations").Range("J49").Value = "1 - No Remaining Hours" Then
        Call NewFilter(Worksheets("Calculations").Range("J48").Value, 22, "1 - No Remaining Hours")
    Else
        If Worksheets("Calculations").Range("J49").Value = "2 - Wrong Date Format" Then
            Call NewFilter(Worksheets("Calculations").Range("J48").Value, 23, "2 - Wrong Date Format")
(more similar code)
End Sub

Sub NewFilter(x As Integer, y As Integer, k As String)
[COLOR=#ff0000]    ActiveSheet.Range("$B$10:$X$194").AutoFilter Field:=x[/COLOR]
    Worksheets("Calculations").Range("J48").Value = y
    ActiveSheet.Range("$B$10:$X$194").AutoFilter Field:=y, Criteria1:="TRUE"
    Worksheets("Calculations").Range("J49").Value = k
    Range("A12").Select
End Sub

Here's how the code is supposed to work:

1. Select a value from the pull-down menu
2. If that value equals "1 - No Remaining Hours" then pass these values to Sub NewFilter
   • x = Worksheets("Calculations").Range("J48").Value
   • y = 22
   • k = "1 - No Remaining Hours"
3. Filter on "TRUE" in the y column (the 22 column in this case)

If I replace "x' with Worksheets("Calculations").Range("J48").Value, then the code works, but I don't understand why it won't accept x as the variable.


Any suggestions?

 

[rizzo93]

Nevermind. I found the problem. In short, I realized "$B$10:$X$194" was not a large enough range, therefore, the column I was referencing was out of scope.