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

killpaddy

Board Regular
Joined
Jul 31, 2012
Messages
52
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?

Code:
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You didn't pass a variable when calling the first routine, you passed a literal value. Try this:
Code:
Sub CONVERT_PATH()
Dim strPath as string
strPath = "\\network\folder\folder\file.xlsm"
CONVERTPATH strPath, True


MsgBox strPath


End Sub
 
Upvote 0
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.
 
Upvote 0
Function example:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top