VBA Range.Insert Method ( Excel OOP Syntax Error )

I have now a Demo ( accompanying code given at end.. )...... Initially the main Code bits and Lines summarised. ( The code is best stepped through in debug F8 mode whilst viewing the Spreadsheet

..........
_................................


Rem 1)
Sets some background color in a Test Range,

Using Excel 2007

Row\Col	A	B	C	D	E	F
9
10
11
12
13
14

Inserting

_............................

80 Let rng.Interior.Color = RGB(255, 0, 0) ' Paints a small Area, referenced by rng, Red and
90 Let rng.Value = rng.Address ' writes in the Address of that referenced Range object

Row\Col	A	B	C	D	E	F
9
10				$D$10:$D$12
11				$D$10:$D$12
12				$D$10:$D$12
13
14

¬_ .........................

100 rng.Insert xlShiftToRight ‘ then “does” the “rng” . Insert “thing” in the current conventional way , _ ( shifting to get a space by spreading / shifting all the appropriate cells to the right
( ' I create a New Virgin Range Object of Size equal to Range Object, rng, at the position in the Sheet of the range Object rng. In order to do this I must make a space. I do this by spreading/ shifting those cells necessary to the right. Consequently my rng is now also in a different place, and some cells are lost ( "slip off" ) the Worksheet. ).......
Having done all that.........

110 Let rng.Interior.Color = RGB(255, 0, 0) ‘ Repaints with rng the same color and
120 Let rng.Value = rng.Address ‘ again puts the address of rng in it , which is now different as the change in the demo Test range shows.
( While we are at it, we put some values in the New Virgin Range by putting some values in the Range found by offsetting back to the left by a “distance” equal to rng “width” , ( - was as noted rng serves as a Template to give the width ( and “depth” ) of our new Range and so necerssarily is equal to the shift done to rng. Hence we “shift back” with:
130 Let rng.Offset(0, (-1 * rng.Columns.Count)).Value = "MiVrginRnge"

Row\Col	A	B	C	D	E	F	G
9
10				MiVrginRnge	$E$10:$E$12
11				MiVrginRnge	$E$10:$E$12
12				MiVrginRnge	$E$10:$E$12
13
14

_...........................

At line
150 rng. Offset(0, (-1 * rng.Columns.Count)).Delete xlShiftToLeft ‘ a corresponding line to “undo” the above is done. ( I have not bothered to change the values showing now the incorrect address )
I suggest the .Delete Method is more appropriate in its current “Range.Delete Method” description, as effectively it does something ( “kills”) the Range Object to which it applies.

Row\Col	A	B	C	D	E	F
9
10				$E$10:$E$12
11				$E$10:$E$12
12				$E$10:$E$12
13
14

_.............................................................................
_........
_........................

Lines from 220 do a similar demo for the case of shifting down
220 Let rng.Interior.Color = 255 'Paint rng red in original position
230 Let rng.Value = rng.Address ' Write in Original Address Values

Row\Col	A	B	C	D	E	F
9
10				$D$10:$D$12
11				$D$10:$D$12
12				$D$10:$D$12
13
14

_..............................

240 rng.Insert xlShiftDown ‘ Does the Insert thing
' Make a New range At Position and With Size of rng , ( so using rng as a Template / Pattern ) , spread / shift cells appropriately down to make space, ( and presumably cells "drop off" the bottom of the Worksheet )
Having done that............

250 Let rng.Interior.Color = RGB(255, 0, 0) '"Re paint" in red rng in its new position ( No change )
260 Let rng.Value = rng.Address 'Write in new Address of rng as it is in a new position
While we are at it...
270 Let rng.Offset((-1 * rng.Rows.Count)).Value = "MiVrginRnge" 'Write some values in the new range

Row\Col	A	B	C	D	E	F
9
10				MiVrginRnge
11				MiVrginRnge
12				MiVrginRnge
13				$D$13:$D$15
14				$D$13:$D$15
15				$D$13:$D$15
16
17

_..............................

Finally as with the first demo we “undo” by “chopping out ( Killing” ) the New Range ( and again i just did not bother to rewrite in the Address Value which now is incorrect for rng which has actually “slipped back up” now to its original position a ( Address $D$10:$D$13)

Row\Col	A	B	C	D	E	F
9
10				$D$13:$D$15
11				$D$13:$D$15
12				$D$13:$D$15
13
14

_..................................

Here the Code :
http://www.excelforum.com/showthread...53#post4371553

' Code For Thread   "VBA Range.Insert Method ( Excel OOP Syntax Error )"
'
'
Option Explicit '
Sub SpreadOutAndInset()
10  Rem 1) Worksheets Info
20  Dim ws As Worksheet                                                                                     ' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object. This also us to get easilly at the Methods and Properties throught the applying of a period ( .Dot) ( intellisense )
30  Set ws = ActiveSheet '           ws = Worksheets("Inserting") 'CHANGE NAME TO SUIT YOUR SHEET !!!!!!!!                               ' use Worksheets Object collection of Worksheets to referrence Worksheet  ' values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer"".
40  '                                Set ws1 = Worksheets.Item(1) ' Item alternative counting from tabs from left
50  Let ws.Range("B10:E13").Interior.Color = RGB(255, 255, 0)
60  Dim rng As Range: Set rng = ws.Range("D10:D12")

'Demo 1 ==============================================================================================================
70  Rem 2) Demo for .Insert ... :=Accross Shift
80  Let rng.Interior.Color = RGB(255, 0, 0) ' I am applying a color through .Color Property to the particular Range Object rng
90  Let rng.Value = rng.Address ' Address held by Excel for this particular Range Object will be returned as the Range Value
100 rng.Insert xlShiftToRight ' I create a New Virgin Range Object of Size equal to Range Object, rng, at the position in the Sheet of the range Object rng. In order to do this I must make a space. I do this by spreading/ shifting those cells necerssary to the right. Consequently my rng is now also in a different place, and some cells are lost ( "slip off" ) the Worksheet.
110 Let rng.Interior.Color = RGB(255, 0, 0) ' This makes no change to rng, as rng is now in the new position and I "re-paint" it again in the same color.
                                                                                                                 rng.Offset(0, (-1 * rng.Columns.Count)).Interior.Color = xlNone ' Bodge for demo code as VBA puts backgroung colour of adjacent cells in which messes up the demo
120 Let rng.Value = rng.Address 'This changes the value of the original Range object, rng, as this is now in a new position in the Worksheet
130 Let rng.Offset(0, (-1 * rng.Columns.Count)).Value = "MiVrginRnge"  ' This gives a value to My New Virgin Range.
140 Rem 3) VBA Range .Delete Method
150 rng.Offset(0, (-1 * rng.Columns.Count)).Delete xlShiftToLeft ' Has similar argument and arguments to the .Insert , but not quite as bad......  the .Delete method chops out an area equal the the size of ARange at the Position of the ARange, so effectively kills "it"     And shift approprite cells back ( To left ) to fill the gap ( and presumably add new cells to the right end of the sheet to fill the gap )
160 'Let rng.Value = ArngToInsert.Address ' Runtime Error 424 : Object needed. rng is now Nothing
'End Demo 1 -----------------------------------------------------------------------------------------------------------

170 Rem 4) ' Clear contents for next demo
180 Set rng = ws.Range("D10:D12") '
190 rng.ClearContents
200 Let rng.Interior.Color = RGB(255, 255, 0)
 '
' Demo 2 ==============================================================================================================
210 Rem 5) Demo for .Insert ... :=Down Shift
220 Let rng.Interior.Color = 255 'Paint rng red in original position
230 Let rng.Value = rng.Address ' Write in Original Address values
240 rng.Insert xlShiftDown ' Make a New range   At Position and With Size or rng  , ( so using rng as a Template / Pattern ) ,    spread / shift cells appropriately down to make space, ( and presumably cells "drop off" the bottom of the Worksheet )
250 Let rng.Interior.Color = RGB(255, 0, 0) '"Re paint" in red rng in its new position ( No change )
                                                                                                               '  Not neededin this demo ':   rng.Offset((-1 * rng.Rows.Count), 0).Interior.Color = xlNone
