CODEWITHSUNDEEP
arraysvbaexcelmacros
Runeaway3
Runeaway3

Reputation: 1449

Will variables persist upon termination of macros?

I am setting an excel/vba macro in which I want to append numbers to an array during a loop. However, I would like the array to persist from macro use to macro use (ie. if the array is filled with [1,3,5] when I run the macro on Tuesday, and the I run the macro again on Wednesday and append 8,9, and 10, then array needs to be [1,3,5,8,9,10]). Is this possible to do in VBA (even if the entire excel sheet is closed and the reopened later-though it is saved before the closure-)?

Upvotes: 0

Views: 53

Answers (1)

user6432984
user6432984

Reputation:

By declaring your Macros using the Static keyword then the Macro's variables will hold their values between function calls. But when you closing the workbook will erase these values.

Alternately you could save the array in the Workbook's Names collection.

enter image description here

Function getArrayFromName(name As String)
    Dim s As String
    On Error Resume Next
    s = ThisWorkbook.Names(name)
    s = Mid(s, 3, Len(s) - 3)
    On Error GoTo 0

    getArrayFromName = Split(s, ",")

End Function

Sub setArrayFromName(name As String, value As Variant)
    ThisWorkbook.Names.Add name, value, False
End Sub

Upvotes: 2

Related Questions