Excel VBA global variables “lifetime”?

Question

Sorry about the non descriptive Title, I just didn't know how to describe my goal. I'm new at VBA and didn't yet understand how things really work.

I've written a function which gets a directory from the user, and displays data from the first file in the directory. Now, I want to add a "next" button. When the "next" button is pressed, my code should display data from the next file in the directory.

I tried to use global variables but they seem to get initialized each time the button is pressed. What is the best way to achieve my goal? Do I have to use the spreadsheet as memory and write and read everything from there? Or does Excel VBA have some other "live memory" mechanism?

Thanks, Li

Answer 1

Perhaps these two functions can also help you:

SaveSetting
GetSetting

as showed here: http://www.j-walk.com/ss/excel/tips/tip60.htm

Answer 2

Globals will not normally be reinitialized when you click a button. They will be reinitialized if you recompile your VBA project. Therefore, while debugging, you may see a global being reinitialized.

You can use the spreadsheet as memory. One way to do this is to have a worksheet whose Visibility property you set to xlSheetVeryHidden (you can do this from the VBA project). This worksheet won't be visible to users, so your VBA application can use it to store data.

Answer 3

This could be approached many ways, as with any problem I guess!

You could break the problem up into two subroutines:

1) Retrieve all the file names in the selected directory and display the first file's data

2) If it's not the last file, get the next file's data and display it

You could use a global variable to store the filenames and an index to remember where you are up to in the collection of filenames.

Global filenames As Collection
Global fileIndex As Integer

Public Sub GetFilenames()
    Dim selectedDirectory As String
    Dim currentFile As String

    selectedDirectory = "selected\directory\"
    currentFile = Dir$(selectedDirectory)

    Set filenames = New Collection

    While currentFile <> ""
        filenames.Add selectedDirectory & currentFile
        currentFile = Dir$()
    Wend

    ' Make sure there were files
    If filenames.Count >= 1 Then
        fileIndex = 1

        ' Call a method to display data
        DisplayData(filenames(fileIndex))
    Else
        ' No files
    End If
End Sub

Public Sub GetNextFile()
    ' Make sure we have a filenames object
    If Not filenames Is Nothing Then
        If fileIndex < filenames.Count Then
            fileIndex = fileIndex + 1

            ' Call the display method again
            DisplayData(filenames(fileIndex))
        Else
            ' Decide what to do after reaching the final file
        End If


    Else
        ' No filenames
    End If    
End Sub

I didn't include the DisplayData procedure as I'm not sure what type of files you're grabbing or what you are doing with them but if it were say excel files it could be something like:

Public Function DisplayData(filename As String)
    Dim displayWb As Workbook

    Set displayWb = Workbooks.Open(filename)

    ' Do things with displayWb
End Function

You could then set the macro of the button to "GetNextFile" and it will cycle through the files after each click. As for the lifetime of global variables, they only reinitialize when the VBA project is reset or when they are specifically initialized through a procedure or the immediate window.