1
votes

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!

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
Funny. I have been programming VBA full time for years and did not even know there was this Erase command. - Patrick Honorez
@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
@iDevlop, Ok definitely I am doing something wrong :D - Charlie
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

1 Answers

0
votes

In Short (thanks to rory!):

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