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