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

Last reviewed: July 29, 1997
Article ID: Q108731
The information in this article applies to:
  • Microsoft Visual Basic Programming System, Applications Edition, version 1.0
  • Microsoft Excel for Windows, version 5.0

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          : kbcode kbprg
Version           : 1.00
Platform          : WINDOWS


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.