Can the array variable values persist when VBA macro finishes

Question

Can the array variable values persist when macro finishes? I have declared a global variable:

Option Explicit
Public Arr2D As Variant

Public Sub ReadDataToArr()
    Arr2D = [MyNamedRange].Value
End Sub

but as soon as the macro finishes then Arr2D becomes out of context and Arr2D gets empty. I would like to read data to all array variables as Workbook opens and use them on whatever occasion I want without declaring and filling them every time.

Update. Why do I think it is empty? Add a Watch for Arr2D variable.

When I am in debug mode of macro above (hitting F8) I see this:

When I come with F8 hits to the macro end then I see this:

Update again. I noticed something strange to me. When I run the above macro second time in debug mode (after initializing values, and after the variable got out of context), then immediately in the very first line of the macro, the variable becomes "filled" with values, and in the context. So maybe that is why disagreement in comments arouse.

Can you show the code to proof this "becomes out of context and Arr2D gets empty"? I disagree. minimal reproducible example might help. — Pᴇʜ
– Pᴇʜ, Commented Feb 12, 2019 at 14:45
Note that you watch in the context of Module1.ReadDataToArr so of course if you exit ReadDataToArr that context is invalid. But you just watch on the wrong context, the variable and its data persists. Proof: Add another sub and put Debug.Print Join(WorksheetFunction.Transpose(Arr2D), ",") into it then run first ReadDataToArr then the other sub. — Pᴇʜ
– Pᴇʜ, Commented Feb 12, 2019 at 15:28

Pᴇʜ · Accepted Answer · 2019-02-12 15:44:06Z

2

Option Explicit

Public test As Variant

Sub a()
    test = 5
End Sub

Sub b()
    MsgBox test
End Sub

First run a then run b it will output 5 as expected.

The variable test will persist until you run End or press the stop button in VBE.

Note that you should test in every procedure if your variable test was initialized.

for example

Option Explicit

Public Arr2D As Variant

Sub InitArr2D()
    Arr2D = [MyNamedRange].Value
End Sub

Sub ProcedureUsingArr2D()
    If Arr2D Is Nothing Then
        InitArr2D
    End If

    Debug.Print Join(WorksheetFunction.Transpose(Arr2D), ",")
End Sub

If you don't test your variable might be Nothing after a runtime error occured and you pressed End. This way it gets automatically re-initialized.

edited Feb 12, 2019 at 15:44

answered Feb 12, 2019 at 15:22

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

4 Comments

Przemyslaw Remin Over a year ago

When whatever macro, or trying to be precise, a sub finishes, does it release memory? I mean having initialized a huge array variable (having assigned values to it), is it kept in memory after sub finishes and is it somehow "reborn" or declared and broght to life when whatever new sub starts?

Pᴇʜ Over a year ago

It keeps Arr2D in memory if it is a global variable which it is. So if it is a huge amount of data think about that Excel has a 2GB memory limit. • See my additional edit to ensure the array is initialized. • You just didn't see the array after the sub finished because you were looking in the wrong context. You were looking in the sub's context instead of a global context. If you look at the global context you will always see it.

Przemyslaw Remin Over a year ago

How to release the memory if variable is not needed anymore? ReDim?

Pᴇʜ Over a year ago

Set Arr2D = Nothing

Collectives™ on Stack Overflow

Can the array variable values persist when VBA macro finishes

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related