Excel VBA - array as union of named ranges : odd behaviour

Question

I'm strugglying for hours trying to understand why my code is not working, despite repeating twice the exact same way of doing :

This works on a set of named ranges :

Dim MyArr() As Variant
Dim RangeName As Variant
RangeName = Array("oneNamedRange", "anotherNamedRange", "onemoreNamedRange")
MyArr = Union(Range(RangeName(0)), Range(RangeName(1)), Range(RangeName(2)))

Now if I try to get another data set like :

Dim MyProcess() As Variant
RangeName = Array("nr1", "nr2", "nr3", "nr4", "nr5", "nr6", "nr7")
MyProcess = Range(RangeName(0)) ' Ok
MyProcess = Range(RangeName(1)) ' Ok
MyProcess = Range(RangeName(2)) ' Ok
MyProcess = Range(RangeName(3)) ' Ok
MyProcess = Range(RangeName(4)) ' Ok
MyProcess = Range(RangeName(5)) ' Ok
MyProcess = Range(RangeName(6)) ' Ok

MyProcess = Union(Range(RangeName(0)), Range(RangeName(1))) ' Ok, got my 2D array
MyProcess = Union(Range(RangeName(0)), Range(RangeName(2))) ' NOK
MyProcess = Union(Range(RangeName(0)), Range(RangeName(3))) ' NOK
MyProcess = Union(Range(RangeName(0)), Range(RangeName(4))) ' NOK
MyProcess = Union(Range(RangeName(0)), Range(RangeName(5))) ' NOK
MyProcess = Union(Range(RangeName(0)), Range(RangeName(6))) ' NOK

MyProcess = Union(Range(RangeName(0)), Range(RangeName(0)), Range(RangeName(0))) ' NOK gives only 1D
MyProcess = Union(Range(RangeName(1)), Range(RangeName(1)), Range(RangeName(1))) ' NOK gives only 1D
MyProcess = Union(Range(RangeName(0)), Range(RangeName(1)), Range(RangeName(1))) ' NOK gives only 2D out of 3
MyProcess = Union(Range(RangeName(0)), Range(RangeName(1)), Range(RangeName(2))) ' NOK gives only 2D out of 3

Looks like the Union or Application.union works strangely in VBA. I also checked the named ranges (size, name..) but could not find any clue.

Could I find any good sub procedure to set an array from named ranges, working in any case of provided ranges ?

After the first Union(), the next iterations should use Union(myProcess, [rangeToAdd]) — Tim Williams
– Tim Williams, Commented Oct 4, 2014 at 16:07
MyProcess is an array, I guess can't add anything to this. The next "union" are for iterative testing purposes, trying each range individually, then adding 2 ranges and so on. — hornetbzz
– hornetbzz, Commented Oct 4, 2014 at 16:14
A working solution is [there][1] by Tim. Thx [1]: stackoverflow.com/a/23894096/461212 — hornetbzz
– hornetbzz, Commented Oct 4, 2014 at 18:26
I didn't quite read your question carefully enough before I commented, but I'm glad you found a solution. — Tim Williams
– Tim Williams, Commented Oct 4, 2014 at 19:31
@Tim : yep, indirect thx to you as I spent many time on your posts :-) — hornetbzz
– hornetbzz, Commented Oct 4, 2014 at 19:37

hornetbzz · Accepted Answer · 2014-10-04 19:36:16Z

Here is how I solved my issue :

Private Function dBToArray(ByVal NamedRanges As Variant, Optional ByVal oSht As Worksheet = Nothing)

    Dim I As Long
    Dim J As Long
    Dim NbData As Long
    Dim NbRanges  As Long
    Dim MyValue As Variant
    Dim MyArray() As Variant

    ' ---------------
    ' CHECK ARGS
    ' ---------------
    If IsMissing(oSht) Then
        MsgBox "info : Parameter arg not passed"
    End If

    If oSht Is Nothing Then
         Set oSht = ActiveWorkbook.Sheets("dB")
    End If

    If ws_exists(oSht.Name) = False Then
        MsgBox "WS Non Exists"
        Exit Function
    End If

    NbData = Range(NamedRanges(0)).Count ' e.g. ID_process count
    NbRanges = UBound(NamedRanges)
    ReDim MyArray(1 To NbData, 1 To NbRanges) As Variant

    ' Parse the dB ranges
    For I = 1 To NbData
         For J = 1 To NbRanges
             MyValue = oSht.Range(NamedRanges(J - 1)).Value
             ' Debug.Print Chr(10) & Time & " - I: " & I & "," & "J: " & J & ", Val = " & MyValue(I, 1)
              MyArray(I, J) = MyValue(I, 1)
         Next J
     Next I



    ' Return the multiDim array
    dBToArray = MyArray

    ' Free some mem
    Set MyValue = Nothing
    Erase MyArray

End Function

Usage

Dim RangeName As Variant
Dim MyArrayFromdBSheet() As Variant

RangeName = Array("ID", "PROCESS_NAME", "PROCESS_CPY", "PROCESS_START", "PROCESS_END")
MyArrayFromdBSheet = dBToArray(RangeName)

Collectives™ on Stack Overflow

Excel VBA - array as union of named ranges : odd behaviour

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related