CODEWITHSUNDEEP
excelvba
Lievcin
Lievcin

Reputation: 938

workaround named range character limit

I'm building a graph, where the series are named ranges. It all works fine as I have a dropbox, where I select the customer, and it loops through the data, creating the string for the named range and edits it, refresh the graph, voila!

Now, the problem is that it seems I have a limit of 255 characters for this string, and if a customer has a lot of records, then it errors.

Has anyone come across a similar issue or knows a workaround?

For i = 8 To lRow
    If Sheets("Data").Cells(i, 4).Value = cmbInst.Value Then
        nRangeTrade = nRangeTrade & "Data!$A$" & i & ","
        nRangeSettle = nRangeSettle & "Data!$C$" & i & ","
    End If
Next

Value of nRangeTrade :

Data!$A$85,Data!$A$97,Data!$A$98,Data!$A$156,Data!$A$157,Data!$A$308,Data!$A$309,Data!$A$464,Data!$A$473,Data!$A$474,Data!$A$478,Data!$A$490,Data!$A$541,Data!$A$546,Data!$A$633,Da"

as you can see, this is where the problems kick in.

Upvotes: 1

Views: 5212

Answers (3)

hymced
hymced

Reputation: 688

Here is an example of how to store a long string/text (>255 characters) in a Name, using smaller string/text parts, a formula, and the concatenation operator (&):

Prerequisite : one "abcabc" name created beforehand

Sub Name_LongText()

strabc$ = "abcdefghijklmnopqrstuvwxyz"

strLongText$ = Replace(String(10, "X"), "X", strabc & ";")

'aaa = Split("") 'aaa(0 To -1)
arrLongText = Split(strLongText, ";") 'the array returned by the Split function is always 0-based, no matter the Option Base 0/1 instruction

strLongText = ""
i = 0
    strLongText = "="
    strLongText = strLongText & Chr(34)
    strLongText = strLongText & arrLongText(i) & ";"
    strLongText = strLongText & Chr(34)
For i = 1 To UBound(arrLongText, 1) - 1
    strLongText = strLongText & "&"
    strLongText = strLongText & Chr(34)
    strLongText = strLongText & arrLongText(i) & ";"
    strLongText = strLongText & Chr(34)
Next

ThisWorkbook.Names("abcabc").Value = strLongText
ThisWorkbook.Names("abcabc").RefersTo = strLongText

Debug.Print ThisWorkbook.Names("abcabc").Value
Debug.Print Len(ThisWorkbook.Names("abcabc").Value)

arrLongText = Split(ThisWorkbook.Names("abcabc").Value, """&""")

strLongText = Join(arrLongText, "")

Debug.Print strLongText

End Sub

Upvotes: 0

user688334
user688334

Reputation:

How about something like this

Sub test()
Dim nRangeTrade As Range, nRangeSettle As Range
lrow = 1000
For i = 8 To lrow
    If Sheets("Data").Cells(i, 4).Value = cmbInst.Value Then

        If nRangeTrade Is Nothing Then
            Set nRangeTrade = Sheets("Data").Range("A" & i)
        Else
            Set nRangeTrade = Application.Union(nRangeTrade, Sheets("Data").Range("A" & i))
        End If

        If nRangeSettle Is Nothing Then
            Set nRangeSettle = Sheets("Data").Range("C" & i)
        Else
            Set nRangeSettle = Application.Union(nRangeSettle, Sheets("Data").Range("C" & i))
        End If
    End If
Next i
'nRangeTrade.Select
Sheets("Data").Names.Add Name:="nRangeTrade", RefersTo:=nRangeTrade
Sheets("Data").Names.Add Name:="nRangeSettle", RefersTo:=nRangeSettle
End Sub

Upvotes: 3

Charles Williams
Charles Williams

Reputation: 23520

A Named Range can refer to other named ranges, so you could build it in several name blocks and then use a master Name to refer to the sub-blocks

Upvotes: 1

Related Questions