VBA versions of Array.add and the += operator

[vbanative]

I am trying to write a couple lines of code in VBA, here is what the VB.Net version of the code looks like.

<code class="keyword"></code>

<code class="plain"></code>

<tbody>
</tbody>

<code class="spaces"> ThisVariable</code><code class="plain"> <<= 8</code>

<tbody>
</tbody>

<code class="spaces"></code><code class="plain"> </code>

<tbody>
</tbody>

<code class="spaces"> </code><code class="plain">Variable.Add(ThisVariable)</code>

<tbody>
</tbody>

How do I write these lines of code in VBA? VBA does not have the "<<=" operator and does not have the .Add property of an array. Any help would be greatly appreciated.

 

[Dryver14]

should that be <= or really <<=

 

[Andrew Poulsom]

For the shift operator see:

Bit Shifting Function in Excel VBA and Visual Basic

 

[vbanative]

should that be <= or really <<=

It is <<=; the bit-shift operator

 

[vbanative]

It is <<=; the bit-shift operator

anybody have any suggestions for how to add an element to the end of array? In VB.net it would simply arrayname.add

 

[Rick Rothstein]

anybody have any suggestions for how to add an element to the end of array? In VB.net it would simply arrayname.add

Assuming it is a one-dimensional array named MyArray...

ReDim Preserve MyArray(LBound(MyArray) To UBound(MyArray) + 1)
MyArray(UBound(MyArray)) = NewValue

Note though that ReDim Preserve is not a fast operation (VB reallocates a new chunk of memory for the array each time it is executed), so if you are doing this repeatedly in a loop, it is better to ReDim Preserve in large chunks rather than one at a time, check if you have filled to the last array element and then ReDim Preserve a new large chunk. Keep track of elements with an index variable and at the end of the loop, do one last ReDim Preserve using the last used index value for the arrays final upper bound setting.

 

[Rick Rothstein]

Note though that ReDim Preserve is not a fast operation (VB reallocates a new chunk of memory for the array each time it is executed), so if you are doing this repeatedly in a loop, it is better to ReDim Preserve in large chunks rather than one at a time, check if you have filled to the last array element and then ReDim Preserve a new large chunk. Keep track of elements with an index variable and at the end of the loop, do one last ReDim Preserve using the last used index value for the arrays final upper bound setting.

Here is a link to code I posted online once which shows the concept...

http://www.mrexcel.com/forum/excel-questions/655300-better-array-collection-objects.html#post3248913