VBA Variable, Object & Memory

There is a question about variable in vba:

1. Is variable with huge string or huge amount of data (like array) or object will use alot of memory?
2. If the procedure completly executed, are they (all local variables and objects) also completely released from the memory?

Another sample, I have some code:

Sub Routine()
   'Step 1
   Call CreateSheet
   Call CopySomeRange
End Sub

Sub CreateSheet()
    Dim ws As Worksheet
    Set ws = Thisworkbook.Add
    With ws
        .Name = "Sample"
        '... some activity here...
    End With
End Sub

Sub CopySomeRange()
    Dim ws As Worksheet
    Set ws = Thisworkbook.Worksheet("Sample")
    With ws
        .Range("A1:A100").Copy .Range("B1:B100") '>>> Sometimes error
    End With
End Sub

Sometimes my action in copy range get error. When I inspect the code, It says ws is empty (not set as "Sample" worksheets).

Both procedure CreateSheet and CopySomeRange are used ws as object. Is that any connection?

a) ws is a local variable in both of your routines. They are completely independent.

b) Local variables are destroyed when the code of a routine finishes (with the exception of static variables). In case of ws, this is only the reference pointing to the worksheet, not the worksheet itself.

c) Objects that are create with the new keyword are destroyed when there is no more reference to it. So if you create an object in a routine and assign it to a local variable, then copy the object reference into a variable that survives the end of the subroutine, eg a global variable, as a result of a function, into a variable that is passed as parameter, the object is not destroyed. The VBA garbage collection is so good that you hardly find any information about it, it simply works.

d) In your code, you want create a new Excel worksheet. This worksheet is handled by Excel, not VBA, VBA cannot destroy it (it can only call the delete-method to ask Excel to delete and therefore destroy it).

e) Objects and Array uses more or less the amount of memory you would expect. An array of 1000*1000 Doubles takes 8mb (1 million times 8 bytes) of data, plus a very small overhead. Strings are a little bit more complicated, but as a rule of thumb, you don't have to take care about memory consumption nowadays.

For more about memory, have a look to https://nolongerset.com/memory-management-in-vba/

Looking at your code, there are (at least) 2 problems. Both raise already a compile time error, so your code is not executed at all.

In Sub CreateSheet, the line to create a new sheet would be

Set ws = ThisWorkbook.Worksheets.Add

In Sub CopySomeRange, the line to assign a worksheet reference to a variable is

Set ws = ThisWorkbook.Worksheets("Sample")

Assuming that those errors are corrected, I don't see any reason that could cause ws to be Nothing.

Q1. Is variable with huge string or huge amount of data (like array) or object will use alot of memory?

A. Yes, but that shouldn't be a problem with modern computers

Q2. If the procedure completly executed, are they (all local variables and objects) also completely released from the memory?

It doesn't matter whether the procedure is completely executed or not, the variables will at some stage go out of scope - this reference explains it better Understanding scope and visibility - Microsoft Learn

Q: Both procedure CreateSheet and CopySomeRange are used ws as object. Is that any connection?

No - there is no connection. See the link above.

> "Sometimes my action in copy range get error. "

I don't see how that is possible as your code will not run at all. See the comment from @VBasic2008. It has multiple syntax errors