Learn Macros‎ > ‎VBA Macros‎ > ‎

Store excel range in an array variable

posted Nov 9, 2011, 11:06 PM by Ayush Jain   [ updated Nov 10, 2011, 12:58 AM ]
If you want to store excel range in string array variable , you can use this user define function.
 
Public Function ReturnArraySel(xlRange As Range) As String()
    Dim strArray() As String
    Dim intctr As Integer
    Dim intCount As Integer
    Dim xlCell As Range
   
    intctr = 0
    intCount = xlRange.Cells.Count
    
        ReDim strArray(0 To intCount - 1)
        For Each xlCell In xlRange
                strArray(intctr) = xlCell.Value
                intctr = intctr + 1
        Next
    
    ReturnArraySel = strArray
 
End Function
 
You just need to pass the excel range as a parameter to above function and it will return an array string.
 
EXAMPLE :
 
Sub StoreArray()
    Dim strArr() As String
    strArr() = ReturnArraySel(ActiveSheet.Range("A1:A50"))
End Sub
 
 
 
 
 
 
ċ
ReturnArraySel.xlsm
(16k)
Ayush Jain,
Nov 10, 2011, 12:38 AM
Comments