Access a variable of a module in another module after running the program which calls that module?

Question

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

i dont want to call the whole module, Just one variable in a module — Abdul Shiyas
– Abdul Shiyas, Commented Jul 2, 2015 at 1:30
Is the variable declared at module level or within a Sub/Function? — barrowc
– barrowc, Commented Jul 2, 2015 at 1:47
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
– barrowc, Commented Jul 2, 2015 at 2:03
An answer providing a way to do this surely will be highly rated and accepted. — Cris
– Cris, Commented May 21, 2018 at 21:13

barrowc · Accepted Answer · 2015-07-02 03:50:07Z

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

answered Jul 2, 2015 at 3:50

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

norman123123 Over a year ago

Shouldn't Directory_Path be called inside the second module to set the value for Directory first? If so, how do you do it?

barrowc Over a year ago

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

Collectives™ on Stack Overflow

Access a variable of a module in another module after running the program which calls that module?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related