VBA Merge of Dynamic Named Ranges results into a static range i/o keeping dynamic

Question

Context (VBA7.1 , Excel 2013 Pro):
all sheets and dynamic named ranges (DNR) are created programmatically. I want to merge some of the DNR units (case of same data over several columns) into a single DNR for grouping them all. At this stage, the unit DNRs and their merged result are all within the worksheet scope.

Issue:
The merged named range does not keep the dynamic property of all individual DNR. If I do it manually, it works of course, just to confirm the DNRs to merge are effectively dynamic.

Question:
how should I apply the named range merge so that the result range keeps as a dynamic named range too ?

Main Code

Sub xx()
... some code goes here ...
    Dim DNRnames() As String
    Dim MergedRange As Range
    Dim currentRng As Range
    Dim rngStr As Variant
    Dim strStringToExclude() As String

    ' Get created DNRs on this sheet
        strStringToExclude = Split("_Desc,Headers", ",")
        DNRnames = DNRGetNames(aWS.Name, False, strStringToExclude)

      ' Merge DNRs into 1 
        For Each rngStr In DNRnames
          ' Set currentRng = aWS.Names(CStr(rngStr)).RefersToRange
          Set currentRng = aWS.Range(CStr(rngStr)) ' also this way keeps it static
          If Not MergedRange Is Nothing Then
            Set MergedRange = Union(MergedRange, currentRng)
          Else
            Set MergedRange = currentRng
          End If
        Next rngStr

      ' Add "MergedRange" to the aWS : ISSUE : the MergeRange is NOT dynamic...
      ' as it would be if I would create it in the ws by a named_range=(range1,range2,..)
        aWS.Names.Add Name:=DNRprefix & "All", RefersTo:=MergedRange
...
end sub

GetDNR: return named ranges from the worksheet as a string array and exclude some selected named range that I don't want to get merged (it's a workaround, since I found "Union" but no "Substract" function in VBA)

Function DNRGetNames(sheetName As String, WbScope As Boolean, SuffixStringToExclude() As String) As String()  ' all DNR from one specific sheet (with wb scope or ws scope ?)
' https://stackoverflow.com/questions/12663879/adding-values-to-variable-array-vba
' https://stackoverflow.com/questions/4029975/excel-listing-named-range-in-a-worksheet-and-get-the-value

  ' kind of getter and setter
  Dim wb As Workbook
  Dim aWS As Worksheet
  Dim element As Name
  ReDim DNRArray(1 To 1) As String

  Set wb = ThisWorkbook
  Set aWS = wb.Sheets(sheetName)

  ' if SuffixStringToExclude is not defined, fill in the suffic string with a default fake data
  If Not Len(Join(SuffixStringToExclude)) > 0 Then
    SuffixStringToExclude = Split("*FaKe!")
  End If

  ' populate a dynamic array with DNR related to aWS
  For Each element In wb.Names
    If Not ArrayIsInString(element.Name, SuffixStringToExclude) Then '
      If IsNameRefertoSheet(aWS, element) Then
        DNRArray(UBound(DNRArray)) = element.Name
        ReDim Preserve DNRArray(1 To UBound(DNRArray) + 1) As String
      End If
    End If
  Next element

  ' clean exit
  If UBound(DNRArray) > 1 Then
    ReDim Preserve DNRArray(1 To UBound(DNRArray) - 1) As String
    DNRGetNames = DNRArray
  Else
    DNRGetNames = Empty
  End If

End Function

Returned DNR by the GetDNR function :

Slai · Accepted Answer · 2018-02-07T11:12:06.317

1

RefersTo has to be A1 style notation string (for example "=A1,B2") :

Set MergedRange = aWS.Range(Join(DNRnames, ","))
aWS.Names.Add DNRprefix & "All", "=" & MergedRange.Address

Range.Address is limited to 255 characters, so it might need to be created before merging the ranges.

For Workbook scope Named Range:

MergedRange.Name = DNRprefix & "All"

edited Feb 07 '18 at 11:12

answered Feb 07 '18 at 00:45

Slai

22,144
5
45
53

Thx Slai ; I did not get it 100%. The 2nd line as is `aWS.Names.Add...` does not compile and I'm not skilled enough with VBA to find what's wrong. – hornetbzz Feb 07 '18 at 05:09
@hornetbzz I can't see anything that may not compile in the `aWS.Names.Add DNRprefix & "All", "=" & MergedRange.Address` line, but if you can give a bit more detail I might be able to think of something. I tried it on Excel 2016, and it worked with `Sheet1.Names.Add "ab", "=A1,B2"` – Slai Feb 07 '18 at 11:16
the line `aWS.Names.Add Name:=DNRprefix(0) & "_All", "=" & MergedRange.Address(0, 0)` drives to this compile error : `expected : named parameter` – hornetbzz Feb 07 '18 at 11:48
remove the `Name:=` parameter name or include the `RefersTo:=` parameter name too. – Slai Feb 07 '18 at 11:51
Oups yes, sorry. So it compiles but still does not keep dynamic, checking the result in the name manager. – hornetbzz Feb 07 '18 at 11:58
CAVEATS : Sounds the merge, with whatever method is used, results in a static range. So I guess I have to define the "merge" by an offset over the resulted static range "DNRprefix(0)&_All" - since this is a rectangular range - , as I did for each single column named range in this worksheet. It will be far more simple to add a 1-liner to achieve that, rather than getting more headhaches in finding a smart solution. – hornetbzz Feb 07 '18 at 12:23
1

@hornetbzz sorry, I think I may have misunderstood the question. I can't find a way to keep the combined named range dynamic even with something like `, RefersTo:="=Name1,Name2"` or `, RefersTo:="=INDIRECT(""Name1,Name2"")"` or `, RefersTo:="=EVALUATE(""Name1,Name2"")"`. I am guessing a VBA UDF Function that refers to something like `Range("Name1,Name2")` might be an option. – Slai Feb 07 '18 at 13:00
yes this is also my opinion. I'll need it anyway otherwise the easy way to add a 1-liner offset would force to keep the merged range rectangular, then loosing the point of interest. – hornetbzz Feb 07 '18 at 13:21

VBA Merge of Dynamic Named Ranges results into a static range i/o keeping dynamic

1 Answers1