Passing variable value from Private Sub to Module

I am trying to pass a variable called "Filter" from a Private Sub to a Module but keeping coming up with a zero value in the Module. I tried to make the variable Global but that didn't seem to work. Any suggestions for a solution is appreciated. Thanks.

Private Sub Code as follows:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim i               As Long
Dim LastRow         As Long
Dim Rep             As String
Dim Color           As Long
Dim Filter          As Long
    
    Filter = 1

Global Variables as follows:

Global SortByColumn As Long
Global SortOrder As Long
Global Filter As Long

Module Code as follows:

Sub MoodysSort()

Dim i As Long
Dim lngSortIndex As Long
Dim EndRow As Long

    If Filter = 0 Then
        'do something
    Else
        'do something else
    End If

Hi,

You are defining Filter in

Private Sub Worksheet_BeforeDoubleClick"

So when you say

Filter = 1

the value is assigned to the local variable and not the global one, and you will not get it in the module.

Just remove

Dim Filter          As Long

from

Private Sub Worksheet_BeforeDoubleClick

and hopefully that will resolve the problem.

Regards.

Personally I'd get rid of the Global variables unless you really need them (ie. can justify a reason for having them other than needing the values in more that one function), and just pass them from one function to the next when you need to.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim i               As Long
Dim LastRow         As Long
Dim Rep             As String
Dim Color           As Long

Dim SortByColumn As Long
Dim SortOrder As Long
Dim Filter As Long    

Filter = 1
SortByColumn = 12
SortOrder = 2

call MoodysSort(Filter, SortByColumn, SortOrder)

Option Explicit

Sub MoodysSort(filter As Long, sortByCol As Long, sortOrder As Long)
Dim i As Long
Dim lngSortIndex As Long
Dim EndRow As Long

    If filter = 0 Then
        'do something
    Else
        'do something else
    End