0

1st module..

Public Sub Directory_Path()
Dim Directory As String
    Directory = InputBox("Enter the Directory path that contains folders ""This Quarter"",""Last Quarter"",""Second_Last_Quarter"".")
    If Right(Directory, 1) = "\" Then
    Directory = Left(Directory, Len(Directory) - 1)
    End If
End Sub

I called the the first module in 2nd module using Public Sub Directory_Path() . I want Directory variable in first module to be used as a variable in 2nd module... Help me. I miss something... If this question is repeated, please answer me and I will delete this post.

Community
  • 1
  • 1
Abdul Shiyas
  • 401
  • 3
  • 9
  • 30
  • possible duplicate of [Access a variable of a module in another module after running the program which calls that module?](http://stackoverflow.com/questions/31174123/access-a-variable-of-a-module-in-another-module-after-running-the-program-which) – barrowc Jul 02 '15 at 03:50

1 Answers1

1

The most obvious solution is to just make it a function...

Public Function Directory_Path() As sting
    Dim Directory As String
    Directory = InputBox("Enter the Directory path that contains folders " & _
                """This Quarter"",""Last Quarter"",""Second_Last_Quarter"".")
    If Right(Directory, 1) = "\" Then
        Directory_Path = Left(Directory, Len(Directory) - 1)
    Else
        Directory_Path = vbNullString
    End If
End Function

...and then call the function:

Debug.Print Directory_Path

Note that instead of requiring the user to type the path, you can use the FileDialog instead:

Public Function Directory_Path() As String
    Dim prompt As FileDialog
    Set prompt = Application.FileDialog(msoFileDialogFolderPicker)
    With prompt
        .Title = "Select Directory path that contains folders " & _
                 """This Quarter"",""Last Quarter"",""Second_Last_Quarter""."
        .AllowMultiSelect = False
        If .Show <> 0 Then Directory_Path = .SelectedItems(1)
    End With
End Function
Comintern
  • 21,855
  • 5
  • 33
  • 80
  • I want to use that path as a string in another module. How can we assign that Debug.Print Directory_Path to some variable – Abdul Shiyas Jul 02 '15 at 04:37
  • `variable = Directory_Path` – Comintern Jul 02 '15 at 04:38
  • You can also use `public variable`. In your first module declare a `Public variable` and assign value to it from that module and call that variable from the function of another module. It will work. – R.Katnaan Jul 02 '15 at 05:19