Divide Array into individual ParamArray arguments

[whiteheadw]

All,

Just curious if it was possible to divide an array into individual values (or objects) that could later be passed to a ParamArray argument.

For example, say I have an array of ranges, and I wanted to pass the entire array to Application.Union. Right now, the only way I could do this is using a For...Next loop, which doesn't really take advantage of the ParamArray option for the Application.Union function. There has got to be a way, however, to pass an entire array all at once into a ParamArray function.

Is this possible?

Thanks

 

[xenou]

Your terminology is confusing. I'm not familiar with a paramarray function, and when I type paramarray into VBA help I get no results. can you explain more about what you want to do, and give an example? As far as multiple arguments to union I've usually have two arguments (can't even remember three) so an example of what you mean will help as far as a test case - how many ranges are involved and what is the reason for employing union on (perhaps) so many ranges.

 

[whiteheadw]

Your terminology is confusing. I'm not familiar with a paramarray function, and when I type paramarray into VBA help I get no results. can you explain more about what you want to do, and give an example? As far as multiple arguments to union I've usually have two arguments (can't even remember three) so an example of what you mean will help as far as a test case - how many ranges are involved and what is the reason for employing union on (perhaps) so many ranges.

Here is an example of a paramarray

Option Explicit

Private Sub ShowText(ParamArray varText() As Variant)
    Interaction.MsgBox Strings.Join(varText, " ")
End Sub

Public Sub AddArguments()
    ShowText "Hello", "World", "This is Cool", vbCrLf, "I am passing multiple arguments into one parameter, thus the term paramarray"
End Sub

Basically, let's say that instead of all those constant strings in AddArguments, I have an array (doesn't matter if it's an array of datatypes or objects, it's the array array containing the datatypes/objects that I'm concerned with). Is it possible to take an array (i.e. dim strArr(1 to 10) as String), without knowing the LBound or UBound of the array, and pass each instance of the array to a subroutine or function that accepts a ParamArray argument (like ShowText does, or like Application.Union, Application.WorksheetFunction.Min/Max, etc.)?

Thanks.

 

[whiteheadw]

Bump?

 

[xenou]

I would probably use a loop in the examples you've suggested, or pass real worksheet range ref(s) rather than a variant array, but I suspect that isn't the answer you are looking for ... otherwise, I'm stumped. If its a matter of specifics we could investigate further in a concrete case.

ξ

 

[whiteheadw]

I would probably use a loop in the examples you've suggested, or pass real worksheet range ref(s) rather than a variant array, but I suspect that isn't the answer you are looking for ... otherwise, I'm stumped. If its a matter of specifics we could investigate further in a concrete case.

ξ

The only specific I'm interested in is seeing if there's a VB function that does this automatically. It doesn't sound like there is, though, so I I'll just use loops when I need to.

The way I actually would like to implement this is probably more object oriented then Excel.Application oriented, anyway.