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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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
Top