CODEWITHSUNDEEP
excelvba
Skyblue
Skyblue

Reputation: 35

Can a workbook be declared globally in Excel VBA?

Im opening a Workbook through Workbook.GetOpenFilename and setting it to Test_wkbk = ActiveWorkbook.Name in a subroutine and I want to make Test_wkbk available globally to the other subroutines in other modules as well. How Do i do that?

Upvotes: 0

Views: 65

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33165

Check to see if the private variable has a value. If it does, use it. If it doesn't use GetOpenFileName

Private wLocations As Workbook

Public Property Get Locations() As Workbook

    Dim sFile As String

    If wLocations Is Nothing Then

        sFile = Application.GetOpenFilename("*.xlsx,*.xlsx")

        On Error Resume Next
            Set wLocations = Workbooks(Dir(sFile))
            If wLocations Is Nothing Then
                Set wLocations = Workbooks.Open(sFile)
            End If
        On Error GoTo 0
    End If

    Set Locations = wLocations

End Property

Upvotes: 4

Related Questions