VBA Array

[gbell3587]

Hi

Ive only recently got in to vba and need to know the syntax of an array. Can someone give me an easy example

Say i have a list of names in column a - how could i store these values in an array and then when required, print the array to either a message box or another row, column or whatever.

Thanks
Graham

 

[tweedle]

Here's a Quick Sample that pulls sheet1 B2-B10.

Option Base 1
Dim strArr() As String * 25
Dim rng, C As Range
Sub Main()
Set rng = ActiveWorkbook.Sheets(1).Range("B2:B10")
LoadArr
DumpArr
End Sub
Sub LoadArr()
ReDim strArr(rng.Cells.Count)
For x = 1 To rng.Cells.Count
    Set C = rng.Cells(x)
    strArr(x) = Trim(C)
Next
End Sub
Sub DumpArr()
msg = ""
For x = LBound(strArr()) To UBound(strArr())
    msg = msg & x & ". " & strArr(x) & vbCrLf
Next
MsgBox msg
End Sub

 

[meldoc]

Hello

Try these, with some data in column B like below.

Excel Workbook
	B
1	bob
2	ted
3	sid
4	len
5	ben
Sheet1

Excel 2010<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test3()<br><SPAN style="color:#007F00">' Load and Empty Array</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, y<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, lr<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> vResult()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><br>  lr = Cells(Rows.Count, "B").End(xlUp).Row<br>  <SPAN style="color:#00007F">ReDim</SPAN> vResult(lr)<br>  <br>    <SPAN style="color:#00007F">For</SPAN> i = 1<SPAN style="color:#00007F">To</SPAN> lr<br>      vResult(i) = Cells(i, 2)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>  <br>    <SPAN style="color:#00007F">For</SPAN> y = 1<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(vResult)<br>      Cells(y, 4) = vResult<br>    <SPAN style="color:#00007F">Next</SPAN> y<br><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> test4()<br><SPAN style="color:#007F00">' Load and Empty Array Reverse</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> i<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, y<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, lr<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> vResult()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN><br>  lr = Cells(Rows.Count, "B").End(xlUp).Row<br>  <SPAN style="color:#00007F">ReDim</SPAN> vResult(lr)<br>  <br>    <SPAN style="color:#00007F">For</SPAN> i = 1<SPAN style="color:#00007F">To</SPAN> lr<br>      vResult(i) = Cells(i, 2)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>  <br>    <SPAN style="color:#00007F">For</SPAN> y = 1<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(vResult)<br>      Cells(y, 4) = vResult(UBound(vResult) - y + 1)<br>    <SPAN style="color:#00007F">Next</SPAN> y<br><br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[gbell3587]

Hello

Try these, with some data in column B like below.

Excel Workbook
	B
1	bob
2	ted
3	sid
4	len
5	ben
Sheet1

Excel 2010



Sub test3()
' Load and Empty Array
Dim i As Long, y As Long, lr As Long
Dim vResult() As Variant
lr = Cells(Rows.Count, "B").End(xlUp).Row
ReDim vResult(lr)

For i = 1 To lr
vResult(i) = Cells(i, 2)
Next i

For y = 1 To UBound(vResult)
Cells(y, 4) = vResult
Next y

End Sub

Sub test4()
' Load and Empty Array Reverse
Dim i As Long, y As Long, lr As Long
Dim vResult() As Variant
lr = Cells(Rows.Count, "B").End(xlUp).Row
ReDim vResult(lr)

For i = 1 To lr
vResult(i) = Cells(i, 2)
Next i

For y = 1 To UBound(vResult)
Cells(y, 4) = vResult(UBound(vResult) - y + 1)
Next y

End Sub

Thanks for this, i'll have a play around and see if i can get it to fit with my code.