260 Let rng.Value = rng.Address 'Write in new Address of rng as it is in a new position
270 Let rng.Offset((-1 * rng.Rows.Count)).Value = "MiVrginRnge" 'Write some values in the new range
' End Demo 2 ----------------------------------------------------------------------------------------------------------

280 Rem 6) VBA Range .Delete Method
290 rng.Offset((-1 * rng.Rows.Count)).Delete xlShiftUp 'Chop out rng    Shift back up appropriate cells to fill gap ( and presumably add new cells to fill gap then made at the bottom )
'
'
'
'
'

300 Rem 9) ' Clear contents for next demo
310 Set rng = ws.Range("D10:D12") '
320 rng.ClearContents
330 Let rng.Interior.Color = RGB(255, 255, 0)
End Sub

_.............................................................................................................
_....................................

So whenever I need the .Insert in the future ( such as part of a code when answering a thread) , I shall copy my Function after any code and then replace the line

rng.Insert xlShift_____

with

Call AlnWonkShtIst(rng, ______ )

I think that makes no sense enough. And will look forward to any comments, however rude and appropriately / deservingly insulting.


Thank you for watching.

Jack

_..............................................

Codes:

Calling Demo Code for Calling Function
Public Function AlnWonkShtIst(ARangeArea As Range, ShtSpread As Variant)

