Solved: Is it possible to have a function return an array of ranges?

[BenD]

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?

[Bob Phillips]

Ben,

An array is an array, and it can contain ANYTHING. Do you want to use that returned value in VBA, or are you hoping to return it to the worksheet (if so, in what way?).

[Imaginative1]

Are you getting an error? Or, are you planning ahead for your implementation...

[BenD]

xld,

I only need to use it in VBA, so I can further process. No need to return it to a worksheet as a range.
See some background below, but that is not essential for the original question.

Background:
For an application that I developed I have a worksheet that can be saved as output (only for reference purposes, see it as an electronic copy). However, for security reasons, but also to limit file size, I am pasting the content as a picture in a new workbook.
At the moment the worksheet contains 2 pages, which, if I want to mimic the original, I need to copy as pictures separately (otherwise the output does not look good).
The helper routine that identifies the ranges for each page is ready, but I need to be able to call that helper routine, which on return should provide me the ranges for each page so they can be processed further.

[Bob Phillips]

Well, as I said, an array can contain anything



Public Sub Test()
Dim ary

ary = rngReturnRanges(ActiveSheet)
MsgBox ary(0).Address(, , , True)
MsgBox ary(0).Address(, , , True)

End Sub

Function rngReturnRanges(ByRef wksSheet As Worksheet) As Variant
Dim rngPages() As Variant

ReDim rngPages(0 To 1)
Set rngPages(0) = wksSheet.Range("A1")
Set rngPages(1) = wksSheet.Range("B1")

rngReturnRanges = rngPages()
End Function

[BenD]

Hi xld,

Great. You are using a Variant as the declaration for the function. Of course, why did I not thing of that?
As usual, it is all so easy once some one has shown you....

Many thanks, I can make good use of this.