How to use the ARRAY function [VBA]
The ARRAY function creates a Variant variable containing array values.
Sub Macro1() MyArray = Array("Cat", "Dog", "Rabbit") For i = LBound(MyArray) To UBound(MyArray) txt = txt & MyArray(i) & vbNewLine Next i MsgBox txt End Sub
The macro above populates variable MyArray with values Cat, Dog and Rabbit.
A FOR ... NEXT statement loops through the array variable using the Lbound and Ubound function to determine the lower and upper limit of the array.
An ampersand then concatenates the values and lastly, the msgbox shows the concatenated string.
Excel Function VBA Syntax
Array(arglist)
Arguments
arglist | A list of values. Text strings must have a beginning and ending double quote. The values must be separated by a comma. If omitted a zero-length array is created. |
Comments
The following macro demonstrates another way to create an array variable. [arglist] means evaluate.
Sub Macro2() MyArray = [{"Cat", 5, "Rabbit"}] For i = LBound(MyArray) To UBound(MyArray) txt = txt & MyArray(i) & vbNewLine Next i MsgBox txt End Sub
This macro creates array variable MyArray with values in two dimensions.
Sub Macro3() MyArray = Array(Array("Cat", "Dog"), Array("Rabbit", "Squirrel")) For r = 0 To 1 For c = 0 To 1 txt = txt & MyArray(r)(c) & " " Next c txt = txt & vbNewLine Next r MsgBox txt End Sub
This macro populates array variable MyArray with values in two dimensions using the evaluate characters.
Sub Macro4() MyArray = [{"Cat", "Dog"; "Rabbit", "Squirrel"}] For r = LBound(MyArray, 1) To UBound(MyArray, 1) For c = LBound(MyArray, 2) To UBound(MyArray, 2) txt = txt & MyArray(r, c) & vbNewLine Next c Next r MsgBox txt End Sub
One Response to “How to use the ARRAY function [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I'm wondering if I can have a list of 40 Names and combine them in pairs without repeating the name pairs. Example:
Anna
Marc
Peter
Rawn
Result:
Anna - Marc
Peter - Rawn
I would be very greatful if you could help me :-)! Thank you.