3

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 :)

Improve this question

3 Answers 3

Reset to default
3

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

Dim siteCount as Long siteCount = Evaluate("SiteCount")
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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")
Improve this answer

2 Comments

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
Good point - that will teach me to add the variable part in after testing! I'll edit the post.
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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.