Store named ranges values as variable

Question

I am trying to store a named range like that

    Dim sValue As String
sValue = ThisWorkbook.Names("MyRange").Value

I got the range address instead of the values in the named range. What I am trying to do exactly is the following Suppose the named range is "MyRange" and it is for A1:C4 In a cell, if we typed equal sign and typed "MyRange" and then pressed F9, we got the values as array like that

{"Head1","Head2","Head3";1,2,3;4,5,6;7,8,9}

How to store that in variable string?

Thanks a lot. Using Variant is the same as the result is the address of the range not the values .. I need to store the same output that appears after pressing F9 in the worksheet formula and I have posted it above. — YasserKhalil
– YasserKhalil, Commented Apr 12, 2020 at 9:25
sValue = ThisWorkbook.Names("MyRange").RefersToRange.Value — HTH
– HTH, Commented Apr 12, 2020 at 9:30

HTH · Accepted Answer · 2020-04-12 09:58:18Z

3

to have a String variable formatted as {"Head1","Head2","Head3";1,2,3;4,5,6;7,8,9}, you can do as follows

    Dim sValue As String, r As Range
    For Each r In ThisWorkbook.Names("MyRange").RefersToRange.Rows
        sValue = sValue & Join(Application.Transpose(Application.Transpose(r.Value)), ",") & ";"
    Next
    sValue = "{" & Left(sValue, Len(sValue) - 1) & "}"

while to store a named range in an array is much simpler (and I'd say, easy to use):

Dim sValue As Variant
sValue = ThisWorkbook.Names("MyRange").RefersToRange.Value

edited Apr 12, 2020 at 9:58

answered Apr 12, 2020 at 9:51

HTH

2,0311 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

YasserKhalil Over a year ago

Thanks a lot for great help. At the end of your code I tried such a line a = Evaluate(sValue) but I got Error 2015 for the value of a in the Locals Window. Why?

HTH Over a year ago

You are welcome. I'm not an Evaluate() expert, but I'd say it would "eat" formulas, not arrays

SoftwareTester · Accepted Answer · 2020-04-12 09:35:52Z

0

I think you are looking for

sValue = ThisWorkbook.Names("MyRange"),name

to find all names on Activesheet:


Sub test_names()
    Dim wsName As String
    wsName = ActiveSheet.Name
    Dim nameRange As Variant
    For Each nameRange In ThisWorkbook.Names
        Set rngName = Range(nameRange)
        wsParentName = rngName.Parent.CodeName
If (wsParentName = wsName) Then
Debug.Print "Found range " & nameRange.Name
End If
    Next nameRange
End Sub

answered Apr 12, 2020 at 9:35

SoftwareTester

1,1101 gold badge11 silver badges28 bronze badges

1 Comment

YasserKhalil Over a year ago

Thanks a lot but this is irrelevant to the issue.

Collectives™ on Stack Overflow

Store named ranges values as variable

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related