http://www.excelforum.com/showthread...t=#post4371552

'  Call  Code For  Function AlnWonkShtIst   in   Thread
'            VBA Range.Insert Method ( Excel OOP Syntax Error )
Sub MyTestsInsert() '                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          MyTestsInsert.. well not quite...   but at least to End stop, if she can take it.....  ;)
Rem 1) Worksheets Info
Dim ws As Worksheet: Set ws = ActiveSheet:           Set ws = Worksheets("Inserting") 'CHANGE SHEET NAME TO SUIT YOURS
ws.Cells.Clear
Dim rng As Range: Set rng = ws.Range("D10:D12")
'Arbritrary initilal sheet values
Let rng.Value = "Anythink"
Application.Wait (Now + TimeValue("0:00:05"))
Dim vOriginalValues As Variant: Let vOriginalValues = rng.Value 'Back up for origninal Range Values

'Demo code part start===================================
Rem 2)  Demo for .Insert ... :=Accross Shift
Let rng.Value = "Original Range here" 'Effectively these are the original values when calling the Function in the Practice.
ws.Columns.AutoFit
Application.Wait (Now + TimeValue("0:00:05"))
Call AlnWonkShtIst(rng, -4121)
'Normal End point of Function, showing values as "Original Range here" in new position
Application.Wait (Now + TimeValue("0:00:05"))
Let rng.Value = "Original range is here now"
Application.Wait (Now + TimeValue("0:00:05"))

ws.Cells.Clear 'Clear for next demo

'Arbritrary initilal sheet values
Let rng.Value = "Anythink"
Application.Wait (Now + TimeValue("0:00:05"))
Let vOriginalValues = rng.Value 'Back up for origninal Range Values

Rem 5) Demo for .Insert ... :=Down Shift
Let rng.Value = "Original Range here" 'Effectively these are the original values when calling the Function in the Practice.
ws.Columns.AutoFit
Application.Wait (Now + TimeValue("0:00:05"))
Call AlnWonkShtIst(rng, "d")
'Normal End point of Function, showing values as "Original Range here" in new position
Application.Wait (Now + TimeValue("0:00:05"))
Let rng.Value = "Original range is here now"
Application.Wait (Now + TimeValue("0:00:05"))

