CODEWITHSUNDEEP
arraysvbaexcelnamed-ranges
Peter
Peter

Reputation: 113

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

Upvotes: 1

Views: 1214

Answers (3)

Peter
Peter

Reputation: 113

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.

Upvotes: 0

QHarr
QHarr

Reputation: 84465

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

Upvotes: 2

JosephC
JosephC

Reputation: 929

I can reproduce the error when I don't define the named range: Fruit. (Check spelling)

  1. Go to: Formulas Tab

  2. Open: Name Manager

  3. Ensure: "Fruit" is a named range.

Quantity code probably works because that named range is defined.

Upvotes: 1

Related Questions