3

I have an excel file with 2 buttons which access two different modules. Can we access a variable of a module in another module after running the program which calls that module?

My modules look like this enter image description here

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...

barrowc
  • 10,444
  • 1
  • 40
  • 53
Abdul Shiyas
  • 401
  • 3
  • 9
  • 30
  • are you asking something like [this](http://stackoverflow.com/questions/2804327/call-a-subroutine-from-a-different-module-in-vba) – 0m3r Jul 02 '15 at 01:23
  • i dont want to call the whole module, Just one variable in a module – Abdul Shiyas Jul 02 '15 at 01:30
  • Is the variable declared at module level or within a Sub/Function? – barrowc Jul 02 '15 at 01:47
  • within a sub.......... – Abdul Shiyas Jul 02 '15 at 01:50
  • 7
    A variable declared within a Sub is only accessible within that Sub. Also, if not declared as Static, the lifetime of the variable ends as soon as the Sub finishes running. You could move the variable to module level and declare it as Public or you could return the variable (by changing the Sub to a Function or using a ByRef parameter) – barrowc Jul 02 '15 at 02:03
  • 1
    An answer providing a way to do this surely will be highly rated and accepted. – Cris May 21 '18 at 21:13

1 Answers1

5

In 1st module - declare Directory as Public at top of module outside of any Sub/Function. It's now available to every module in this project:

Public Directory As String

Sub Directory_Path()

    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

In 2nd module, just use the name Directory wherever you need it. Example:

MsgBox "The directory path is " & Directory
barrowc
  • 10,444
  • 1
  • 40
  • 53
  • 1
    Shouldn't Directory_Path be called inside the second module to set the value for Directory first? If so, how do you do it? – norman123123 Feb 08 '20 at 20:57
  • Something does have to set the value of `Directory_Path` as otherwise it will just be an empty string when it's accessed in the second module. The question mentions having two buttons to access two modules but it's not clear what the purpose of each button would be. In the more general case, I would prefer to use a function to return the required value rather than having a public variable – barrowc Feb 08 '20 at 22:41