VBA Range.Insert Method ( Excel OOP Syntax Error )

**JackSheet_1** · 04-24-2016, 09:03 PM

VBA Range.Insert Method ( Excel OOP Syntax Error )

( cross posted here: http://www.mrexcel.com/forum/general...ml#post4501533
)

A general discussion on the Excel ( Range ) .Insert Method with emphasis on both the optional argument ( xlShift bit ) .. and a suggestion that it is not really well described as a Range Method

OK. So many people know all this.. maybe.. But there are some subtle points i discuss here...

The OOP ( Object Orientated Programming ) Period ( .Dot ) stuff convention / line syntax confuses me a bit here...

And the documentation never made it clear to me what the optional argument did. And that “Microsoft Excel decides based on the shape of the Range where to shift” for no specified argument, is rather worrying / and uncertain IMO.
I agree it is all there and with hindsight it does makes sense, “.......shifts other cells away to make space…….“

Initially however i think the way the thing is presented is misleading....
But the nice thing of the Mr excel Forum Screenshot tools is that it makes it nice to and easy to demonstrate this, using the background colours...

Let me try to explain in words first.

Here is the current ( “pseudo”) code line for the VBA “Range” Insert Method.

ARange.Insert ShiftDirection

According to my understanding of OOP, .Insert is “applied” to the Range Object, ARange, in the same way that, for example, the
ARange.Clear
Property would be applied to clear most things done to a Range.

Similarly, applied to a Range Object, something like .Value Property Returns the Value ( or Values ) of the Range
= ARange.Value
Similarly the inclusion ( often omitted / forgotten ) of Let will prepare VBA to “give” the Range Object a value
Let ARange.Value = ARange.Value

In a Similar way applying the .Address Property of a Range Object can be used in a code line Such as to give the Range Values the full Address of the complete Range
Let Range.Value = ARange.Address ' Address held by Excel for this particular Range Object will be returned as the Range Value in every Cell ( or in memory allocated space for Value of that Range Object )

So generally we are talking about things to do with that Range object giving ( Letting ) or taking (getting ) from it.
Very Briefly that is OOP , ( I think ).

In the case of the
.Insert
, I am not sure if these ideas are valid.. In the demo below I have prepared I believe it is more true or appropriate to say the following.
“At a specified Area of the spreadsheet you do the following:
________ Spread / shift apart a space in a ( by argument specified direction ) and produce a new “Virgin” un referenced Range Object. The “Range” specified serves conveniently to say both where that space will be made and necessarily also the size of the new range object.”
Clearly looking at in that way, The Range Object used is not itself directly being affected. Indirectly it is itself shifted or Offset, but so are typically a large amount of Range Objects in this case, and only coincidentally is this range Object one of them

So in this particular Method I think it should be written the other way around, and it should be a Method of the Worksheet, or an Application Method. “Pseudo” Codes:

Wonkation.Insert _ ( RangeTemplate:=ARange, ShiftDirection:=Down / ToRight )
Wonkation.Shift _ ( RangeTemplate:=ARange, ShiftDirection:=Down / ToRight )
Wonkation.OffsetShift _ ( RangeTemplate:=ARange, ShiftDirection:=Down / ToRight )

Or, even name it differently
Wonkation.NewVirginRangeCreate ( WhereToAndNewRangeSize:=Range, WhichDirectionToShiftToMakeSpaceForIT:=Right Or Down )
Or, Function like
Wonkation.AlnWnkShtIst ( ARangeArea As Range, ShtSpread As String )

Later in this Thread I suggest an alternative to this line
rng.Insert xlShift_____

based on a FUnction I have written and share of this corresponding form, and which would be callled thus: ( hence replacing the above line )

Call AlnWonkShtIst(rng, ______ )
_..................................

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

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

**JackSheet_1** · 04-24-2016, 09:05 PM

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	D	E
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

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

**JackSheet_1** · 04-24-2016, 09:06 PM

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

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

**JackSheet_1** · 04-24-2016, 09:08 PM

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

Please Login or Register  to view this content.

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

**JackSheet_1** · 04-24-2016, 09:09 PM

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

Now having convinced myself ( probably wrongly ) that I am not talking NOT sense, I decide to correct this Excel OOP Syntax Error. So I wrote a Function

Public Function AlnWonkShtIst_NewVirginRangeCreate (ARangeArea As Range, ShtSpread As Variant)
‘ 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

And i wrote a demo Code to Call it
Sub MyTestsInsert_NewVirginRangeCreate()'

