BenD
12-17-2009, 02:35 PM
I was wondering if it is possible to somehow return an array of ranges from a function. I have a function that assigns a variable number of ranges and these need to become available outside that function. One route would be to use a global range array, but I would prefer not to go the global route.
Therefore I was thinking of 2 approaches:
Have a function return the array of ranges.
Use an range array as function parameter.Approach 1 could look like (a very simplified example):
Function rngReturnRanges(ByRef wksSheet as Excel.Worksheet) as Excel.Range()
Dim rngPages() As Excel.Range
ReDim rngPages(1)
set rngPages(0) = wksSheet.Range("A1")
set rngPages(1) = wksSheet.Range("B1")
set rngReturnRanges = rngPages()
end Function
Approach 2 could look like:
Sub test()
Dim rngPages() As Excel.Range
ReDim rngPages(2)
Set rngPages(0) = wksBasicMode.Range("A99")
Set rngPages(1) = wksBasicMode.Range("B99")
Call rngReturnRanges2(wksBasicMode, rngPages())
' Do something with the changed ranges:
End Sub
Sub rngReturnRanges2(wksSheet As Worksheet, ByRef rngPages() As Excel.Range)
Set rngPages(0) = wksSheet.Range("A1")
Set rngPages(1) = wksSheet.Range("B1")
End Sub
After some testing I came up with the example in approach 2 that works. But it still makes me wonder if a function by itself can return an array of ranges. Anyone in a position to give a definite answer?
Therefore I was thinking of 2 approaches:
Have a function return the array of ranges.
Use an range array as function parameter.Approach 1 could look like (a very simplified example):
Function rngReturnRanges(ByRef wksSheet as Excel.Worksheet) as Excel.Range()
Dim rngPages() As Excel.Range
ReDim rngPages(1)
set rngPages(0) = wksSheet.Range("A1")
set rngPages(1) = wksSheet.Range("B1")
set rngReturnRanges = rngPages()
end Function
Approach 2 could look like:
Sub test()
Dim rngPages() As Excel.Range
ReDim rngPages(2)
Set rngPages(0) = wksBasicMode.Range("A99")
Set rngPages(1) = wksBasicMode.Range("B99")
Call rngReturnRanges2(wksBasicMode, rngPages())
' Do something with the changed ranges:
End Sub
Sub rngReturnRanges2(wksSheet As Worksheet, ByRef rngPages() As Excel.Range)
Set rngPages(0) = wksSheet.Range("A1")
Set rngPages(1) = wksSheet.Range("B1")
End Sub
After some testing I came up with the example in approach 2 that works. But it still makes me wonder if a function by itself can return an array of ranges. Anyone in a position to give a definite answer?