VBA library documentation that will get for the final exam (keep in
mind that not all library functions may have been covered during a
particular semester)
|
- Accessing the following VBA objects:
|
| |
- Application:
the MS-Office program running
- ActiveDocument:
current MS-Word document
- Selection:
currently selected text
|
- Useful methods and attributes of the
Application object
|
| |
- Application.Windows.Count:
number of open Word windows
- Application.CapsLock:
a Boolean to indicate whether caps lock is turned on or off
|
- Useful methods and attributes of the
ActiveDocument object
|
| |
- ActiveDocument.CountNumberedItems:
total number of bulleted and numbered items
- ActiveDocument.Save:
saves the current document
- ActiveDocument.SaveAs("<filename>"):
save and rename document
- ActiveDocument.Select:
make the entire document the current selection
- ActiveDocument.ActiveWindow.Caption = "<Caption>":
change caption title in the Word window
- ActiveDocument.Close (<wdPromptToSaveChanges>
or <wdDoNotSaveChanges> or <wdSaveChanges>):
close current Word window and apply the effect of specified constant
- ActiveDocument.Checkspelling:
run spell checker
- ActiveDocument.DeleteAllComments:
delete all comments from the active document
- ActiveDocument.Printout:
prints the active document
- ActiveDocument.SendMail:
sends email with the active document as an attachment
- ActiveDocument.Words.Count:
number of words in the MS-Word document
- ActiveDocument.Words.Comments.Count:
number of annotation comments in the MS-Word document
- ActiveDocument.Select:
select all the text in the active Word document
- ActiveDocument.Application:
the application associated with the document
- ActiveDocument.Name:
name of the active document
- ActiveDocument.Path:
save path of the active document
- ActiveDocument.FullName:
name & save path of the active document
- ActiveDocument.HasPassword:
a Boolean to indicate if the active document has been saved since
last changed
- ActiveDocument.password = "<Password>"
: sets the password for the
active document to the value specified
- ActiveDocument.SpellingChecked:
a Boolean to indicate if the active document has been spell checked
since last changed
- ActiveDocument.Saved:
a Boolean to indicate if the active document has been saved since
last changed
- ActiveDocument.SpellingErrors.Count:
the number of spelling mistakes in the active document
- ActiveDocument.InlineShapes.Count:
number of inserted inline shapes in the document
- ActiveDocument.Shapes.Count: number
of primitive shapes in the document
- ActiveDocument.Tables(i)
- Allows access to one table from the collection of tables.
- 'i'
is a positive integer (i.e. >= 1)
- ActiveDocument.Tables.Count: number
of tables in the active document
- ActiveDocument.Tables(i).Sort(<Boolean>):
'i'
is a positive integer, sorts the table specified by 'i'
in the active document, the Boolean specifies if it is true that the
tables include headers
- ActiveDocument.InlineShapes(i).height :
height of inline shape # 'i'
- ActiveDocument.InlineShapes(i).width :
width of inline shape # 'i'
- ActiveDocument.Shapes(1).Delete :
delete shape # 'i'
- ActiveDocument.Shapes(i).height :
height of shape # 'i'
- ActiveDocument.Shapes(i).width :
width of shape # 'i'
- ActiveDocument.Shapes(6).Fill.ForeColor =
<Color: vbBlack, vbRed, vbGreen, vbYellow, vbRed, vbBlue, vbWhite>:
sets Shape # 'i' to the specified
color
|
- ActiveDocument:
find and replace text
ActiveDocument.Content.Find.Execute
FindText:="<text>", ReplaceWith:="<text>", _
Replace:= <wdReplaceAll>,
MatchCase:= <Boolean>
|
- ActiveDocument:
find and replace font effects
(turn on/off)
With ActiveDocument.Content.Find
.Font.<Font effect> = <Boolean>
With .Replacement
.Font.<Font effect> = <Boolean>
End With
.Execute Replace:=wdReplaceAll
End With
|
- ActiveDocument:
find and replace text styles in
a document
With ActiveDocument.Content.Find
.Style = "<Search style>"
With .Replacement
.Style = "<Replacement style>"
End With
.Execute Replace:=wdReplaceAll
End With
|
- Counting the number of occurrences
of a particular word in a document:
With ActiveDocument.Content.Find
Do While .Execute(FindText:=<"searchWord">,
Forward:=True, _
MatchWholeWord:=True) = True
<Do something when each instance of search word is found>
Loop
End With
|
- Useful methods and attributes of the
Selection object
|
| |
- Selection.Expand:
expand selection to next grouping size of text
- Selection.Copy:
copy selected text
- Selection.MoveLeft:
move cursor left
- Selection.MoveRight:
move cursor right
- Selection.ParagraphFormat.Alignment = <wdAlignParagraphCenter
or wdAlignParagraphLeft or wdAlignParagraphRight>: to center, left
or right align a paragraph (respectively)
- Selection.ParagraphFormat.SpaceAfter = <#
points e.g. 0, 3, 6 etc.): Adds extra
spacing to the end of paragraph
- Selection.ParagraphFormat.LeftIndent =
InchesToPoints(# inches): # inches for
left indenting of a paragraph
- Selection.ParagraphFormat.RightIndent =
InchesToPoints(# inches): # inches for
right indenting of a paragraph
- Selection.PasteAndFormat (<wdFormatOriginalFormatting
or wdFormatPlainText>): copied text is pasted with the
specified formatting
- Selection.Font.Name = <Font name>
: change font type
- Selection.Font.Size = <Size>
: change font size
- Selection.Font.Bold = <Boolean>
: turn bolding on or off
- Selection.Font.Bold = wdToggle:
toggle bolding
- Selection.Font.Italic = <Boolean>
: turn italics on or off
- Selection.Font.Italic = wdToggle:
toggle italics
- Selection.Font.Underline = <Boolean>
: turn underlining on or off
- Selection.HomeKey Unit:= <wdStory>:
move selection to the top of the document
- Selection.Type :
returns information about the type of information selected (if any:
<wdSelectionIP - none> <wdSelectionNormal
- text selection> <wdSelectionShape - graphical shape
selected> )
- Selection.text = <"Selected text">
: writes the selected text to
the document by setting the text attribute directly
- Selection.TypeText(<"Selected text">)
: writes the selected text to
the document through the method
TypeText()
|
- Common and useful collections
|
| |
- InlineShapes():
includes all the external images inserted into a Word document
- Lists(): all the lists in a Word
document
- Shapes(): all the primitive shapes
in a Word document
- Tables(): all the tables in a Word
document
- Documents(): the collection of
documents currently opened
|
- Useful methods and attributes of the
Documents collection
|
| |
- Documents.Item(i):
- Allows access to one document from the collection of
documents.
- 'i'
is a positive integer (i.e. >= 1)
- Documents.close: closes all of the
open Word documents
- Documents.count: returns a positive
integer that is equal to the number of documents currently opened.
- Documents(i).Save: save the
document accessed through the index
- Documents(i).SaveAs (<"name">):
save the document (accessed through the index) under another name
- Documents(i).PrintOut: print the
document accessed through the index
- Documents.Open (<"location and name">):
opens the document with the specified location and name and adds it
to the documents collection
|
|
|
| |
- Len(<string>): this function
returns the number of characters in the specified string
- Dir("<Path to a folder>") :
returns the name of a document under the specified path
- Dir("<Path to a document>") :
returns the name of the document under the specified path
- Dir : after the path to a folder
has been specified with a previous call to Dir,
this call returns the name of the next document in the specified
folder
- CreateObject("<Name of MS-Office
application>"): starts up the Office application
specified in the brackets.
- <Name of an excel
spreadsheet variable>.workbooks.Open("<Path
and name of an Excel spreadsheet/worksheet>"):
opens up the Excel spreadsheet at the specified location.
- <Name of an excel
spreadsheet variable>.Range("<Cell
in a spreadsheet>").Value:
Returns as a string the contents of the cell of spreadsheet referred
to by the spreadsheet variable.
|
-
Excel methods (accessible via VBA run through Word)
|
| |
- <reference to an Excel
window>.Visible = <Boolean> : Makes an Excel
window visible or not visible
- <reference to an Excel
window>.workbooks.open("<Path to an Excel document">) :Opens
the Excel document named under the specified path and returns a
reference to that document.
- <reference
to an Excel window>.range("<Cell address>").Value :
returns the contents of the specified cell
|
As mentioned VBA documentation WILL NOT be provided for the following
(you're expected to know how to apply them without documentation)
(This is only a quick overview, you should look to the course notes for
details)
You could see exam questions that involve any of the above questions in the
form of VBA code writing or a program code trace.
-
Basic mathematical operators: assignment (=),
addition (+),
subtraction (-),
multiplication(*),
division (/),
exponnent(^)
|
-
Basic statistical formulas
|
|
|
-
sum (<start
cell> : <end cell>)
-
average (<start
cell> : <end cell>)
-
min (<start
cell> : <end cell>)
-
max (<start
cell> : <end cell>)
|
-
Counting functions (an alternative to entering the start: cell range
is to manually enter the data sequence as the function argument).
|
|
|
-
count (<start
cell> : <end cell>)
-
counta (<start
cell> : <end cell>)
-
countblank
(<start cell> : <end cell>)
|
-
String (and related utility) functions (an alternative to entering
the cell is to manually enter a string as the function argument).
|
|
|
-
left (<cell>,
<number of characters>)
-
right (<cell>,
<number of characters>)
-
mid (<cell>,
<start position>, <number of characters>)
-
find(<find
text>, <within text>, [<start position>])
-
concatenate(<cell1>, cell2, cell3....celln)
|
|
|
|
|
|
|
|
|
|
-
=if (<condition
to check>, <return value: condition true>, <return value:
condition false>)
|
-
Logical comparators (relational operators): less than (<),
less than equal to (<=),
greater than
(>),
greater than equal to (>=),
equal to (=),
not equal to (<>)
|
|
|
|
|
-
LOOKUP(<Lookup
value>, <Lookup column (vector) Start : End>, <result
column (vector) Start : End>)
-
VLOOKUP(<Lookup
value>, <Lookup table Start : End>, <Lookup table Column specifying
the return value>, [<Exact match required?>])
|
-
Conditional counting functions (an alternative to entering the
start:cell range is to manually enter the data sequence as the
function argument).
|
|
|
-
countif (<start
cell> : <end cell>, <condition>)
-
countifs (<start
cell> : <end cell>, <condition>, (<start cell>
: <end cell>, <condition>) )
|
- Characters that
define input masks
|
|
|
- Numbers:
0, 9 (mandatory, optional)
- Alphabetic letters:
L, ? (mandatory, optional)
- Alpha or digit:
A, a (mandatory, optional)
- Digit, space, plus
or minus sign: #
(default is a space)
-
Changing case:
>,<
(upper, lower)
|
- Specifying
validation rules (Strings): Like "<Format
for String>"
|
|
|
- Formats: Numbers [0-9],
Alphabetic [A-Z],
#, Wildcard
*, Single character wild
card ?
|
