Properties

Properties
Properties are used to describe an object. Some properties are read-only, while others are read/write. These properties describe Excel objects. For example, an Excel workbook has a particular path on your hard drive or network where it is saved. That path is a property of the workbook. That path is read-only as it cannot be changed without saving the file to a different location. Properties are separated from objects by periods just as methods are. The following sentence will display the current path of the Loan Calculator.xls file in an onscreen message box:

Msgbox Workbooks(“Loan Calculator.xls”).Path

Note: Msgbox is a function. Functions will be discussed later in this section.

If you want to set a read/write property equal to something, it changes the current value of that particular object. If you don’t set a read/write property equal to something, Excel will tell you the object’s current value. For example, the following sentence will set the sheet name of the first sheet in the workbook to “Cover”

Sheets(“Sheet1”).Name = “Cover”

Collections & Methods

Collections:-
Some objects are collections of other objects. For example, a workbook is a collection of all the objects it contains (ex. sheets, cells, ranges, charts, VBA modules, etc.). A VBA statement that makes reference to the workbook names “Loan Calculator.xls” would appear like this:

Workbooks(“Loan Calculator.xls”)

Methods:-
A method is an action that can be performed on an object. Excel VBA objects are separated from their methods by a period. For example, if you wanted to save a particular file as part of a VBA program you could include the following sentence in the code:

Workbooks(“Loan Calculator.xls”).Save

Object Oriented Programming & Objects

Object Oriented Programming:-
VBA is a structured programming language where sentences (called statements) are constructed of building blocks such as objects, methods, and properties. These VBA statements are grouped in larger blocks called procedures. A procedure is a set of VBA statements that performs a specific task or calculates a specific result. The first step to learning how to create procedures is to learn about the building blocks.

Objects:-
VBA is an object-oriented programming language, which means the statements you create in VBA act on specific objects rather than begin general commands. Excel is made of objects that you can manipulate through the VBA statements. The entire workbook file is an object, an individual sheet is an object, a range within a sheet can be an object, and an individual cell is an object. There are many more types of objects, and as you see objects can be containers for (called collections) other objects.

VBA Programming Basics

The VBA programming language provides the tools needed to create solutions when the macro recorder can’t get the job done. VBA is such a powerful programming language that you can (if you have the programming ability) create a solution to virtually any problem you encounter in Excel. In fact you can completely customize Excel through VBA and you can even change the entire Excel interface.

(i) Object Oriented Programming.
(ii) Objects.
(iii) Collections.
(iv) Methods.
(v) Properties.
(vi) Functions.
(vii) Procedures.
(viii) Variables and Constants.

Activate window by name in MS-Word

Sub GenerateGlossary()
Dim strSource As String
Dim strDestination As String
Dim strGlossaryName As String

strSource = ActiveWindow.Caption
strGlossaryName = "word"

Documents.Add
ActiveDocument.SaveAs FileName:=strGlossaryName, FileFormat:=wdFormatDocument
strDestination = ActiveWindow.Caption
Windows(strSource).Activate
End Sub

Active document paragraph in word.

Sub loopDemo()
Dim i As Integer
For i = 1 To ActiveDocument.Paragraphs.Count
Application.StatusBar = "formatting" & i & " out of " & ActiveDocument.Paragraphs.Count & "..."
Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdMove
Next i
End Sub

Turning Off Track Changes

Sub trac()
Dim blnTrackChangesOn As Boolean
blnTrackChangesOn = ActiveDocument.TrackRevisions
ActiveDocument.TrackRevisions = False
ActiveDocument.TrackRevisions = blnTrackChangesOn
End Sub