1

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

Improve this question
4
  • Have a good look around. I have seen more questions like this. For example: stackoverflow.com/questions/19038697/… Commented Jun 28, 2018 at 12:28
  • 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. Commented Jun 28, 2018 at 13:25
  • It should still work though as per yours above. Commented Jun 28, 2018 at 13:58
  • 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"). Commented Jun 28, 2018 at 14:06

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Because I know my ranges are only one column in width and won't change from that I think I can use the For Each x In y format, right? The code ran properly without needing to take into account the 2D format.
Whenever you read a range from the sheet into an array, which is effectively what you are trying to do. then it will be a 2D array and looped as shown.
If instead you want to use a Range object, which you can then iterate over with a For Each you would have as per edit I will add....
I haven't had any issues iterating through an array with a For Each loop and that's how I've seen it presented elsewhere on StackOverflow. Is this just proper VBA practice?
No. What you have done is valid. This is just another way of doing things.
1

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.

Improve this answer

3 Comments

This or Fruit is (other)sheet scoped and not workbook scoped. + Why mention the Worksheet in the call to the defined name: Fruits = ThisWorkbook.Range("Fruit")
I checked and Fruit is defined. Are you not able to reproduce the error otherwise? I mentioned the worksheet in the call despite it being workbook-scoped because without that I get Run-time error '1004': Method 'Range' of object '_Global' failed and after reading around it seemed the best way to solve the issue is to specify the worksheet on which the range is located.
@JosephC you were on the right track with checking named ranges - my dynamic formula uses COUNT instead of COUNTA and so was returning an error for any text-based lists.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.