VBA to create a named range

[robprager]

Hi everyone, I'm a VBA newbie and trying to learn more

I'm running Windows 7 and Office 2013.

I read this page, http://www.mrexcel.com/forum/excel-...l-basic-applications-define-named-ranges.html, but the code that was provided isn't exactly the same as what I am after.

I have the following scenario on a worksheet (see below), except in my specific case, I have about 70 different combinations of named ranges to create, so I have populated until row 70. I'm looking for a macro that will create a named range by taking the text string in A1, and making that the named range for the range reference in B1 etc.

	A	B
1	NamedRange1	Sheet1:Sheet3!$A$1:$A$5
2	NamedRange2	Sheet1:Sheet3!$A$6:$A$10
3	NamedRange3	Sheet1:Sheet3!$A$11:$A$15

<tbody>
</tbody>

Thanks in advance to anyone who can help me out

 

[shg]

Welcome to the board.

3D ranges can only be used by the few functions that accept them in the user interface. They cannot be used at all in VBA, and you cannot create a named 3D range.

 

[robprager]

Thanks for your prompt reply! Is there no way I could use this cheat ?

http://www.mrexcel.com/forum/excel-questions/458581-visual-basic-applications-3d-name-range.html

 

[Domenic]

Actually, it looks like the Evaluate method can be used for a 3D reference...

x = Application.Evaluate("SUM(NamedRange1)")

To create the named ranges, try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] CreateNamedRanges()

    [COLOR=darkblue]Dim[/COLOR] sName           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow         [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i               [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] LastRow
        sName = Cells(i, "A").Value
        [COLOR=darkblue]If[/COLOR] Len(sName) > 0 [COLOR=darkblue]Then[/COLOR]
            sName = Replace(sName, " ", "_")
            ActiveWorkbook.Names.Add Name:=sName, RefersTo:="=" & Cells(i, "B").Value
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
    MsgBox "Completed...", vbInformation
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Note that any spaces within the text string used to name the range will be replaced by an underscore. Otherwise, an error would occur.

Hope this helps!

 

[shg]

That thread described creating a named string, and then parsing it to extract valid range references. Not knowing how you intend to use them, I don't know if you can make that work.

@ Domenic: Not sure what that buys you over just entering the sum formula in the UI.

 

[robprager]

Thanks Domenic

Where abouts do I enter the code x = Application.Evaluate("SUM(NamedRange1)")

 

[Domenic]

@ shg

Yeah, I'm not sure either. You had already mentioned that it could be used within a worksheet formula. So I thought I would offered it as a possibility, since I wasn't sure how it would be used.

@ robprager

You're very welcome!

Where are you planning to use the named ranges? Within a worksheet? Or within VBA code?

 

[robprager]

Hi both

Let me explain in some more detail what I'm trying to achieve

The reason why I am using a 3D named range is because I have about 20 tabs with the exact same layout but with different information, which I am trying to capture as a whole. They are forecasting tabs for different projects, but each project is staffed by the same roles, project managers, analysts, etc. Each named range I am creating is to cover all of the project tabs, but for one of these specific roles, and also for a time period. So, for example, I already have a named range of PM1January2015, and for example, this could refer to Project1:Project20!A1:AA1. PM1February2015 would then be Project1:Project20!AAB1:AAZ1 etc.

Now, I have an analysis tab where I am using the 3d named ranges in a pretty simple form. Just SUM(<Namedrange>), so for instance SUM(PM1January2015). So in answer to your question Domenic, they are for use in a worksheet.

The reason I wanted to use a VBA was because I have about 100 more of these named ranges to create and they all follow a similar pattern. I'm adept enough to use a formula to populate a new sheet in the spreadsheet whereby in Colm A it has all the names, and in Col B it has the references, but was hoping a VBA could somehow zap them to become named ranges.

 

[Domenic]

In that case, once you've run the code to create the named ranges, you can simply use the worksheet formula...

=SUM(PM1January2015)

 

[shg]

Well, in that case, Domenic's suggestion leaps to the fore:

Function MySum(sRng As String) As Double
    Application.Volatile
    MySum = Evaluate("Sum(" & sRng & ")")
End Function

Row\Col	A​	B​	C​
2​	Sheet2:Sheet4!A2	6​	B2: =MySum(A2)

Forgive me for ever doubting you, Domenic.