VBA TUTOR NEWSLETTER ~ TUTORIAL TEN: Referencing Objects in VBA

>GRSoftware >VBA Tutor >Newsletter Tutorials >Tutorial 9 >Tutorial 11

VBA TUTOR NEWSLETTER ~ TUTORIAL TEN:
Referencing Objects in VBA

I continually get calls for help asking how to reference "things" using VBA. It seems clear that many beginner VBA users are confused over the basic building block of WORD, EXCEL and ACCESS, the concept of 'Objects'. 'Objects' can be a difficult concept to use in practice when you are just beginning to learn computer programming, but a basic understanding is essential. As well as reviewing Tutorial 3, a good place to start is Lesson 9 of VBATutor, which give a good overview of the concepts of inbuilt and user defined 'Objects'.

This extract from Lesson Nine outlines the main concepts of using 'Objects'.

"An Object represents an element [part] of an application, such as a cell, a form, a bookmark or a button. In Visual Basic code, you must identify an object before you can reference [refer to] one of its properties."

So, to use 'Objects', you must both identify the 'Object', then reference one of its 'Properties', then you can set (give a value to) or reference (ask for or use the value of) that 'Property'.

For Example:

OptionButtton2.value = False

Identify the 'Object', " OptionButtton2 ".
Reference to a 'Property' of the 'Object, " value ".
Set the 'Property', " False ".

OR .....

if OptionButtton2.value = False Then

Identify the 'Object', " OptionButtton2 ".
Reference to a 'Property' of the 'Object, " value ".
'Reference' the value of the 'Property', " if ... False ... Then ".

To 'FULLY' comprehend the concept of 'Objects', open the VB Editor, then open the 'Object Browser' which is found in the 'View' menu.

Object Browser in the VB Editor window

You can see in this window that the 'Objects' are listed in the left pane and the 'Properties' are listed in the right pane. When you select either, a brief description will appear in the bottom pane. For a more detailed description of a property, select it then click on the help button.

Have a good look at all the 'Objects' and their 'Properties' that can be either 'set' or 'referenced'.

EXERCISE for BEGINNER VBA USERS:
Use the words 'Property' of the document 'Object' to create a msgbox that give a word count of the active document.

EXERCISE FOR INTERMEDIATE VBA USERS:

"User Defined Data Types" are created using the "Type" statement as show in the example below which has been taken from 'Help' example available in the VB Editor.

Type EmployeeRecord ' Create user-defined type.
ID As Integer ' Define elements of data type.
Name As String * 20
Address As String * 30
Phone As Long
HireDate As Date
End Type

The created 'Object' is used in the following way.

Sub CreateRecord()
Dim MyRecord As EmployeeRecord ' Declare variable
MyRecord.ID = 12003 ' Assign a value to an element.
End Sub

"User Defined Data Types", your own created 'Objects', are essential in a large project to keep the data encapsulated into manageable 'blocks'.

As a little exercise to see how "User Defined Data Types" can be used, use the above code to fill a table with data collected from either a series of input boxes or a userform.

EXERCISE for ADVANCED VBA USERS:

Have a look at Lesson 27 in VBATutor for an explanation of how to create and use 'Object' Arrays.

[ NOTE: 'button7 is an userform image 'Object'. ]

First 'declare' the Object Array ....

Dim buttons(9), circles(9), crosses(9), lines(9) As Object

Second, 'initialize' the array using the 'set' statement ...

Set buttons(7) = button7

Finally, you can reference the Objects by their array index ...

buttons(7).Visible = False

NOW ....
Create a series of 4 buttons, set them into an object array, then alter some of their properties by referring to them by their object array index.

HOPEFULLY, THESE CODE EXAMPLES WILL ADD TO YOUR ABILITY TO BECOME A VBA POWER USER!