CODEWITHSUNDEEP
vbaexcel
Ishnatal
Ishnatal

Reputation: 13

Object variable not set error?

I'm primarily a C++/C# programmer and very unfamiliar with VBA, so I'm not too certain what exactly the problem is with this code. It's throwing the following error:

"Run-time error '91': Object variable or With block variable not set."

That error is being thrown on the line in the FOR loop. The right side of the statement seems to be throwing the error. What exactly is the problem with that line, and how would I go about fixing it?

Here's the code snippet:

Option Explicit

Private gEmployees() As Employee

Const gLastNameStartingCell = "A4"
Const gNamesCountCell = "A1"

Const gNamesTab = "NamesTab"

Function BuildEmployeeNameArray()

    ' Declare all variables
    Dim iNameCount As Integer
    Dim wksActive As Object

    ' Counter
    Dim i As Integer

    ' Select the sheet with all the names
    Set wksActive = Sheets(gNamesTab)

    ' Get the number of names on the sheet
    iNameCount = wksActive.Range(gNamesCountCell)

    ' Resize the Array as appropriate
    ReDim gEmployees(0 To iNameCount - 1)

    ' Fill out the employee list
    For i = 0 To iNameCount - 1
        gEmployees(i).mLastName = wksActive.Range(gLastNameStartingCell).Offset(i, 0).Value
    Next i

End Function

Employee is a Class Module. Here's the relevant information from that file.

Option Explicit
Public mLastName As String

Private Sub Class_Initialize()

    ' Initialize variables
    mLastName = ""

End Sub

Upvotes: 0

Views: 336

Answers (2)

enderland
enderland

Reputation: 14185

You are missing the creation of the actual employee to add to the array.

Dim e as Employee
' Fill out the employee list
For i = 0 To iNameCount - 1
    'Create a new employee each time through the loop
    set e = new employee
    gEmployees(i)=e

    'set the last name appropriately 
    gEmployees(i).mLastName = wksActive.Range(gLastNameStartingCell).Offset(i, 0).Value
Next i

This will fix your problem.

The array never sets the type, either, the way you are doing this, so I'm not sure how you would have gotten any similar syntax to work in C++/C#.

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166885

ReDim gEmployees(0 To iNameCount - 1)

just creates an empty array with iNameCount slots - you have not populated each of those slots with an Employee object, so you can't set the mLastName property if the object isn't there.

Upvotes: 1

Related Questions