Offset.Resize [VBA]

[vijay.vizzu]

Hi..all,


I am not so familar with VBA, now i am learning VBA in chandoo's VBA Classes. I understand how to use activecell with offset, but in somewhere in my example i find the code which was used activcell with resize along with offset.


So can you please explain, how to use offset.resize?

 

[NARAYANK991]

Hi Vijay ,


The two are quite different.


Offset in VBA has only two parameters , unlike the OFFSET function which you use in an Excel worksheet ; these are the RowOffset and the ColumnOffset.


If we are talking of the Range.Offset property , then suppose you have a Range object , say B2:N20 ; in VBA , you would have the following statement for this :

[pre]

Dim temp As Range
Set temp = ThisWorkbook.Worksheets("Sheet1").Range("B2:N20")
Now , if you activate the sheet Sheet1 , you can use statements like :


Msgbox temp.address

temp.Select


Now , suppose you want to refer to the cell K17 ; obviously , you can refer to it as Range("K17") , but if you want to use temp as the reference point and refer to K17 , then you have to calculate the row and column numbers of K17 , in relation to B2.


The row number of 17 is offset by 15 from the row number of 2 ; the column number of column K , which is 11 , is offset by 9 from the column number of column B which is 2.


Suppose you use a statement such as :

[pre][code]MsgBox temp.Offset(15, 9).Address
you will see : $K$17:$W$35


Things have worked out the way we expected , but not entirely , since temp as a range has been offset , to give a new range.


If we want a single cell K17 , we can use two methods :

MsgBox temp.Offset(15, 9).Cells(1, 1).Address

[/pre]
or

MsgBox temp.Offset(15, 9).Resize(1, 1).Address[/code][/pre]
The Resize function has resized the multi-celled range , $K$17:$W$35 , to a single-celled range , since we specified a row size of 1 and a column size of 1.


Try changing the above parameters to different values , and see what you get.


Narayan

 

[Debraj]

Hi Vijay..


Te understand both the function.. I hope this example is perfect for you..


http://chandoo.org/forums/topic/conditional-formula#post-67398


Please try to understand the sheet in the attachment.. (Specially Sheet 1), A marvellous work of Offset & Resize..


Regards,

Deb

 

[vijay.vizzu]

Thank you so much Narayan & Debraj


I am trying to understand the concept.