How to Carry a Variable Through From a Sub into Another Sub

[killpaddy]

How do I pass a variable from one sub to another, I thought the 'ByRef' definition was meant to do this, but it hasn't worked.

The CONVERTPATH Sub converts a UNC path to a local drive number and vice versa when it is called from another sub.

How do you get the variable 'Path' to be passed between subs, and out of interest does the method work with a function?

Sub CONVERT_PATH()


CONVERTPATH "\\network\folder\folder\file.xlsm", True


MsgBox (Path)    < 'Path' is blank in this sub, the value is not carried over


End Sub

---------------------------------------------------------------------------------------------------------

Sub CONVERTPATH(ByRef Path As String, UNC As Boolean)


Dim PathDrive As String
Dim ConvDrive As String


Dim NetDrive1 As String
Dim NetDrive2 As String


Dim TWO As Integer
Dim ConvPath As String


If UNC = True Then
    PathDrive = Replace(left(Path, InStr(1, Replace(Path, "\", "?", 1, 3), "\") - 1), "?", "\")
Else
    PathDrive = left(Path, InStr(1, Path, "\") - 1)
End If


    Set objNetwork = CreateObject("WScript.Network")
    Set objdrives = objNetwork.EnumNetworkDrives
    strDrives = "Network drive Mappings:" & Chr(13)
    NonFound = 0
    For i = 0 To objdrives.Count - 1 Step 2
        NetDrive1 = objdrives.Item(i)
        NetDrive2 = objdrives.Item(i + 1)
        If NetDrive1 = PathDrive Then
            ConvDrive = NetDrive2
            Exit For
        ElseIf NetDrive2 = PathDrive Then
            ConvDrive = NetDrive1
            Exit For
        Else
            NonFound = NonFound + 1
        End If
    Next
    
TWO = 2
If (objdrives.Count / TWO) < NonFound + 1 Then MsgBox ("Drive in file path not recognised")
ConvPath = ConvDrive & Right(Path, Len(Path) - Len(PathDrive))


Path = ConvPath


End Sub

 

[RoryA]

You didn't pass a variable when calling the first routine, you passed a literal value. Try this:

Sub CONVERT_PATH()
Dim strPath as string
strPath = "\\network\folder\folder\file.xlsm"
CONVERTPATH strPath, True


MsgBox strPath


End Sub

 

[killpaddy]

Ah, yes, that would make sense. Does this work the same for a 'function'

 

[RoryA]

Yes - ByRef works the same no matter what type of routine. But with a function you would normally pass it a value and use the return value of the function rather than needing to pass ByRef and let the function change the input - unless you effectively want to return two values.

 

[RoryA]

Function example:

Sub CONVERT_PATH()
Dim strPath As String

strPath = CONVERTPATH("\\network\folder\folder\file.xlsm", True)

MsgBox strPath

End Sub

Function CONVERTPATH(ByVal Path As String, ByVal UNC As Boolean) As String

Dim PathDrive As String
Dim ConvDrive As String

Dim NetDrive1 As String
Dim NetDrive2 As String

Dim TWO As Integer
Dim ConvPath As String

If UNC Then
    PathDrive = Replace(Left(Path, InStr(1, Replace(Path, "\", "?", 1, 3), "\") - 1), "?", "\")
Else
    PathDrive = Left(Path, InStr(1, Path, "\") - 1)
End If


    Set objNetwork = CreateObject("WScript.Network")
    Set objdrives = objNetwork.EnumNetworkDrives
    strDrives = "Network drive Mappings:" & Chr(13)
    NonFound = 0
    For i = 0 To objdrives.Count - 1 Step 2
        NetDrive1 = objdrives.Item(i)
        NetDrive2 = objdrives.Item(i + 1)
        If NetDrive1 = PathDrive Then
            ConvDrive = NetDrive2
            Exit For
        ElseIf NetDrive2 = PathDrive Then
            ConvDrive = NetDrive1
            Exit For
        Else
            NonFound = NonFound + 1
        End If
    Next
    
TWO = 2
If (objdrives.Count / TWO) < NonFound + 1 Then MsgBox ("Drive in file path not recognised")
ConvPath = ConvDrive & Right(Path, Len(Path) - Len(PathDrive))

CONVERTPATH = ConvPath

End Function