2

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!

Community
  • 1
  • 1
Charlie
  • 101
  • 2
  • 16
  • 1
    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 Jun 30 '16 at 15:45
  • Funny. I have been programming VBA full time for years and did not even know there was this Erase command. – iDevlop Jun 30 '16 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 Jun 30 '16 at 15:51
  • @iDevlop, Ok definitely I am doing something wrong :D – Charlie Jun 30 '16 at 15:53
  • 1
    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 Jun 30 '16 at 15:53
  • Ok that makes a lot of sense. It is only a copy/paste so I'll remove the array and now I know for sure that I don't need to erase every time :D Awesome! Many thanks for your answers! – Charlie Jun 30 '16 at 15:56

1 Answers1

0

In Short (thanks to rory!):

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

Charlie
  • 101
  • 2
  • 16
  • 1
    Not even before leaving. It's destroyed automatically. However it seems to be good practice to `set` **object variables** to `Nothing` before exiting procedure. – iDevlop Jun 30 '16 at 20:17
  • @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! – Charlie Jul 01 '16 at 10:14