These codes run self explanatory: I have included some waits so you can see what is going on as the code progresses.

Approximate Progression as follows:.

Starts on a clear Spare Sheet. Puts Anything in

Using Excel 2007

_Row\^Col	C	D	E
9
10		Anythink
11		Anythink
12		Anythink
13

Inserting

For Demo purposes the Values are changed to

_Row\^Col	C	D	E
9
10		Original Range here
11		Original Range here
12		Original Range here
13

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

The Fuction is now called, just as I envisage it being used instead of
rng.Insert _ xlShiftToRight

That is to say

Call AlnWonkShtIst(rng, -4121) ' ( -4121 is just one suggestion / allowed argument in my code to specify a left shifthing to allow space for a to be newly created Range )

The Function, as would in the practice makes a back up of the Actual Values in rng, as the Function demos by pasting this out to demo the New Virgin Range Create and the shifting of the original rng thus:

_Row\^Col	D	E
9
10	New Virgin range	Original Range Shifted 'ere
11	New Virgin range	Original Range Shifted 'ere
12	New Virgin range	Original Range Shifted 'ere
13

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

The Function Ends as it would in the practice replacing the original values in rng

_Row\^Col	D	E
9
10	New Virgin range	Original Range here
11	New Virgin range	Original Range here
12	New Virgin range	Original Range here
13

_.............................
This first demo section stops now and Anything is put in rng which is still Offset / shifted as this Function is a replacement for .Insert and we are not considering the “undo” Delete.

_Row\^Col	C	D	E	F
9
10			Anythink
11			Anythink
12			Anythink
13

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

After a 15 second break the second demo part starts which considers the Shift down
For demo purposes again the original Values are changed to

_Row\^Col	C	D	E	F
9
10			Original Range here
11			Original Range here
12			Original Range here
13

The Function is now called thus
Call AlnWonkShtIst(rng, "d") ' "d" is ine possible variation the argument specifying down shift

The original values of rng are backed up, then the demo business is done showing:

_Row\^Col	C	D	E	F
9
10			New Virgin range
11			New Virgin range
12			New Virgin range
13			Original Range Shifted 'ere
14			Original Range Shifted 'ere
15			Original Range Shifted 'ere
16

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

Once again the original values are put in by the function just before it ends.

_Row\^Col	C	D	E	F
9
10			New Virgin range
11			New Virgin range
12			New Virgin range
13			Original Range here
14			Original Range here
15			Original Range here
16

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

As the last demo the calling code displays to show final position of rng

_Row\^Col	C	D	E	F
9
10			New Virgin range
11			New Virgin range
12			New Virgin range
13			Anythink
14			Anythink
15			Anythink
16

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

**JackSheet_1** · 04-24-2016, 09:09 PM

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

Please Login or Register  to view this content.

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

Now here The called ( by above code )
Function

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

Please Login or Register  to view this content.

**Izandol** · 04-25-2016, 02:07 AM

What is benefit of this?

**JackSheet_1** · 04-25-2016, 06:20 AM

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

**Izandol** · 04-25-2016, 08:39 AM

I mean only what is gained from this? It is lot of code to replace one line and I do not think it is more clear.

VB. Net is not same as VB6 at all. VB6 is more close to VBA.

**Doc.AElstein** · 04-25-2016, 08:56 AM

Originally Posted by Izandol

I mean only what is gained from this? It is lot of code to replace one line and I do not think it is more clear. .....

OK Sorry, I thought somebody might find some of the demos, codes and discussions useful

Originally Posted by Izandol

....
VB. Net is not same as VB6 at all. VB6 is more close to VBA.

OK, thanks
_..............................

Thanks for the info and opinion

**Izandol** · 04-25-2016, 10:29 AM

You are same person as Jack_Sht1?

**JackSheet_1** · 04-25-2016, 10:38 AM

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

VBA Range.Insert Method ( Excel OOP Syntax Error )

LinkBack

Thread Tools

Rate This Thread

Display

VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Re: VBA Range.Insert Method ( Excel OOP Syntax Error )

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Insert Row throws error "Insert Method of Range class failed"

While inserting a column getting an error: Insert method of range class failed

Error in Excel VBA code for inserting combo box - "Insert method of Range Class failed"

Sql not executing, insert into syntax error

[SOLVED] Insert Into syntax error

Help - Insert method of Range class failed - Selection.Insert Shift:=xlToRight

Runtime error 1004: Insert method of Range class failed......not sure why

Bookmarks

Bookmarks

Posting Permissions