Search code examples
arraysvbaexcelnamed-ranges

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

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

Solution

  • The issue was that dynamic named range formula determines how long the range is using the COUNT function and this doesn't work for text so it was returning as an error, which VBA couldn't handle. Upon changing the dynamic named range formula to use the COUNTA function it was able to read the range and store in it an array and the issue was resolved.