Name
Returns
Description
TransitionExp
Boolean
Set/Get whether to evaluate expressions using 
Eval
Lotus 1-2-3 rules in the worksheet 
TransitionForm
Boolean
Set/Get whether formula entries can be entered using 
Entry
Lotus 1-2-3 rules 
Type
XlSheetType
Read-only. Returns the worksheet type (for example,
xlWorksheetxlExcel4MacroSheet,
xlExcel4IntlMacroSheet
UsedRange
Range
Read-only. Returns the range in the worksheet that is
being used 
Visible
XlSheet
Set/Get whether the worksheet is visible. Also, set this to 
Visibility
xlVeryHiddento prevent a user from making the work-
sheet visible 
VPageBreaks
VPageBreaks
Read-only. Returns a collection holding all the vertical
page breaks associated with the worksheet 
Worksheet Methods
Name
Returns
Parameters
Description
Activate
Activates the worksheet 
Calculate
Calculates all the formulas in the
worksheet 
ChartObjects
Object
[Index]
Returns either a chart object
(ChartObject)or a collection of
chart objects (ChartObjects)in
aworksheet 
CheckSpelling
[CustomDictionary],
Checks the spelling of the text in the 
[IgnoreUppercase],
worksheet. Acustom dictionary can 
[AlwaysSuggest],
be specified (CustomDictionary)
[SpellLang]
all uppercase words can be ignored
(IgnoreUppercase), and Excel can
be set to display a list of suggestions
(AlwaysSuggest)
CircleInvalid
Circles the invalid entries in the
worksheet 
ClearArrows
Clears out all the tracer arrows in
the worksheet 
ClearCircles
Clears all the circles around invalid
entries in a worksheet 
958
Worksheet Methods
Pdf reverse page order - rotate PDF page permanently in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Empower Users to Change the Rotation Angle of PDF File Page Using C#
rotate pages in pdf permanently; how to rotate one page in pdf document
Pdf reverse page order - VB.NET PDF Page Rotate Library: rotate PDF page permanently in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
PDF Document Page Rotation in Visual Basic .NET Class Application
rotate pages in pdf permanently; pdf rotate single page
Name
Returns
Parameters
Description
Copy
[Before],[After]
Adds a new copy of the worksheet
to the position specified at the
Beforeor Afterparameter 
Delete
Deletes the worksheet 
Evaluate
Variant
Name
Evaluates the Namestring expression
as if it were entered into a work-
sheet cell 
ExportAs
TypeAsVariant,
Exports a file to a format specified 
FixedFormat
FileName,Quality,
by using the xlFixedFormatType
IncludeDoc
constants
Properties,
IgnorePrintAreas,
From,To,
OpenAfterPublish
Move
[Before],[After]
Moves the worksheet to the position
specified by the parameters 
OLEObjects
Object
[Index]
Returns either a single OLEobject
(OLEObject)or a collection of
OLEobjects (OLEObjects)for a
worksheet 
Paste
[Destination],
Pastes the contents of the clipboard 
[Link]
into the worksheet. Aspecific desti-
nation range can be specified with
the Destinationparameter. Set
Linkto Trueto establish a link to
the source of the pasted data. Either
the Destinationor the Link
parameter can be used 
PasteSpecial
[Format],[Link],
Pastes the clipboard contents into the 
[DisplayAsIcon],
current worksheet. The format of the 
[IconFileName],
clipboard data can be specified with 
[IconIndex],
the string Formatparameter. Set 
[IconLabel],
Linkto Trueto establish a link to  
[NoHTMLFormatting]
the source of the pasted data. Set
DisplayAsIconto Trueto display
the pasted data as an icon and the
IconFileNameIconIndex, and
IconLabelto specify the icon and
label. Adestination range must be
already selected in the worksheet 
Table continued on following page
959
Worksheet Methods
C# PDF Page Move Library: re-order PDF pages in C#.net, ASP.NET
page. Enable C# users to move, sort and reorder all PDF page in preview. Support to reverse page order in PDF document. RasterEdge
rotate all pages in pdf and save; rotate pdf page permanently
VB.NET PDF Page Move Library: re-order PDF pages in vb.net, ASP.
Support to reverse page order in adobe PDF document in both .NET WinForms application and ASP.NET webpage. Enable move, sort and reorder PDF page in preview.
rotate all pages in pdf; change orientation of pdf page
Name
Returns
Parameters
Description
PivotTables
Object
[Index]
Returns either a single PivotTable
report (PivotTable)or a collection
of PivotTablereports (Pivot-
Tables)for a worksheet 
PivotTable
Pivot
[SourceType],
Creates a PivotTablereport. The 
Wizard
Table
[SourceData],
SourceTypeuses the XLPivot
[TableDestination],
TableSourceTypeconstants to 
[TableName],
specify the type of SourceData
[RowGrand],[Column
being used for the PivotTable
Grand],[SaveData],
TableDestinationholds the range 
[HasAutoFormat],
in the parent worksheet where that 
[AutoPage],
report will be placed. TableName
[Reserved],
holds the name of the new report. Set 
[BackgroundQuery],
RowGrandor ColumnGrandto True
[OptimizeCache],
to show grand totals for rows and 
[PageFieldOrder],
columns, respectively. Set HasAuto
[PageFieldWrap
Formatto Truefor Excel to format 
Count],[ReadData],
the report automatically when it is 
[Connection]
refreshed or changed. Use the Auto
Pageparameter to set if a page field
is created automatically for consoli-
dation. Set BackgroundQueryto
Truefor Excel to query the data
source asynchronously. Set Opti-
mizeCacheto Truefor Excel to
optimize the cache when it is built.
Use the PageFieldOrderwith the
xlOrderconstants to set how new
page fields are added to the report.
Use the PageFieldWrapCountto
set the number of page fields in each
column or row. Set ReadDatato
Trueto copy the data from the
external database into a cache.
Finally, use the Connectionparam-
eter to specify an ODBC connection
string for the PivotTable’s cache
PrintOut
[From],[To],
Prints out the worksheet. The 
[Copies],[Preview],
printer, number of copies, collation, 
[ActivePrinter],
and whether a print preview is 
[PrintToFile],
desired can be specified with the 
[Collate],
parameters. Also, the sheets can be 
[PrToFileName],
printed to a file using the Print
[IgnorePrintAreas]
ToFileand PrToFileNameparam-
eters. The Fromand Toparameters
can be used to specify the range of
printed pages 
960
Worksheet Methods
C# Word: How to Use C# Code to Print Word Document for .NET
document pages in original or reverse order within entire C# Class Code to Print Certain Page(s) of powerful & profession imaging controls, PDF document, image
how to rotate all pages in pdf at once; how to rotate one page in a pdf file
Name
Returns
Parameters
Description
PrintPreview
[EnableChanges]
Displays the worksheet in a print pre-
view mode. Set the EnableChanges
parameter to Falseto disable the
Margins and Setup buttons, hence not
allowing the viewer to modify the
page setup 
Protect
[Password],
Protects the worksheet from changes.
[DrawingObjects],
Acase-sensitive Passwordcan be 
[Contents],
specified. Also specifies whether 
[Scenarios],[User
shapes are protected (Drawing
InterfaceOnly],
Objects), whether the entire 
[AllowFormatting
contents are protected (Contents)
Cells],[Allow
or whether only the user interface 
FormattingColumns],
is protected (UserInterfaceOnly)
[AllowFormatting
Rows],[Allow
InsertingColumns],
[AllowInserting
Rows],[Allow
Inserting
Hyperlinks],
[AllowDeleting
Columns],[Allow
DeletingRows],
[AllowSorting],
[AllowFiltering],
[AllowUsing
PivotTables]
ResetAllPage
Resets all the page breaks in the 
Breaks
worksheet 
SaveAs
FilenameAsString,
Saves the worksheet as FileName
[FileFormat],
The type of file to be saved can be 
[Password],
specified with the FileFormat
[WriteResPassword],
parameter. The file can be saved 
[ReadOnly
with the optional passwords in the 
Recommended],
Passwordand WriteResPassword
[CreateBackup],
parameters. Set ReadOnly
[AddToMru],[Text
Recommendedto Trueto display a 
Codepage],[Text
message to the user every time the 
VisualLayout],
worksheet is opened. Set Create
[Local]
Backupto Trueto create a backup
of the saved file. Set the AddToMru
parameter to Trueto add the work-
sheet to the recently opened files list 
Table continued on following page
961
Worksheet Methods
Name
Returns
Parameters
Description
Scenarios
Object
[Index]
Returns either a single scenario
(Scenario)or a collection of sce-
narios (Scenarios)for a worksheet 
Select
[Replace]
Selects the worksheet 
SetBackground
FilenameAsString
Sets the worksheet’s background to 
Picture
the picture specified by the File
Nameparameter 
ShowAllData
Displays all of the data that is
currently filtered 
ShowDataForm
Displays the data form that is part of
the worksheet 
Unprotect
[Password]
Deletes the protection set up for a
worksheet. If the worksheet was
protected with a password, the
password must be specified now 
XmlDataQuery
Range
[XPath]AsString,
Represents cells mapped to a 
[Selection
particular XPath 
Namespaces],[Map]
XmlMapQuery
Range
[XPath]AsString,
Represents cells mapped to a 
[Selection
particular XPath 
Namespaces][Map]
Worksheet Events
Name
Parameters
Description
Activate
Triggered when a worksheet is made to have focus 
BeforeDouble
TargetAsRange,
Triggered just before a user double-clicks a work-
Click
CancelAsBoolean
sheet. The cell closest to the point double-clicked
in the worksheet is passed into the event proce-
dure as Target. The double-click action canbe
canceled by setting the Cancelparameter to True
BeforeRight
TargetasRange,
Triggered just before a user right-clicks a worksheet. 
Click
CancelAsBoolean
The cell closest to the point right-clicked in the
worksheet is passed into the event procedure as
Target. The right-click action can be canceled by
setting the Cancelparameter to True
Calculate
Triggered after the worksheet is recalculated 
Change
TargetAs
Triggered when the worksheet cell values are 
Range
changed. The changed range is passed into the
event procedure as Target
962
Worksheet Events
Name
Parameters
Description
Deactivate
Triggered when the worksheet loses focus 
Follow
TargetAs
Triggered when a hyperlink is clicked on the 
Hyperlink
Hyperlink
worksheet. The hyperlink that was clicked is
passed into the event procedure as Target
PivotTable
ByVal
Triggered when a PivotTablereport is updated 
Update
TargetAs
on a worksheet 
PivotTable
Selection
TargetAs
Triggered when the selection changes in a 
Change
Range
worksheet. The new selected range is passed into
the event procedure as Target
WorksheetFunction Object
The WorksheetFunctionobject allows access to Excel worksheet functions via VBA. The parent of the
WorksheetFunctionobject is the Applicationobject.
WorksheetFunction Common Properties
The ApplicationCreator, and Parentproperties are defined at the beginning of this appendix.
WorksheetFunction Methods
The methods of the WorksheetFunctionobject are actually individual Excel functions that do not have
VBAequivalents. The Excel functions listed here constitute the methods of the WorksheetFunction
object. You can explore these functions in detail by going to Excel’s Help and entering the keywords
“List of Functions” in the Search box.
963
WorksheetFunction Object
AccrInt
AccrIntM
Acos
Acosh
AmorDegrc
AmorLinc
And
Asc
Asin
Asinh
Atan2
Atanh
AveDev
Average
AverageIf
AverageIfs
BahtText
BesselI
BesselJ
BesselK
BesselY
BetaDist
BetaInv
Bin2Dec
Bin2Hex
Bin2Oct
BinomDist
Ceiling
ChiDist
ChiInv
ChiTest
Choose
Clean
Combin
Complex
Confidence
Convert
Correl
Cosh
Count
CountA
CountBlank
CountIf
CountIfs
CoupDayBs
CoupDays
CoupDaysNc
CoupNcd
CoupNum
CoupPcd
Covar
CritBinom
CumIPmt
CumPrinc
DAverage
Days360
Db
Dbcs
DCount
DCountA
Ddb
Dec2Bin
Dec2Hex
Dec2Oct
Degrees
Delta
DevSq
DGet
Disc
DMax
DMin
Dollar
DollarDe
DollarFr
DProduct
DStDev
DStDevP
DSum
Duration
DVar
DVarP
EDate
Effect
EoMonth
Erf
ErfC
Even
ExponDist
Fact
FactDouble
FDist
Find
FindB
FInv
Fisher
FisherInv
Fixed
Floor
Forecast
Frequency
FTest
Fv
FVSchedule
GammaDist
GammaInv
GammaLn
Gcd
GeoMean
GeStep
Growth
HarMean
Hex2Bin
Hex2Dec
Hex2Oct
HLookup
HypGeomDist
IfError
ImAbs
Imaginary
ImArgument
ImConjugate
ImCos
ImDiv
ImExp
ImLn
ImLog10
ImLog2
ImPower
ImProduct
ImReal
ImSin
ImSqrt
964
WorksheetFunction Methods
ImSub
ImSum
Index
Intercept
IntRate
Ipmt
Irr
IsErr
IsError
IsEven
IsLogical
IsNA
IsNonText
IsNumber
IsOdd
Ispmt
IsText
Kurt
Large
Lcm
LinEst
Ln
Log
Log10
LogEst
LogInv
LogNormDist
Lookup
Match
Max
MDeterm
MDuration
Median
Min
MInverse
MIrr
MMult
Mode
MRound
MultiNomial
NegBinomDist
NetworkDays
Nominal
NormDist
NormInv
NormSDist
NormSInv
NPer
Npv
Oct2Bin
Oct2Dec
Oct2Hex
Odd
OddFPrice
OddFYield
OddLPrice
OddLYield
Or
Pearson
Percentile
PercentRank
Permut
Phonetic
Pi
Pmt
Poisson
Power
Ppmt
Price
PriceDisc
PriceMat
Prob
Product
Proper
Pv
Quartile
Quotient
Radians
RandBetween
Rank
Rate
Received
Replace
ReplaceB
Rept
Roman
Round
RoundDown
RoundUp
RSq
RTD
Search
SearchB
SeriesSum
Sinh
Skew
965
WorksheetFunction Methods
Sln
Slope
Small
SqrtPi
Standardize
StDev
StDevP
StEyx
Substitute
Subtotal
Sum
SumIf
SumIfs
SumProduct
SumSq
SumX2MY2
SumX2PY2
SumXMY2
Syd
Tanh
TBillEq
TBillPrice
TBillYield
TDist
Text
TInv
Transpose
Trend
Trim
TrimMean
TTest
USDollar
Var
VarP
Vdb
VLookup
Weekday
WeekNum
Weibull
WorkDay
Xirr
Xnpv
YearFrac
YieldDisc
YieldMat
ZTest
966
WorksheetFunction Object Example
WorksheetFunction Object Example
In this example, an array of numbers is passed to the Maxworksheet function to determine the biggest
number in the array:
Sub GetBiggest()
Dim oWSF As WorksheetFunction
Dim vaArray As Variant
Set oWSF = Application.WorksheetFunction
vaArray = Array(10, 20, 13, 15, 56, 12, 8, 45)
MsgBox “Biggest is “ & oWSF.Max(vaArray)
End Sub
WorksheetView Object
The WorksheetViewobject contains various properties that determine how certain values and objects
ona given worksheet are displayed. Along with the common properties of ApplicationCreator, and
Parent, the WorksheetViewobject contains the following properties.
WorksheetView Properties
Name
Returns
Description
Display
Boolean
Set/Get whether the formulas are displayed on the 
Formulas
worksheet 
Display
Boolean
Set/Get whether the gridlines are displayed on the 
Gridlines
worksheet
DisplayHeadings
Boolean
Set to Trueto display both row and column headings.
Setto Falseto display no headings 
DisplayOutline
Boolean
Set/Get whether outline symbols are displayed on the
worksheet
DisplayZeros
Boolean
Set/Get whether the zero values are displayed on the
worksheet
Sheet
Object
Read-only. Returns the sheet associated with the specified
WorksheetViewobject
XmlDataBinding Object
The XMLDataBindingobject represents the connection to the data source for an XMLMap. Along with
the common properties of ApplicationCreator, and Parent, the XMLDataBindingobject contains
the SourceURLproperty. The SourceURLproperty returns a string variable that represents the path to
the XMLdata file or the URLthat provides the source data for the specified data binding. See Chapter 12
for examples of how to use the XMLDataBindingobject.
XmlDataBinding Methods
Name
Returns
Parameters
Description
ClearSettings
Clears all settings for the current
object 
LoadSettings
UrlasString
Loads a set of settings
Refresh
xlXmlImport
Refreshes all data 
Result
XmlMap Object and the XMLMaps Collection
The XMLMapobject represents an XMLMap that has been added to a workbook. The XMLMapscollection
contains all of the XMLMapobjects within a workbook. Along with the common collection attributes, the
XMLMapscollection has an Add method that allows you to add a new XMLMapobject to the collection. 
967
WorksheetView Properties
Documents you may be interested
Documents you may be interested