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

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.

1. Start Microsoft Excel version 5.0. A new Workbook (sheet1) is created by default.
2. 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
   						

3. 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
   						

4. 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.