the same question, revisisted

i hate to post this much code but i have no idea how to do this and i'm absolutley desperate. this thing is due tommorow and i have to know how to get this class to work right. all i need is one thing. so here's my "Sandwich" class:

Option Explicit

Private Const SANDWICHES_WORKSHEET As String = "Sandwiches"
Private Const NAME_OFFSET As Integer = 0
Private Const SIZE_OFFSET As Integer = 1

Private wsSandwiches As Worksheet
Private rgSandwich As Range

Private intIngredientCount As Integer
Private arrIngredients() As Variant 'IngrdID, Servings

' ####################################### Essential Methods and stuff #######################################

Private Sub Class_Initialize()
    If WorksheetExists(wbSandwichAnalysis, SANDWICHES_WORKSHEET) Then
        Set wsSandwiches = wbSandwichAnalysis.Worksheets(SANDWICHES_WORKSHEET)
    Else
        Set wsSandwiches = Nothing
        Err.Raise vbObjectError + 200, "Sandwich Class", "The worksheet named " & SANDWICHES_WORKSHEET & " could not be located."
    End If
End Sub

Private Function WorksheetExists(wb As Workbook, sName As String) As Boolean
    
    Dim s As String
    
    On Error GoTo ErrHandler
    
    s = wb.Worksheets(sName).Name
    
    WorksheetExists = True
    Exit Function
    
ErrHandler:
    WorksheetExists = False
End Function

Public Function GetSandwich(sName As String) As Boolean  ' Returns boolean, but sets value of rgSandwich by looking up the name of the sandwich input into the sName parameter
    Dim lRow As Long
    Dim bFoundSandwich As Boolean
    
    Set rgSandwich = Nothing
    bFoundSandwich = False
    
    lRow = 2
    Do Until IsEmpty(wsSandwiches.Cells(lRow, 1))
        If UCase(wsSandwiches.Cells(lRow, 1).Value) = UCase(sName) Then
            Set rgSandwich = wsSandwiches.Cells(lRow, 1)
            bFoundSandwich = True
            Exit Do
        End If
        lRow = lRow + 1
    Loop
    
    GetSandwich = bFoundSandwich
End Function

Private Sub UninitializedError()
    Err.Raise vbObjectError + 101, "Sandwich Class", _
    "The Sandwich has not been properly initialized. " & _
    "Use the GetSandwich method to initialize the Sandwich."
End Sub

' ####################################### Actions and Stuff #######################################

Public Function Delete() As Boolean
    Delete = False
    If rgSandwich Is Nothing Then
        UninitializedError
    Else
        rgSandwich.EntireRow.Delete xlUp
        Set rgSandwich = Nothing
        Delete = True
    End If
End Function

' ####################################### Properties #######################################

Property Let Name(NameString As String)
    If rgSandwich Is Nothing Then
        Name = ""
    Else
        rgSandwich.Offset(0, NAME_OFFSET).Value = NameString
    End If
End Property
Property Get Name() As String
    If rgSandwich Is Nothing Then
        UninitializedError
    Else
        Name = rgSandwich.Offset(0, NAME_OFFSET).Value
    End If
End Property



Property Let Size(SizeString As String)
    If rgSandwich Is Nothing Then
        Size = ""
    Else
        Select Case SizeString
            Case "Small"
                rgSandwich.Offset(0, SIZE_OFFSET).Value = SizeString
            Case "Regular"
                rgSandwich.Offset(0, SIZE_OFFSET).Value = SizeString
            Case "Large"
                rgSandwich.Offset(0, SIZE_OFFSET).Value = SizeString
            Case "Flat Bread"
                rgSandwich.Offset(0, SIZE_OFFSET).Value = SizeString
            Case Else
                Err.Raise vbObjectError + 514, "Sandwich Class", "Size not valid. " & _
                "Either choose from valid sizes or create new size."
        End Select
    End If
End Property
Property Get Size() As String
    If rgSandwich Is Nothing Then
        UninitializedError
    Else
        Size = rgSandwich.Offset(0, SIZE_OFFSET)
    End If
End Property



Property Let IngredientName(Index As Integer, ByVal sName As String)
    If rgSandwich Is Nothing Then
        IngredientName = ""
    Else
        rgSandwich.Offset(0, Index + 2).Value = sName
    End If
End Property
Property Get IngredientName(Index As Integer) As String
    If rgSandwich Is Nothing Then
        UninitializedError
    Else
        IngredientName = rgSandwich.Offset(0, Index + 2).Value
    End If
End Property



Property Let IngredientAmount(Index As Integer, ByVal sAmount As Variant)
    If rgSandwich Is Nothing Then
        IngredientAmount = ""
    Else
        If Not IsNumeric(sAmount) Then
            GoTo ErrHandler
        Else
            rgSandwich.Offset(0, Index + 3).Value = sAmount
        End If
    End If
ErrHandler:
    Err.Raise vbObjectError + 513, "Sandwich Class", "Amount is not numeric."
End Property
Property Get IngredientAmount(Index As Integer)
    If rgSandwich Is Nothing Then
        UninitializedError
    Else
        IngredientAmount = rgSandwich.Offset(0, Index + 3).Value
    End If
End Property

(for readability, the property Lets and Gets are grouped.)

so this is what i need: see how the property lets and gets for IngredientName/Amount have the parameters "Index" and "sAmount/Name"?
well the reason i have this is to be able to set the variables by Index in a way, by calling it like this:

dim oSandwich as New Sandwich

With oSandwich
.Name = "Test Sandwich"
.IngredientName(1) = "Tomatoes"
.IngredientAmount(1) = 2.5
.IngredientName(2) = "Sauce"
' etc.
End With

but i guess i just don't understand how Property Let/Get/Set methods work. and the Office help does nothing for me.

i'm sorry if this is a stupid question or if i'm missing something easy, but this could be the one thing that lands me my dream job, so please help if at all possible.

thanks a million before hand,
stephen

Hello Stephen,

It's seems to me you would be better off placing this code into a VBA Module instead of a Class Module. Class modules are used to create in process server objects. What you are attempting is a regular macro.

You should explore the User Type construct and Collection objects. These are most likely what you are really after. Your code needs to be revamped, but without knowing the original objectives that's not possible.

no offense, but i know what i'm doing. the first code listing is my Sandwich Class ( i also have a Sandwiches collection class, which works fine)

the second code listing is something i have in a code module that references each Sandwich object and returns the ingredients associated with each of them.

i've been working on it a bit, and i have the Property Get procedures pretty well working fine, but the Lets are giving me trouble.

what kind of call/method/whatever calls the property let procedure?
oSandwich.property = ?
varname = oSandwich.property ?

after several hours of experimentation i finally figured it out. i had to do some crazy math, but it works like a charm. thanks for all the help.