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

 

[Domenic]

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)

<TBODY>
</TBODY>

Forgive me for ever doubting you, Domenic.

Yeah, this is a much better solution. It avoids having all those defined names, and it can be copied down.

Nice one shg!

 

[shg]

Thanks, but it was your idea ...

 

[Domenic]

Thanks, but it was your idea ...

Teamwork...

 

[robprager]

Teamwork...

So with this solution you guys are proposing, would it work a bit like an INDIRECT formula, in so far as it would take the range in the cell, and do the sum based on that range?

Is there a disadvantage to having lots of named ranges in a spreadsheet?

 

[Domenic]

So with this solution you guys are proposing, would it work a bit like an INDIRECT formula, in so far as it would take the range in the cell, and do the sum based on that range?

That's exactly right. So, basically, once you've added the code for the custom function in a regular module (Alt+F11 > Insert > Module > Copy/Paste > Alt+Q), based on your sample data, you could simply enter the following formula in B1, and copy down:

=MySum(A1)

So there would be no need to individually enter the formula for each row, as required with named ranges. The only drawback is that it's necessarily a volatile function. So this means that anytime there's a change in any cell within any worksheet in the workbook, the function re-calculates. And these re-calculations can slow down your worksheet.

 

[shg]

Domeic, I wish you were around when I was in the early stages of learning Excel.

 

[Domenic]

Domeic, I wish you were around when I was in the early stages of learning Excel.

Why? You've done really well. You're very knowledgeable.

 

[shg]

A little generalization:

Function My3DFunc(sFunc As String, sRng As String) As Double
    Application.Volatile
    My3DFunc = Evaluate(sFunc & "(" & sRng & ")")
End Function

E.g.,

=My3DFunc("sum", "Sheet2:Sheet3!A2")

... where "sum" could be replaced by any of the single-argument functions that accept 3D references (and, or, count, counta, ...)

 

[robprager]

One other question guys. Would it be possible to create a VBA that returns the names of tabs that are between two stated tabs? So lets say we have 20 tabs, the first being Sheet One, the last been Sheet 20. Could a VBA return the names of the tabs in between?

 

[Domenic]

Maybe something like this...

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

    [COLOR=darkblue]Dim[/COLOR] aSheetNames()           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFirstSheet             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sLastSheet              [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] SheetCount              [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i                       [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    sFirstSheet = "Sheet5" [COLOR=green]'change the name of the first sheet accordingly[/COLOR]
    sLastSheet = "Sheet12" [COLOR=green]'change the name of the last sheet accordingly[/COLOR]
    
    [COLOR=darkblue]ReDim[/COLOR] aSheetNames(1 [COLOR=darkblue]To[/COLOR] Sheets.Count)
    
    SheetCount = 0
    [COLOR=darkblue]For[/COLOR] i = Sheets(sFirstSheet).Index + 1 [COLOR=darkblue]To[/COLOR] Sheets(sLastSheet).Index - 1
        SheetCount = SheetCount + 1
        aSheetNames(SheetCount) = Sheets(i).Name
    [COLOR=darkblue]Next[/COLOR] i
    
    [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aSheetNames(1 [COLOR=darkblue]To[/COLOR] SheetCount)
    
    Worksheets.Add before:=Sheets(1)
    
    Range("A2").Resize(UBound(aSheetNames)).Value = Application.Transpose(aSheetNames)
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!