VBA Procedure to Quickly Produce Text Array From Worksheet List

[Wookiee]

When creating text arrays in VBA, I've found that it can be quite tedious to type every single item in an array, especially if the list is long. So I created this macro which will take a list of values in Column A of the active worksheet and produce an array in Cell C5. You can then copy the entire cell and paste directly into VBA.

[pre]

Sub TextArrayCreator()
' Macro crafted 15 November 2011 by Jason B White

'Declare Variables
Dim lngLastRow As Long, strArray As String, intLoop As Integer, rngCell As Range

'Determine Last Cell In Column A With Data
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Create Beginning Segment Of Array
strArray = "Array("

'Format Text In Each Cell To Add As Array Element
For intLoop = 1 To lngLastRow
strArray = strArray & Chr(34) & Range("A" & intLoop).Value & Chr(34) & ", "
Next intLoop

'Add Closing Parenthesis
strArray = strArray & ")"

'Remove Superfluous Comma And Space From Last Entry
strArray = Replace(strArray, ", )", ")")

'Print Array Formula To Cell C5
Range("C5") = strArray

End Sub

[/pre]

I hope I used the code tags properly.