Microsoft KB Archive/108731

From BetaArchive Wiki
Knowledge Base


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.

  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.


Keywords: KB108731