Excel VBA: Application or object-defined error when storing named range in an array

Question

I am trying to store the values from a named range into an array and am having trouble with named ranges that have text in them rather than numbers. (These named ranges are dynamic so I'd like to be able to use named ranges to allow for additional values to be added to the lists and the macro to incorporate them.)

For purposes of an example I have two named ranges: Fruit and Quantity. Below are the values in each range.

Fruit: Apples, Oranges, and Bananas (located in B3:B5)

Quantity: 3, 4, and 5 (located in C3:C5)

Below is the code I came up with to store Fruit in an array.

Sub FruitArray()
  Dim Fruits As Variant
  Dim Fruit As Variant

  Fruits = ThisWorkbook.Worksheets("Inventory").Range("Fruit") ' Run-time error '1004': Application-defined or object defined error

  For Each Fruit In Fruits
    Debug.Print Fruit
  Next
End Sub

When I run this code I get "Run-time error '1004': Application-defined or object defined error" and the debugger highlights the Fruits = ThisWorkbook.Worksheets("Inventory").Range("Fruit") line.

When I run nearly identical code for Quantity it works and prints 3, 4, 5 in the Immediate window.

Sub QuantityArray()
  Dim Quantities As Variant
  Dim Quantity As Variant

  Quantities = ThisWorkbook.Worksheets("Inventory").Range("Quantity")

  For Each Quantity In Quantities
    Debug.Print Quantity
  Next
End Sub

At first I thought the issue was that I couldn't store text in arrays from a range but when I specify the actual range in the code rather than the named range it works and prints Apples, Oranges, and Bananas.

Sub FruitArray()
  Dim Fruits As Variant
  Dim Fruit As Variant

  Fruits = ThisWorkbook.Worksheets("Inventory").Range("B3:B5")

  For Each Fruit In Fruits
    Debug.Print Fruit
  Next
End Sub

Is there something I am missing to be able to store the text-based named range values in an array?

Thank you

Have a good look around. I have seen more questions like this. For example: stackoverflow.com/questions/19038697/… — JvdV
I saw that one and based the overall structure of my code from theirs but changed it to a For Each x In y format. — Peter
If you are using a named Ranges, Declare it as a Range variable not variant. And use Set Fruits = ThisWorkbook.Worksheets("Inventory").Range("B3:B5"). — BLitE.exe

QHarr QHarr · Accepted Answer · 2018-06-28T12:24:20

As noted in comments: Check the named range actually exists first.

More general observations on working with the named range:

For Variant (I am thinking array)

Fruits = ThisWorkbook.Worksheets("Inventory").Range("Fruit").Value

Remember a range read in from the sheet is 2D not one. And you will loop from the LBound to the UBound.

Example:

Sub test()
    Dim Fruits()
    Fruits = ThisWorkbook.Worksheets("Inventory").Range("Fruit").Value
    Dim i As Long, j As Long
    For i = LBound(Fruits, 1) To UBound(Fruits, 1)
        For j = LBound(Fruits, 2) To UBound(Fruits, 2)
            Debug.Print Fruits(i, j)
        Next j
    Next i
End Sub

For Range object:

If instead you want to actually work with a Range object so you can use a For Each Loop then you want the following.

Option Explicit
Public Sub FruitArray()
  Dim Fruits As Range, Fruit As Range
  Set Fruits = ThisWorkbook.Worksheets("Inventory").Range("Fruit")

  For Each Fruit In Fruits
    Debug.Print Fruit '<== This takes advantage of default member .Value
  Next
End Sub

Excel VBA: Application or object-defined error when storing named range in an array

3 Answers

More general observations on working with the named range: