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.

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.

Excel VBA: Return a value from a Named Range/Formula (ERROR , Return a value from a Named Range/Formula (ERROR 1004, 2015, or 13) a value from a Named Formula in Excel VBA (a simple Named Range works as  Now by using Range object, I can access this range. Code: Sub Error1004_Example() Range("Headings").Select End Sub

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

Formula = "=IF(" Application-defined or object-defined error, Excel VBA: Return a value from a Named Range/Formula (ERROR 1004, 2015, or 13) - vba. Are you evaluating the entire named range or just the relative reference? I just tested your formula as is and got #Value because Networkdays is looking for a one to one comparison. level 2

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.

VBA Runtime Error 1004 while assigning formula to a cell, Hello, I am receiving the Run-time error '1004': Application-defined or What is odd is if I copy the formula and put it directly in to cell W2 (and replace wbData.​Name with the workbook name, i.e. "program report Joined: Feb 13, 2015 up the quotation marks for the "" value halfway through the formula. How to Fix the Excel Errors: #VALUE, #REF, and #NAME (Easily). Written by co-founder Kasper Langmann, Microsoft Office Specialist.. If you have spent much time working with formulas in Microsoft Excel, you have run into a few errors.

Posts by goldbeck09, HasFormula Then cel.Formula = "=" & cel.Value & "/USD_ConversionRate" >> VBA Runtime Error 1004 “Application-defined or Object-defined  Dim R As Range Set R = Range("A1") If IsError(R.Value) = True Then Select Case R.Value Case CVErr(xlErrValue) Debug.Print "#VALUE error" Case CVErr(xlErrDiv0) Debug.Print "#DIV/0 error" Case CVErr(xlErrName) Debug.Print "#NAME? error" Case Else Debug.Print "Some other error" End Select End If

Writing To A Range Using VBA – Daily Dose of Excel, Return a value from a Named Range/Formula (ERROR 1004, 2015, or 13) '##​Sheet1 module## Sub test() 'ERROR 1004: application-defined or  A named range in Excel is nothing more than a cell or range of cells that has been given a friendly, descriptive name. Naming your ranges allows you use easily recognizable names in your formulas instead of cell addresses. For instance, say that you have line-item sales in cells A1:A25 and you have a percent …

Array formulas and functions in Excel, External dynamic named range formula returning #value when workbook closed · goldbeck09 · Mar goldbeck09 · Jul 15th 2015. Hey all,. I have my code, but the very last step to paste the data is giving me a run-time error '1004'My macro gives the error message 'Run-time error '13': Type mismatch . Even if I put in C$10:C$13 instead of the Name range I still get the #VALUE! error, it must be something very simple but I can't see it. I have attached my workbook so you can see what I'm trying to do, This being my first post I hope I've done everything right. Driver01Apr18 is C$4:C$8 Driver01May18 is C$10:C$13

Comments
  • ohhhhhhhhhhh. I did not realize that ROW returns an Array. Huh.
  • Thanks. That addresses the specific example of the problem but not the problem itself. I dumbed it down a little so as to be easily presented, and that answer doesn't really help what I'm actually doing (not your fault as you have no way of knowing that).