Microsoft KB Archive/108731

= How to Manipulate Object's Properties w/ Property Set/Let/Get =

Article ID: 108731

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q108731



SUMMARY
In Visual Basic for Applications in Microsoft Excel version 5.0 for Windows, you can create your own objects by using modules and then manipulate the properties of those objects by using the Property Set, Property Get, and Property Let statements. This article explains how to use the Property Set/Let/Get syntax.



MORE INFORMATION
Usually, you will define either Property Let and Property Get or Property Set and Property Get. If a property value stores an object reference, use Property Set and Property Get. If a property value stores a variant reference, use Property Let and Property Get.

In the following example, you'll create the object module SGLCount in step 2 and then create another module that uses SGLCount in step 3.

 Start Microsoft Excel version 5.0. A new Workbook (sheet1) is created by default.  Add a new module by using the Insert menu (ALT, I, M, M) or by clicking the Module button on the Visual Basic for Applications toolbar. Name the module SGLCount. Add the following code to the SGLCount module: Option Explicit Private iMyCount As Integer Private rMyRange As Variant

' Use Property Let for Variants Property Let MyCount(iCount As Variant) iMyCount = iCount End Property

Property Get MyCount MyCount = iMyCount End Property

' Use Property Set for Objects Property Set MyRange(rRange As Range) ' Use Set because rRange is a Range Object Set rMyRange = rRange End Property

Property Get MyRange Set MyRange = rMyRange End Property   Add another new module by using the Insert menu (ALT, I, M, M) or by clicking the Module button on the Visual Basic for Applications toolbar. Use the default name for the module (module1). Add the following code to the module1 module: Option Explicit Sub TestCount

Dim rRange As Range

Set rRange = ActiveSheet.Range("B1") rRange.Value = 7777

' Execute module SGLCount Property Set MyRange: Set SGLCount.MyRange = ActiveSheet.Range("B1") ActiveSheet.Range("A1").Select

' Execute module SGLCount Property Set MyCount: SGLCount.MyCount = 5

' Execute module SGLCount Property Get MyRange: rRange = SGLCount.MyRange

' Execute module SGLCount Property Get MyCount: rRange.Value = SGLCount.MyCount rRange.Select

End Sub  Add a command button (Command1) to Sheet1 and assign the TestCount macro to the button. To assign a macro, place the mouse insertion point on the Command1 button and click the right mouse button. Then select Assign Macro.

Keywords: KB108731

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.