Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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: 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 -- medicenpringles ------------------------------------------------------------------------ medicenpringles's Profile: http://www.excelforum.com/member.php...o&userid=16458 View this thread: http://www.excelforum.com/showthread...hreadid=478593 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
the same question, revisisted
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. -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478593 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
the same question, revisisted
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- -- medicenpringle ----------------------------------------------------------------------- medicenpringles's Profile: http://www.excelforum.com/member.php...fo&userid=1645 View this thread: http://www.excelforum.com/showthread.php?threadid=47859 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
the same question, revisisted
oSandwich.propertyName = "whatever" ' calls the Let procedure
varname= oSandwich.propertyName ' calls the Get procedure -- Regards, Tom Ogilvy "medicenpringles" <medicenpringles.1xdl2g_1130105107.6536@excelfor um-nospam.com wrote in message news:medicenpringles.1xdl2g_1130105107.6536@excelf orum-nospam.com... 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- ? -- medicenpringles ------------------------------------------------------------------------ medicenpringles's Profile: http://www.excelforum.com/member.php...o&userid=16458 View this thread: http://www.excelforum.com/showthread...hreadid=478593 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
the same question, revisisted
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. -- medicenpringles ------------------------------------------------------------------------ medicenpringles's Profile: http://www.excelforum.com/member.php...o&userid=16458 View this thread: http://www.excelforum.com/showthread...hreadid=478593 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|