'End demo Code part----------------------------------------

Rem 10) Demo over Put back original values back in sheet
    If IsArray(vOriginalValues) Then 'Orignal Range may be 1 or more cells so .Value property my retunn 1 or a field of Values
    Let rng.Value = vOriginalValues
    Else ' For 1 cell , put single value back in
    Let rng.Value = vOriginalValues
    End If

End Sub

_......................................

Now here The called ( by above code )
Function

http://www.excelforum.com/showthread...t=#post4371553

'
'
'   Function for Thread      VBA Range.Insert Method ( Excel OOP Syntax Error )
'   Alternative for VBA Range.Insert Method
'
'
Public Function AlnWonkShtIst(ARangeArea As Range, ShtSpread As Variant) 'ShtSpread:= "Right" , "R" , "r"   or  -4121 "Down" , "D" , "d"          or        xlShiftDown -4121 Shift cells down.        xlShiftToRight -4161 Shift cells to the right.      xlDown -4121 Down.     xlToLeft -4159 To left.     xlToRight -4161 To right.       xlUp -4162 Up.
'  ARangeArea is to be used as a Template / Pattern to say where and what size to create a New Virgin Range
'  ShtSpread is some argument to specify which direction to spread aside to make space for the new Virgin Range object
Dim MiVrginRnge As Range, OrigRange As Range ' New Virgin Range, Original Range used as template and necerssarily shifted by the AlnWonkShtIst Function ( VBA .Insert Method )
Set OrigRange = ARangeArea

Rem 3) .Insert ...  :=Accross Shift    Demo 1
    If CStr(ShtSpread) = "Right" Or CStr(ShtSpread) = "R" Or CStr(ShtSpread) = "r" Or CStr(ShtSpread) = "-4121" Then
    ARangeArea.Insert xlShiftToRight
    Set MiVrginRnge = ARangeArea.Offset(0, (-1 * ARangeArea.Columns.Count))
Rem 5) .Insert ...  :=Down Shift       Demo 2
    Else
    ARangeArea.Insert xlShiftDown
    Set MiVrginRnge = ARangeArea.Offset((-1 * ARangeArea.Rows.Count), 0)
    End If
Rem - RamSham 69) ' Bit of dialogue,   Wait a bit ;)    , Shut up again.......................                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   If the Excel kids are United, United United, United we Satnd, United we Fall  , We will never be divided.   This 'aint no Love Song neither , it is for real, man :    http://www.mrexcel.com/forum/lounge-v-2-0/929097-artificial-general-intelligence.html#post4466440
Dim vOrigRangeDotValue As Variant: Let vOrigRangeDotValue = OrigRange.Value 'Orignal Range may be 1 or more cells so .Value property my retunn 1 or a field of Values
    If IsArray(vOrigRangeDotValue) Then
    Let ARangeArea.Value = "Original Range Shifted 'ere"
    Let MiVrginRnge.Value = "New Virgin range" '  "VBA Shooting out for intercepts" for single value "unorientetd" Array will result in this going in all Original Range Cells, even for more than one.
    MiVrginRnge.Parent.Columns.AutoFit
    Application.Wait (Now + TimeValue("0:00:05"))
    Let OrigRange.Value = vOrigRangeDotValue
    Else
    Dim OrigRangeValue As String: Let OrigRangeValue = OrigRange.Value
    Let OrigRange.Value = "Original Range Shifted 'ere"
    Let MiVrginRnge.Value = "New Virgin range" '  "VBA Shooting out for intercepts" for single value "unorientetd" Array will result in this going in all Original Range Cells, even for more than one.
    Application.Wait (Now + TimeValue("0:00:05"))
    Let OrigRange.Value = OrigRangeValue
    End If
