VBA Referring to named range

Question

I'm just starting to used named references within my VBA, but have hit a minor problem.

I have a named reference called "SiteCount" that counts the number of sites in a column

=COUNTA(Data!$B:$B)

It is saved as Workbook in scope and works when I use it in a cell as

=SiteCount

I thought I could use this in my code rather than replicate the calculation, however I can not save the value even just as

Sitecount = Range("SiteCount")

I have tried using the worksheet name as well, but I get the same 1004 "Method range of object global failed"

I'm guessing it's something very simple. but I can't figure it out. Advice is gratefully received :)

user2140173 · Accepted Answer · 2014-07-01 11:20:03Z

3

Evaluate() should do since it's a formula name not a named range

Dim siteCount as Long
siteCount = Evaluate("SiteCount")

answered Jul 1, 2014 at 11:20

user2140173

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

Comments

Rory · Accepted Answer · 2014-07-01 11:50:09Z

3

It's not a named range, it's a named formula, so you have to refer to it differently. You can use:

lSiteCount = [SiteCount]

note that the variable name must not be the same as the formula name!

or

SiteCount = Sheets("Data").Evaluate("SiteCount")

or

SiteCount = Application.Evaluate("SiteCount")

edited Jul 1, 2014 at 11:50

answered Jul 1, 2014 at 11:21

Rory

34.2k5 gold badges40 silver badges42 bronze badges

2 Comments

z̫͋ Over a year ago

Do not write SiteCount = [SiteCount] because when using [...], it will first look in the VBA environment if a variable has the same name. So [SiteCount] will actually refer to the variable SiteCount if it is defined. Evaluate doesn't have that problem

Rory Over a year ago

Good point - that will teach me to add the variable part in after testing! I'll edit the post.

Gary's Student · Accepted Answer · 2014-07-01 11:22:38Z

1

If you want to get the values of specific named formulas then:

Sub WhatsInAName()
    For Each n In ActiveWorkbook.Names
        If n.Name = "SiteCount" Then
            MsgBox n.Value & vbCrLf & Evaluate(n.Value)
        End If
    Next n
End Sub

answered Jul 1, 2014 at 11:22

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

VBA Referring to named range

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related