the same question, revisisted

[medicenpringles]

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:


Code:
--------------------

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:

Code:
--------------------

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

 

[Leith Ross]

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.

 

[medicenpringles]

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

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

i've been working on it a bit, and i have the Property Get procedure
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-

 

[Tom Ogilvy]

oSandwich.propertyName = "whatever" ' calls the Let procedure
varname= oSandwich.propertyName ' calls the Get procedure

--
Regards,
Tom Ogilvy


"medicenpringles"

 

[medicenpringles]

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.