Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Custom Properties
Hi Guys, I am having a few problems with some custom properties in an excel work book. You can set up custom properties for a work sheet like so: Code: -------------------- Public Property Get Insulation_Type() As String Insulation_Type = Range(Insulation_Type_Address()).Value End Property Public Property Let Insulation_Type(New_Value As String) Range(Insulation_Type_Address()).Value = New_Value End Property -------------------- This then enables you to do things like: Code: -------------------- Worksheets("Gutter1").Insulation_Type = "XXXX" -------------------- and Code: -------------------- Ins_Type = Worksheets("Gutter1").Insulation_Type -------------------- This works fine, however, when you then do something like this: Code: -------------------- Function Data_Appears_Valid(Check_Sheet As Worksheet) As Boolean Dim Error_List(1, 200) As String Dim Error_Count As Integer Dim Error_Display As Integer Dim Out_Pos_Row As Integer Dim Out_Pos_Cell As String Error_Count = 0 '* '* Insulation type '* If Check_Sheet.Insulation_Type = "" Then Error_List(0, Error_Count) = "'" & Check_Sheet.Name & "'!" & Check_Sheet.Insulation_Type_Address Error_List(1, Error_Count) = Check_Sheet.Name & ": Insulation Type cannot be blank" Error_Count = Error_Count + 1 End If -------------------- The code complains that Check_Sheet does not have the property Insulation_Type. How can I pass a worksheet as a parameter and get the code to compile ? Secondly, I originally had the properties set up like so: Code: -------------------- Type Property_String Address As String Value As String End Type Public Property Get Insulation_Type() As Property_String Insulation_Type.Value = Range(Insulation_Type_Address()).Value Insulation_Type.Address = Insulation_Type_Address() End Property -------------------- And accessed these as follows: Code: -------------------- Ins_Type = Worksheets("Gutter1").Insulation_Type.Value Call Reset_Cell(Worksheets("Gutter1").Insulation_Type.A ddress) -------------------- This worked fine for one pass through the code, second and subsequent passes caused the error: "the object invoked has disconnected from its clients" I spent many hours Monday and Tuesday searching for an answer to this on the web. Apparently there are many causes, none of which fitted my scenario. The actual cause is the fact that I have a user defined type as the parameters. Return this to an intrinsic type and the error goes away. So my next question is: How can I implement this type of functionality ? Ie have a property such as Insulation_Type.Address and Insulation_Type.Value ? Regards and thanks. Rich -- Rich_z ------------------------------------------------------------------------ Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737 View this thread: http://www.excelforum.com/showthread...hreadid=383054 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Custom Properties
Where is your code for Insulation_Type? It must be in a class module for it
to work. The class module name is the name of the new object you created to be a worksheet with your custom properties but your Function call specifies "Function Data_Appears_Valid(Check_Sheet As Worksheet)..." If Check_Sheet is a Worksheet it does not have your custom properties. Make if Function Data_Appears_Valid(Check_Sheet As ClassName) (where ClassName is the name of the Class Module) "Rich_z" wrote: Hi Guys, I am having a few problems with some custom properties in an excel work book. You can set up custom properties for a work sheet like so: Code: -------------------- Public Property Get Insulation_Type() As String Insulation_Type = Range(Insulation_Type_Address()).Value End Property Public Property Let Insulation_Type(New_Value As String) Range(Insulation_Type_Address()).Value = New_Value End Property -------------------- This then enables you to do things like: Code: -------------------- Worksheets("Gutter1").Insulation_Type = "XXXX" -------------------- and Code: -------------------- Ins_Type = Worksheets("Gutter1").Insulation_Type -------------------- This works fine, however, when you then do something like this: Code: -------------------- Function Data_Appears_Valid(Check_Sheet As Worksheet) As Boolean Dim Error_List(1, 200) As String Dim Error_Count As Integer Dim Error_Display As Integer Dim Out_Pos_Row As Integer Dim Out_Pos_Cell As String Error_Count = 0 '* '* Insulation type '* If Check_Sheet.Insulation_Type = "" Then Error_List(0, Error_Count) = "'" & Check_Sheet.Name & "'!" & Check_Sheet.Insulation_Type_Address Error_List(1, Error_Count) = Check_Sheet.Name & ": Insulation Type cannot be blank" Error_Count = Error_Count + 1 End If -------------------- The code complains that Check_Sheet does not have the property Insulation_Type. How can I pass a worksheet as a parameter and get the code to compile ? Secondly, I originally had the properties set up like so: Code: -------------------- Type Property_String Address As String Value As String End Type Public Property Get Insulation_Type() As Property_String Insulation_Type.Value = Range(Insulation_Type_Address()).Value Insulation_Type.Address = Insulation_Type_Address() End Property -------------------- And accessed these as follows: Code: -------------------- Ins_Type = Worksheets("Gutter1").Insulation_Type.Value Call Reset_Cell(Worksheets("Gutter1").Insulation_Type.A ddress) -------------------- This worked fine for one pass through the code, second and subsequent passes caused the error: "the object invoked has disconnected from its clients" I spent many hours Monday and Tuesday searching for an answer to this on the web. Apparently there are many causes, none of which fitted my scenario. The actual cause is the fact that I have a user defined type as the parameters. Return this to an intrinsic type and the error goes away. So my next question is: How can I implement this type of functionality ? Ie have a property such as Insulation_Type.Address and Insulation_Type.Value ? Regards and thanks. Rich -- Rich_z ------------------------------------------------------------------------ Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737 View this thread: http://www.excelforum.com/showthread...hreadid=383054 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Custom Properties
Hi K(ev???) Thanks for the quick reply. You beat me to the post by about 15 minutes. I have changed the parameter declaration to 'AS OBJECT', and it compiles (and more importantly runs...) without error. Any thoughts on the using a UDT in a property ? -- Rich_z ------------------------------------------------------------------------ Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737 View this thread: http://www.excelforum.com/showthread...hreadid=383054 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Custom Properties
Rick
this should be Function Data_Appears_Valid(Check_Sheet As Sheet1) As Boolean where sheet1 is the *codename* for the sheet with the property get/set This is correct because it actually prevents you from passing an illegal sheet(i.e a sheet without the property) to this function;) DM Unseen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Custom Properties
Hi DM, Unfortunately this will be called from several worksheets which obviously will have different names and slightly different layouts, but all with the same set of properties. It's actually working now with the declaration 'AS OBJECT' and as there will not be any dynamic calls to the function, i can be certain that the sheets using the function will always have the correct properties. Thanks for your input! regards Rich -- Rich_z ------------------------------------------------------------------------ Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737 View this thread: http://www.excelforum.com/showthread...hreadid=383054 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Custom Properties
OK,
I did not know this. As an alternative Function Data_Appears_Valid(Check_Sheet As Worksheet) As Boolean Dim mypropsheet as object .... .... set mypropsheet = Check_Sheet if mypropsheet .Insulation_Type = "" Then ....... should also work DM Unseen |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Custom Properties
Can't really comment since I haven't had any experience trying that in a
class module and haven't encountered that problem "Rich_z" wrote: Hi K(ev???) Thanks for the quick reply. You beat me to the post by about 15 minutes. I have changed the parameter declaration to 'AS OBJECT', and it compiles (and more importantly runs...) without error. Any thoughts on the using a UDT in a property ? -- Rich_z ------------------------------------------------------------------------ Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737 View this thread: http://www.excelforum.com/showthread...hreadid=383054 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Spreadsheet Properties | Excel Worksheet Functions | |||
Custom File Properties | Excel Discussion (Misc queries) | |||
i am having problem displaying the custom properties | Excel Discussion (Misc queries) | |||
Custom File Properties | Excel Discussion (Misc queries) | |||
Custom Properties | Excel Programming |