Error 91 in class function

Question

Using Access 2010, I'm gathering information and dropping it on an Excel spreadsheet. When I run the code below, I'm getting

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

in my class on this line Set Cci = ChartColorItems(ColorID) in Public Function GetRGB(ByRef ColorID As String) As Integer

The 'ChartColors' class:

Option Compare Database
Option Explicit

Private pChartColorItems As Collection

Public Property Get ChartColorItems() As Collection
  Set ChartColorItems = pChartColorItems
End Property
Public Property Set ChartColorItems(ByRef lChartColorItem As Collection)
  Set pChartColorItems = lChartColorItem
End Property

Public Function GetRGB(ByRef ColorID As String) As Integer
  Dim Cci As ChartColorItem
  Dim x As Integer

'---------------------------------------------------
'Error happens here:      
  Set Cci = ChartColorItems(ColorID)
'---------------------------------------------------
  x = RGB(Cci.Red, Cci.Green, Cci.Blue)
  GetRGB = x
  Set Cci = Nothing

End Function

Private Sub Class_Initialize()

  Dim Cci As ChartColorItem
  Dim Colors As Collection

  Set Colors = New Collection

  Set Cci = New ChartColorItem
  Cci.Red = 149
  Cci.Green = 55
  Cci.Blue = 53
  Cci.ColorID = "Pie1"
  Colors.Add Cci, Key:=Cci.ColorID
  Set Cci = Nothing

  Set Cci = New ChartColorItem
  Cci.Red = 148
  Cci.Green = 138
  Cci.Blue = 84
  Cci.ColorID = "Pie2"
  Colors.Add Cci, Key:=Cci.ColorID
  Set Cci = Nothing

End Sub

and the ChartColorItem class:

Option Compare Database
Option Explicit

Private pColorID As String
Private pRed As Integer
Private pGreen As Integer
Private pBlue As Integer

Public Property Get ColorID() As String
  ColorID = pColorID
End Property
Public Property Let ColorID(ByRef x As String)
  pColorID = x
End Property

Public Property Get Red() As Integer
  Red = pRed
End Property
Public Property Let Red(ByRef x As Integer)
  pRed = x
End Property

Public Property Get Green() As Integer
  Green = pGreen
End Property
Public Property Let Green(ByRef x As Integer)
  pGreen = x
End Property

Public Property Get Blue() As Integer
  Blue = pBlue
End Property
Public Property Let Blue(ByRef x As Integer)
  pBlue = x
End Property

When I debug, the code steps through the ChartColorItems() getter just fine, the error happens after the End Function, and drops me on the line noted above.

This is very similar to some code I wrote earlier this week, the main difference is that I'm populating my ChartColors by using the Class_Initialize sub, since I'm trying to store off a fixed set of colors, whereas my earlier code was gathering data and inserting it into the class in a more 'normal' way.

Answer 1

You defined the private collection at the module level as pCharColorItems, but you never initialize it in the class' intialize method. Instead, you use a locally scoped Colors collection variable.

Private Sub Class_Initialize()

  Dim Cci As ChartColorItem
  Dim Colors As Collection

  Set Colors = New Collection

You need to use pChartColorItems instead.

Private Sub Class_Initialize()

  Dim Cci As ChartColorItem
  Dim Colors As Collection

  Set pChartColorItems = New Collection

  Set Cci = New ChartColorItem
  Cci.Red = 149
  Cci.Green = 55
  Cci.Blue = 53
  Cci.ColorID = "Pie1"
  pChartColorItems.Add Cci, Key:=Cci.ColorID
  Set Cci = Nothing

  Set Cci = New ChartColorItem
  Cci.Red = 148
  Cci.Green = 138
  Cci.Blue = 84
  Cci.ColorID = "Pie2"
  pChartColorItems.Add Cci, Key:=Cci.ColorID
  Set Cci = Nothing

End Sub

But there's another bug on this line of GetRGB.

 x = RGB(Cci.Red, Cci.Green, Cci.Blue)

You declared x as an Integer, when the RGB function returns a long. The value of "Pie1" causes an overflow error.