Excel vba variant array reusable, must be erased?

Question

I have a technical question:

My issue: I create a

Dim arrTemp as Variant 
Dim wbSource as workbook
Dim wbTarget as workbook

because I need to export multiple ranges from multiple worksheets (not fixed range) to another workbook. My code looks like:

' Worksheet 1
arrTemp = wbSource(1).Range("A1:B2").value
wbTarget(1).Range("A1:B2").value = arrTemp
If Not(IsArrayEmpty(arrTemp)) Then Erase arrTemp
' Worksheet 2
arrTemp = wbSource(2).Range("A1:B2").value
wbTarget(2).Range("A1:B2").value = arrTemp
If Not(IsArrayEmpty(arrTemp)) Then Erase arrTemp
' Worksheet 3
arrTemp = wbSource(3).Range("A1:B2").value
wbTarget(3).Range("A1:B2").value = arrTemp
If Not(IsArrayEmpty(arrTemp)) Then Erase arrTemp

(worksheet can be empty in the first place, that's why empty arr handler) (worksheets can contain int/str/double/... and the size is not that big to define specific arr type)

My question is: Does it make sense to erase the array every time? or It will be overwritten automatically?

I did a test to check the properties of the array (Lbound & UBound) before and after defining the array with a new range. I can see that It automatically Redim the array. Does it means that I only need to clear it in the end of the procedure?

Or it is a good practice to clear it in between?

Last but not least, do you see any problem in my code? Better way to perform this task?

Many thanks in advance!

Edit: Bear in mind The code is not correct for this task, no need to transfer to an array!

To be honest I don't see any benefit in using the variable at all rather than just assigning the value of one range to the other each time. However, there is no need to erase the array. — Rory
– Rory, Commented Jun 30, 2016 at 15:45
Funny. I have been programming VBA full time for years and did not even know there was this Erase command. — Patrick Honorez
– Patrick Honorez, Commented Jun 30, 2016 at 15:49
@Rory. I extracted this way from the "Power Programming 2013" the array-transfer-method to copy ranges (well kind of overkill, I am not working with hundred of thousands rows). Would your advise be to simple send from range to range?. — Charlie
– Charlie, Commented Jun 30, 2016 at 15:51
Yes in this case. If you needed to process the data in between, the array would make sense, but not for this. Also, using Value2 should be slightly faster than Value. — Rory
– Rory, Commented Jun 30, 2016 at 15:53

Charlie · Accepted Answer · 2016-06-30 16:01:21Z

0

In Short (thanks to rory!):

No need to erase the array every time in between. Only before leaving the procedure.

answered Jun 30, 2016 at 16:01

Charlie

1012 silver badges16 bronze badges

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

2 Comments

Patrick Honorez Over a year ago

Not even before leaving. It's destroyed automatically. However it seems to be good practice to set object variables to Nothing before exiting procedure.

Charlie Over a year ago

@iDevlop oh.. I was trying to follow the good practices code... I like to study high-level code and normally I don't see people clearing anything... until I figure out it I will stick to the good practices, but I guess the garbage collector does pretty much everything.. thanks for your insights, much appreciated!

Collectives™ on Stack Overflow

Excel vba variant array reusable, must be erased?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related