CODEWITHSUNDEEP
excelvbanamed-ranges
Rick
Rick

Reputation: 45231

Return a value from a Named Range/Formula (ERROR 1004, 2015, or 13)

I am having trouble returning a value from a Named Formula in Excel VBA (a simple Named Range works as expected).

Steps to Recreate

  1. Blank workbook

  2. Create the Named Range/Formula rowOffset equal to:

    =ROW(Sheet1!$A$2)-ROW(Sheet1!$A$1)

  3. rowOffset evaluates as expected (equal to 1)

However, I have been unable to figure out how to return the value of rowOffset inside of VBA.

Sample Code With Errors

'##Sheet1 module##
Sub test()
    'ERROR 1004: application-defined or object-defined error
    Debug.Print Me.Range("rowOffset").Value
    'Returns formula
    Debug.Print Me.Names("rowOffset").Value
    'ERROR 2015 in Immediate window
    Debug.Print Application.Evaluate(Me.Names("rowOffset"))
    'ERROR 13: type mismatch
    Debug.Print Application.Evaluate(Me.Names("rowOffset").Value)
End Sub

Why doesn't the above code work in this specific situation, but does work for a Named Range with the definition =$A$1 (the value of A1 is returned, as expected)?

EDIT: Thanks to Simoco for the (very simple!) answer. One trip through the locals window and I should have been able to figure this out on my own! haha.

Upvotes: 2

Views: 5082

Answers (3)

BCW
BCW

Reputation: 21

Thanks this helped me. I changed

Application.Evaluate(Me.Names("rowOffset").Value)

to

Application.Evaluate(ThisWorkbook.Names("rowOffset").Value)

This allows user to access named ranges that have no cell address within the speadsheet.

Upvotes: 0

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

First of all, interesting question!

Woking code:

Debug.Print Evaluate("rowOffset")(1)

or

Debug.Print Join(Evaluate("rowOffset"))

or

Debug.Print Evaluate("INDEX(rowOffset,1)")

or

Debug.Print [INDEX(rowOffset,1)]

Why Debug.Print Evaluate("rowOffset") is not working?
The reason is because ROW() actually returns array with one element rather than single value,
i.e. {1}, and all you need is to take it's first element or concatenate array using Join.

enter image description here

Upvotes: 2

Matt Klinker
Matt Klinker

Reputation: 773

It looks like the following answer should give you the details needed to get it done. Surprised there's not something simpler, but hope it helps!

Excel listing named range in a worksheet and get the value

Upvotes: 1

Related Questions