'MiVrginRnge.Clear
End Function

'   https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.insert.aspx
'   https://msdn.microsoft.com/de-de/library/office/ff840310.aspx
'   XlInsertShiftDirection-Enumeration (Excel)
'   xlShiftDown -4121    Zellen nach unten verschieben
'   xlShiftToRight -4161    Zellen nach rechts verschieben

Hi
Thank you for taking an interest.

With regard to your question:

Originally Posted by Izandol

What is benefit of this?

. I am not sure if I fully understand your exact question. Possibly if you have time you could elaborate a little?

But in the meantime I shall try to answer as best I can..... Regarding this Thread and resins for benefits of etc.

_1) Very broadly i think the thread could come under the category of Tutorials, as is part of the Title of this Sub Forum.

The screenshot produced by the Forum Tools
(_... http://www.excelforum.com/the-water-...xcelforum.html
http://www.excelforum.com/suggestion...ml#post4355361
_....)
in particular, I felt, demonstrated very nicely what is going on with the VBA .Insert. – Posts # 2 – 3 even at a quick glance show up very clearly the “shifting” and “creating a Range ” involved. As does Post # 5. In particular the direction and the "buldging out" of the background highlightes yellowl Cells makes it very clear how things are spread out or shifted to allow for the insertion of a New Virgin Range. This is very easy Pictorily to show, but difficult to explain easilly, I felt.

I realise the above may not be a great benefit to someone like yourself , as you presumably understand already the VBA .Insert

_.....
But, and Further

_2 ) The running theme in my Post and one catalyst for it, was my suggestion that
“shifting” and “creating a Range”,
applied for example to a Worksheet using the argument
( ARange As Template, SpecifiedxlShiftDirection )
was more appropriate than the current

"Excel Range.Insert Method"

It was the confusion in this , that is to say, exactly what is going on, that initiated my doing the screenshots to convince myself what was going on. This gave me a very good understanding at least, suggesting my proposal could have some validity.
Having got such a god understanding thought these various screen shots and code snippet attempts, I felt it was worthwhile to tidy up, consolidate and share for the benefit of others. Any further feedback on my ideas cloud possibly further the learning attributes of this thread.

Hope that helps. Thanks again for taking the time to reply
Jack

P.s. 1
I see from your Profile that you are a VB6 specialist. ( I am vary new generally to computing, - a very late starter hoping to speed up an important personal job forced upon me through applying programming, ( currently to an existing very large Excel Spreadsheet ) ). I did however do a very short evening course on VB, ( VB.Net ) , which i think is very similar to VB6 and , other more experienced Forum Members have told me both that VB is a true OOP ( Object orientated Programming ) language and also, they have sometimes commented then commented further suggesting that VBA is “not really” a OOP language
I wonder therefore if my confusion as to the correct way to describe this Insert or Shift / Create process is an example of this VBA non perfection to a 1 to 1 OOP VB6 to VBA comparison. I struggle initially to think of a VB equivalent to the thing we are doing here to a worksheet.
Possibly you are able to add some useful input on this last point.

P.s.2
It is possible the splitting into several posts ( Due to Forum Post size limitations ) has made the Thread less easily readable. I therefore took my a step i rarely approve and cross posted her, ( as I said at the outset of this Tread ),
http://www.mrexcel.com/forum/general...ml#post4501533
_.. as at the referenced Site there were no such size restrictions to a post length and so i was able to post in one Post initially.
Incidentally , my participation in a Thhread there sparked my interest initially on getting the concepts discussed here clear.
http://www.mrexcel.com/forum/excel-q...ml#post4501046
(The advantage however in this Forum is that i am able to edit Typos, Or edit should you have any suggested corrections , for example )

Thanks once again for your interest. I look forward if you are able to make any comments , in particular to the Latter Points in _2)

Jack

Actually the Wife / Wife's computer, but "effectively" as good as!