Excel VBA global variables "lifetime"?

Question

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?

Maybe you could write to some file and re-read the values at appropriate time. — Basile Starynkevitch
– Basile Starynkevitch, Commented Oct 27, 2013 at 13:13
My code is too large and includes many other action, it'll only confuse everyone.. Did I manage to explain my goal? — user429400
– user429400, Commented Oct 27, 2013 at 13:22

to StackOverflow · Accepted Answer · 2013-10-27 13:52:47Z

4

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.

answered Oct 27, 2013 at 13:52

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

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

Comments

Anthony · Accepted Answer · 2013-10-28 03:03:02Z

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.

Daniel Bonetti · Accepted Answer · 2013-11-13 18:28:17Z

0

Perhaps these two functions can also help you:

SaveSetting
GetSetting

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

answered Nov 13, 2013 at 18:28

Daniel Bonetti

2,4462 gold badges27 silver badges39 bronze badges

Collectives™ on Stack Overflow

Excel VBA global variables "lifetime"?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related