How to handle object declaration in VBA (Error 91)

Question

I'm stuck in VBA and I couldn't find a good answer in the other questions related to error 91. I want to create an object and store variables and arrays inside that object. I tried an approach like I would do in js:

Dim block As Object
...
Set block = Nothing
block.Name = "Unbekannter Prüfblock"
block.Awf = "Unbekannter Anwendungsfall"
block.Conditions = Array()
block.Checks = Array()

I use the "Set block = Nothing" because I will use it multiple times in a loop.

But all I get is error 91 - Object variable not set

How can I set the object? Do I really have to declare everything in vba? Isn't there a "stop annoying me with declaration notices" toggle? ;-)

Update

Thank you all so much for the detailed answers!

As suggested I created a class for "block" and also a class for "condition" and "check". Block for example:

Option Explicit

Public name As String
Public awf As String
Public conditions As Collection
Public checks As Collection

Then I use it inside my code like this:

Dim bl As Block
Dim co As Condition
Dim ce As Check

Set bl = New Block
bl.name = ws.Range("B" & i).value
bl.awf = ws.Range("B" & i).value

Set co = New Condition
co.attr = ws.Range("B" & i).value
co.value = ws.Range("C" & i).value
bl.conditions.Add co

Answer 1

VBA isn't Javascript; objects and their members cannot be created inline, they need a class definition.

When you make a member call against an object, you refer to it by name, and whenever that name refers to a null reference (Nothing) you'll get error 91.

To fix it, you need to ensure every member call is made against a valid object reference. Using the Set keyword you can assign such a reference, and to create a new instance of an object you can use the New keyword followed by the name of the class that defines the type you want a new instance of:

Dim Block As Object
Block.Something = 42 ' Error 91

Set Block = New SomeClass ' set reference 
Block.Something = 42 ' OK

Note that because the object is declared As Object, every member call is late-bound (resolved at run-time); if the member doesn't exist (or if there's a typo), you'll get error 438 at run-time.

You can move this error to compile-time with early binding by using a more specific data type for the declaration:

Dim Block As SomeClass

Because the members of SomeClass are known at compile-time, the IDE will now provide you with a member completion list when you type up a member call, and typos will no longer be valid at compile-time: strive to remain in the early-bound realm whenever possible! Note: As Variant (explicit or not) is also similarly late-bound.

So we add a new class module and call it SomeClass and we add a couple of public fields:

Option Explicit
Public Name As String
Public Case As String 
Public Condition As Variant
Public Check As Variant

And now you can create and consume a new instance of that class, and add instances of it to a collection to process later (note: you can't do that with a UDT/Type).

The VBIDE settings have an annoying option ("automatic syntax check", IIRC) that immediately pops a message box whenever there's a compilation error on the current line; uncheck it (invalid lines will appear in red, without a message box), but do have the "require variable declaration" setting checked: it will add Option Explicit to every module, and that will spare you from a number of easily avoidable run-time errors, moving them to compile-time.

Answer 2

In JS, you can add properties (together with values) on the fly to an object. That's not possible in VBA (and most other languages).

Your declaration Dim block As Object is defining a variable that is supposed to point to an Object. But it isn't pointing to anything yet, per default it is initialized with Nothing, which is, literally, nothing, and has neither properties nor methods, it's just a placeholder to signal "I don't point to anything yet". Furthermore, Object cannot be instantiated.

in VBA, you assign something to an object variable with Set (this is different to most other languages). If you want to create a new object, you use the keyword New.

However, before you do that, you need to know what kind of object (which class) you need. This can be an existing class, eg a Worksheet or a Range in Excel, or it can be an own defined class (by creating a new class module in your code). In any case, you need to define the properties and the methods of that class. Considering the most simple class module Class1 (of course you should think about a more usefull name):

Option Explicit
Public name as String
Public case as String

(I will not start to talk about private members and getter and setter).

You then write

Dim block As Class1
Set block = New Class1
block.name = "Unbekannter Prüfblock"

(But block.data = "Hello world" will not be possible as data is not a member of your class.) Big advantage of this attempt is that the compiler can show you when you for example mistyped a property name before you even start your code (Debug->Compile). In JS, you will get either a runtime error or a new property on the fly - hard to find nasty bugs.

If you later need a new (empty) object, just create a new object using New. If the old object is not referenced anywhere else, the VBA runtime will take care that the memory is freed (so no need to write Set block = Nothing).

In case you really don't know the properties in advance (eg when you read XML or JSON files or a key-value list from an Excel sheet...), you can consider to use a Collection or a Dictionary. Plenty of examples on SO and elsewhere.

One remark to block.Condition = Array(). Arrays in VBA are not really dynamic, you cannot add or remove entries easily during runtime. In VBA, you have static and dynamic arrays:

Dim a(1 to 10) as String   ' Static array with 10 members (at compile time)
Dim b() as String          ' Dynamic array.

However, for dynamic members you need to define how many members you need before you write something into it, you use the ReDim statement for that. Useful if you can calculate the number of members in advance:

Redim b(1 to maxNames)     ' Dynamic array, now with maxNames members (whatever maxNames is)

You can change the array size of a dynamic array with Redim Preserve, but that should be an exception (bad programming style, inperformant). Without Preserve, you will get a new array, but the former data is lost.

Redim Preserve b(1 to maxNames+10)  ' Now with 10 more members.

If you really don't know the number of members and it can change often during runtime, again a Collection or a Dictionary can be the better alternative. Note that for example a Dictionary can itself a Dictionary as value, which allows to define Tree structures.

Answer 3

Regarding your issue adding to the collection: You need to add this code to your class module "Block" - only then you can add objects to the collections

Private Sub Class_Initialize()
Set conditions = New Collection
set checks = new Collection
End Sub