About VB resize property

[plainme]

Please see the complete code below. I have two questions:
1. What does the bold part do?
Ex. Resize(UBound(x, 1), UBound(x, 2))
2. Why = x?
Ex. CurrWkbk.Worksheets("Data").Range("C8").Offset(0, (3 * i - 3)).Resize(UBound(x, 1), UBound(x, 2)) = x

Thanks!

Sub CopyPasteXY()
Application.ScreenUpdating = False
Dim WkbkName As String
Dim RemoteWkbk As Excel.Workbook, CurrWkbk As Excel.Workbook
Dim i As Long
Set CurrWkbk = ActiveWorkbook

i = 1
While Len(Range("FileList").Offset(i, 0)) > 0
WkbkName = Range("FileList").Offset(i, 0)

'Open workbook
Set RemoteWkbk = Workbooks.Open(WkbkName)

' Copy contents
x = RemoteWkbk.Worksheets("General Information").Range("B9:B12").Value
y = RemoteWkbk.Worksheets("Section 1)").Range("B8:D20").Value
' Paste contents
CurrWkbk.Worksheets("Data").Range("C8").Offset(0, (3 * i - 3)).Resize(UBound(x, 1), UBound(x, 2)) = x
CurrWkbk.Worksheets("Data").Range("C13").Offset(0, (3 * i - 3)).Resize(UBound(y, 1), UBound(y, 2)) = y


RemoteWkbk.Close False

i = i + 1
Wend
Application.ScreenUpdating = False
End Sub

 

[Rasm]

You can use any name in place of x - as long as you diminsion it as a variant. Your x is an array when you make it equal a range - so now the resize makes the new location fit that array diminsion. In your code - add a breakpoint - simply click the gray narrow area to the left of the code - that will make the code line go marron (now you have break point) (if you clcik again it goes away) - also add a local watch (Select View on ribbon - then click 'locals window') - then you can view the values of all your variables - when the code pauses at the breakpoint - just press F5 to continue the code execution.

 

[plainme]

Thanks! Further questions in the below.

>You can use any name in place of x - as long as you diminsion it as a variant.
--I understand this. but what is difference between "x=..." in Copy contents and "....=x"? I usually use "x=..." to diminsion a variant.

' Copy contents
x = RemoteWkbk.Worksheets("General Information").Range("B9:B12").Value

' Paste contents
CurrWkbk.Worksheets("Data").Range("C8").Offset(0, (3 * i - 3)).Resize(UBound(x, 1), UBound(x, 2)) = x


>In your code - add a breakpoint - simply click the gray narrow area to the left of the code - that will make the code line go marron (now you have break point) (if you clcik again it goes away)
--What do you mean "the gray narrow area". I am in old version of Excel. Maybe you are in newer version.

__________________

 

[Rasm]

Thanks! Further questions in the below.

>You can use any name in place of x - as long as you diminsion it as a variant.
--I understand this. but what is difference between "x=..." in Copy contents and "....=x"? I usually use "x=..." to diminsion a variant.

' Copy contents
x = RemoteWkbk.Worksheets("General Information").Range("B9:B12").Value

' Paste contents
CurrWkbk.Worksheets("Data").Range("C8").Offset(0, (3 * i - 3)).Resize(UBound(x, 1), UBound(x, 2)) = x


>In your code - add a breakpoint - simply click the gray narrow area to the left of the code - that will make the code line go marron (now you have break point) (if you clcik again it goes away)
--What do you mean "the gray narrow area". I am in old version of Excel. Maybe you are in newer version.

__________________

ok here we go.

Dim x as variant ' That is how you diminsion a variable - you should always do this before you do the x=something. You can in the very first code line do this Option Explicit - that forces you to diminsion a variable before you use it - it is great as it catches any spelling mistakes - also it carries the lower & upper case - that way it is spelled always the same way inthe code.


When x=something (you now do the equvalent of 'copy')

when something = x (you now do the equvalent of paste)


I hope I answered your question

 

[plainme]

Thanks!