Filling a array of custom objects and returning it in an Excel macro

Question

I made a custom class and I'm trying to make an array of objects with it but I get an error "Object variable or with block variable not set" when I try to add my object to the array.

Macro :

Option Explicit

Public Function ArrayLen(arr As Variant) As Integer
    ArrayLen = UBound(arr) - LBound(arr) + 1
End Function

Function loadRegisteredFood() As ClsRegisteredFood()
    ' Load all the registered food in memory
    Worksheets("Foods").Activate
    Dim dataRegisteredFood As Range
    Set dataRegisteredFood = Range("RegisteredFoodTable")

    Dim registeredFoods() As ClsRegisteredFood
    ReDim registeredFoods(dataRegisteredFood.Rows.Count - 1)

    Dim x As Integer
    For x = 1 To dataRegisteredFood.Rows.Count
         Dim registeredFood As New ClsRegisteredFood
         registeredFood.Name = dataRegisteredFood.Cells(x, 1).Value
         registeredFood.Prot = dataRegisteredFood.Cells(x, 2).Value
         registeredFood.Fat = dataRegisteredFood.Cells(x, 3).Value
         registeredFood.Carbs = dataRegisteredFood.Cells(x, 4).Value
         registeredFood.Netcarbs = dataRegisteredFood.Cells(x, 5).Value
         registeredFood.Kcal = dataRegisteredFood.Cells(x, 6).Value
         registeredFood.Portion = dataRegisteredFood.Cells(x, 7).Value
         registeredFoods(x - 1) = registeredFood
    Next x

    Dim length As Integer
    length = ArrayLen(registeredFoods)
    MsgBox length

    Dim rf As Variant
    For Each rf In registeredFoods
        MsgBox rf.Name & " contient " & rf.Kcal & " Kcal"
    Next rf

    loadRegisteredFood = registeredFoods
End Function

Sub test()
    Dim registeredFoods() As ClsRegisteredFood
    registeredFoods = loadRegisteredFood()
    ' printRegisteredFood (registeredFoods)
End Sub

Class :

Public Name As String
Public Prot As Double
Public Fat As Double
Public Carbs As Double
Public Netcarbs As Double
Public Kcal As Double
Public Portion As String

The workbook :

https://www.dropbox.com/s/jdoa31jo7f1qz85/test.xlsm?dl=0

The error appear on this line registeredFoods(x - 1) = registeredFood

What could be causing this?

Thanks.

Since the array is an array of objects, you must Set the array items Set registeredFoods(x - 1) = registeredFood. — Axel Richter
– Axel Richter, Commented Mar 31, 2018 at 8:38
indeed I forgot that, you should put an awser so I can accept it — sliders_alpha
– sliders_alpha, Commented Mar 31, 2018 at 9:22
@AxelRichter I spoke too fast, in the macro the array contains 14 times the last food line, however in the function the array is correct — sliders_alpha
– sliders_alpha, Commented Mar 31, 2018 at 10:41

Axel Richter · Accepted Answer · 2018-03-31 11:19:56Z

The error you are facing comes because of the missed Set in Set registeredFoods(x - 1) = registeredFood. Since the array is an array of objects, you must using Set for setting the array items.

Also while using Dim registeredFood As New ClsRegisteredFood registeredFood is not a new created ClsRegisteredFood object if called multiple times. Dim is always only a declaration. It only creates a new object if registeredFood is not an object already but is Nothing.

The Dim registeredFood As New ClsRegisteredFood is the same as

Dim registeredFood As ClsRegisteredFood
If registeredFood Is Nothing Then Set registeredFood = New ClsRegisteredFood

So you are always overwriting the same object in the for loop.

So do changing your code like:

...
    Dim x As Integer
    Dim registeredFood As ClsRegisteredFood
    For x = 1 To dataRegisteredFood.Rows.Count
         Set registeredFood = New ClsRegisteredFood
...
         Set registeredFoods(x - 1) = registeredFood
    Next x
...

Collectives™ on Stack Overflow

Filling a array of custom objects and returning it in an Excel macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related