Array(s) Resolved

[IGBP]

Hi everyone ...
brand new to this forum and just learning vb/vba.
In Excel, i am trying to write a function.
I have several named ranges and have figured out how to loop through the ranges and get a total of all cells in all ranges.
Now what I am trying to figure out is ...
I want to create 4 arrays. 1 for each named range in this spreadsheet(all the ranges are different lenghts and will be added to often). I then want to loop through each range and assign each cells value to one element of the array, reDim and Preserve the array, then get the next value. Once I have all the elements, I can (hopefully) perform some calculations based on certain criteria.
I am calling the function from a command button click event.
Any help, ideas, or direction to examples is appreciated.
Thanks

[WorkHorse]

To assign the values to an array, you need to Dim the array variable with parentheses () with nothing in them. That lets VB know the variable is an array but doesn't have a fixed number of elements. You must ReDim the array with the low-bound value before you can use it. As you loop through the cells in the range, ReDim Perserve the array, incrementing the the upper-bound index by one. Here's a funtion:

Function RangeToArray(ByVal strRangeName As String)
 
    Dim c As Range
    Dim ReturnArray()
    ReDim ReturnArray(0)
    
    For Each c In Range(strRangeName)
        ReDim Preserve ReturnArray(UBound(ReturnArray) + 1)
        ReturnArray(UBound(ReturnArray)) = c.Value
    Next
 
    RangeToArray = ReturnArray
 
End Function

You can call it like this:

Sub MyClickEvent()
 
    Dim i As Long
    
    Dim MyRangeValues()
    ReDim MyRangeValues(0)
 
    MyRangeValues = RangeToArray("MyRangeName")
 
    For i = 1 To UBound(MyRangeValues)
        Debug.Print MyRangeValues(i)
    Next
 
End Sub

If you want, you can use another variable in the Function to keep track of incrementing the upper-bound index of the array instead of checking the UBound each time.

[IGBP]

WorkHorse,
Excellent Post. Thank-you very much.
I will try it and let you know.

IGBP

[IGBP]

Thanks WorkHorse