What's New for Microsoft Excel 2000 Developers

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Wade Tai
Microsoft Corporation

Created: February 2000
Updated: November 2000

Applies To: Microsoft Excel 2000

Summary: Lists and describes the major feature changes made to Visual Basic for Applications in Microsoft Excel 2000. (12 printed pages)

Contents

IntroductionNew ObjectsNew Properties (by Object)New Properties (Alphabetic List)New Methods (by Object)New Methods (Alphabetic List)New EventsHidden ObjectsHidden Properties and MethodsMethods with New, Changed, or Deleted ArgumentsLanguage-Specific Properties, Methods, and Functions

Introduction

The Microsoft® Excel 2000 Visual Basic® for Applications (VBA) object model includes many changes that have been made to support new and improved features in Excel. To provide backward compatibility, replaced components have been hidden rather than removed. This means that they don't show up in the Object Browser by default, but old code that uses the hidden components will still work correctly without modification. When you write new code, however, you should use only the new objects, properties, and methods.

The major feature changes made to Visual Basic for Applications in Microsoft Excel 2000 are listed and described in the following table.

Feature Description
ADO Provides a direct database access interface. ADO is now the preferred data access method in Excel.
Component Object Model (COM) add-ins Provide a consistent user interface in which developers can create add-ins in any programming language that supports COM (Microsoft Visual C++®, Visual J++®, and Visual Basic, for example).
Digital signature Provides a way to determine whether the Visual Basic for Applications project of the workbook has been digitally signed.
Display unit labels Provides unit labels to make tick-mark labels on chart axes easier to read.
Microsoft Office E-mail Provides integrated e-mail capabilities.
Microsoft Office Web Components Provides a way to save spreadsheets, charts, and PivotTable® reports as interactive Web pages.
Multilingual editing Provides improved multilingual user interface and editing features, and improved phonetic text handling for East Asian language support.
OLAP data sources for PivotTable and PivotChart reports Provide a means for viewing and analyzing data from Online Analytical Processing (OLAP) data servers, which store and organize data to facilitate analysis of business trends and results.
PivotChart reports Provides an interface to PivotChart™ reports, which are based on data from PivotTable reports.
Save to the Web and Office Web Editing Provides Web page authoring, saving, and editing features. Also provides an interface for saving Microsoft Office Web Components.
Text file and Web Queries Provides new Web page query and text file importation enhancements.
Visual Basic For Applications 6.0 Provides the latest language features, as well as modeless user forms and support for additional ActiveX® controls.
Web page scripting Provides Web page scripting features for HTML-based client solutions.

New Objects

Objects that have been added to Visual Basic for Applications (VBA) in Excel 2000 are listed in the following table.

Objects and collections Description
CubeField, CubeFields Provide new OLAP PivotTable report functionality.
DefaultWebOptions, PublishObject, PublishObjects, WebOptions Provide new Web page authoring, editing, and saving functionality.
DisplayUnitLabel Provides new unit labels to make the tick-mark labels on your chart axes easier to read.
OLEDBError, OLEDBErrors Provide new OLE DB error objects.
Phonetics Provides improved phonetic text handling for East Asian language support.
PivotLayout Provides new PivotChart report functionality.

New Properties (by Object)

In Excel 2000, many new VBA properties have been added to existing objects. These properties are listed in the following table, sorted by object name.

Object Property
Application AnswerWizard, AutoPercentEntry, CalculationVersion, COMAddIns, DefaultWebOptions, ExtendList, FeatureInstall, LanguageSettings, OLEDBErrors, ProductCode, ShowWindowsInTaskbar, UserLibraryPath
Axis DisplayUnit, DisplayUnitCustom, DisplayUnitLabel, HasDisplayUnitLabel
Chart HasPivotFields, PivotLayout, Scripts
DataLabel NumberFormatLocal
DataLabels NumberFormatLocal
Hyperlink EmailSubject, ScreenTip, TextToDisplay
OLEObject AltHTML
OLEObjects AltHTML
Parameter RefreshOnChange
PivotCache CommandText, CommandType, LocalConnection, MaintainConnection, QueryType, Recordset, RefreshPeriod, UseLocalConnection
PivotField Caption, CubeField, CurrentPageName, DragToData, DrilledDown, LayoutBlankLine, LayoutForm, LayoutPageBreak, LayoutSubtotalLocation, SubtotalName, Subtotals
PivotItem Caption, DrilledDown
PivotTable CubeFields, GrandTotalName, PrintTitles, RepeatItemsOnEachPrintedPage, SmallGrid
QueryTable AdjustColumnWidth, CommandText, CommandType, MaintainConnection, PreserveColumnInfo, PreserveFormatting, QueryType, RefreshPeriod, TextFileColumnDataTypes, TextFileCommaDelimiter, TextFileConsecutiveDelimiter, TextFileDecimalSeparator, TextFileFixedColumnWidths, TextFileOtherDelimiter, TextFileParseType, TextFilePlatform, TextFilePromptOnRefresh, TextFileSemicolonDelimiter, TextFileSpaceDelimiter, TextFileStartRow, TextFileTabDelimiter, TextFileTextQualifier, TextFileThousandsSeparator, WebConsecutiveDelimitersAsOne, WebDisableDateRecognition, WebFormatting, WebPreFormattedTextToColumns, WebSelectionType, WebSingleBlockTextImport, WebTables
Range ID, Phonetics
Shape AlternativeText, Script
ShapeRange AlternativeText
TickLabels Alignment, Depth, NumberFormatLocal, Offset
Workbook CalculationVersion, EnvelopeVisible, HTMLProject, PublishObjects, VBASigned, WebOptions
Worksheet DisplayRightToLeft, Scripts

New Properties (Alphabetic List)

In Excel 2000, many new VBA properties have been added to existing objects. These properties are listed in the following table, sorted alphabetically by property name.

Property Object
AdjustColumnWidth QueryTable
Alignment TickLabels
AlternativeText Shape, ShapeRange
AltHTML OLEObject, OLEObjects
AnswerWizard Application
AutoPercentEntry Application
CalculationVersion Application, Workbook
Caption PivotField, PivotItem
COMAddIns Application
CommandText PivotCache, QueryTable
CommandType PivotCache, QueryTable
CubeField PivotField
CubeFields PivotTable
CurrentPageName PivotField
DefaultWebOptions Application
Depth TickLabels
DisplayRightToLeft Worksheet
DisplayUnit Axis
DisplayUnitCustom Axis
DisplayUnitLabel Axis
DragToData PivotField
DrilledDown PivotField, PivotItem
EmailSubject Hyperlink
EnvelopeVisible Workbook
ExtendList Application
FeatureInstall Application
GrandTotalName PivotTable
HasDisplayUnitLabel Axis
HasPivotFields Chart
HTMLProject Workbook
ID Range
LanguageSettings Application
LayoutBlankLine PivotField
LayoutForm PivotField
LayoutPageBreak PivotField
LayoutSubtotalLocation PivotField
LocalConnection PivotCache
MaintainConnection PivotCache, QueryTable
NumberFormatLocal DataLabel, DataLabels, TickLabels
Offset TickLabels
OLEDBErrors Application
Phonetics Range
PivotLayout Chart
PreserveColumnInfo QueryTable
PreserveFormatting QueryTable
PrintTitles PivotTable
ProductCode Application
PublishObjects Workbook
QueryType PivotCache, QueryTable
Recordset PivotCache
RefreshOnChange Parameter
RefreshPeriod PivotCache, QueryTable
RepeatItemsOnEachPrintedPage PivotTable
ScreenTip Hyperlink
Script Shape
Scripts Chart, Worksheet
ShowWindowsInTaskbar Application
SmallGrid PivotTable
SubtotalName PivotField
Subtotals PivotField
TextFileColumnDataTypes QueryTable
TextFileCommaDelimiter QueryTable
TextFileConsecutiveDelimiter QueryTable
TextFileDecimalSeparator QueryTable
TextFileFixedColumnWidths QueryTable
TextFileOtherDelimiter QueryTable
TextFileParseType QueryTable
TextFilePlatform QueryTable
TextFilePromptOnRefresh QueryTable
TextFileSemicolonDelimiter QueryTable
TextFileSpaceDelimiter QueryTable
TextFileStartRow QueryTable
TextFileTabDelimiter QueryTable
TextFileTextQualifier QueryTable
TextFileThousandsSeparator QueryTable
TextToDisplay Hyperlink
UseLocalConnection PivotCache
UserLibraryPath Application
VBASigned Workbook
WebConsecutiveDelimitersAsOne QueryTable
WebDisableDateRecognition QueryTable
WebFormatting QueryTable
WebOptions Workbook
WebPreFormattedTextToColumns QueryTable
WebSelectionType QueryTable
WebSingleBlockTextImport QueryTable

New Methods (by Object)

In Excel 2000, many new VBA methods have been added to existing objects. These methods are listed in the following table, sorted by object name.

Object Method
Application CalculateFull, GetPhonetic
Hyperlink CreateNewDocument
Pane ScrollIntoView
PivotCache CreatePivotTable, ResetTimer
PivotCaches Add
PivotTable Format
PivotTables Add
QueryTable ResetTimer
Range SetPhonetic
Window PointsToScreenPixelsX, PointsToScreenPixelsY, RangeFromPoint, ScrollIntoView
Workbook ReloadAs, WebPagePreview

New Methods (Alphabetic List)

In Excel 2000, many new VBA methods have been added to existing objects. These methods are listed in the following table, sorted alphabetically by method name.

Method Object
Add PivotCaches, PivotTables
CalculateFull Application
CreateNewDocument Hyperlink
CreatePivotTable PivotCache
Format PivotTable
GetPhonetic Application
PointsToScreenPixelsX Window
PointsToScreenPixelsY Window
RangeFromPoint Window
ReloadAs Workbook
ResetTimer PivotCache, QueryTable
ScrollIntoView Pane, Window
SetPhonetic Range
WebPagePreview Workbook

New Events

Two new Visual Basic for Applications events have been added to Microsoft Excel 2000. These events are listed in the following table.

Event Object
FollowHyperlink Worksheet
SheetFollowHyperlink Application, Workbook

Hidden Objects

No objects have been newly hidden in the Excel 2000 Visual Basic for Applications object model. Typically, hidden objects are supported only for backward compatibility; for new code, you should use the replacement functionality provided. To view hidden objects in the Object Browser, right-click anywhere in the Object Browser window and then click Show Hidden Members on the shortcut menu.

Hidden Properties and Methods

Hidden properties for visible objects in Microsoft Excel 2000 are listed in the following table. No methods have been hidden. Most of the hidden properties have been replaced by new Excel functionality. These properties are supported only for backward compatibility; for new code, you should use the replacement functionality provided in Excel 2000.

Object Hidden property Replacement
Application UILanguage LanguagePreferredForEditing
PivotCache, QueryTable Sql CommandText
QueryTable TablesOnlyFromHTML WebSelectionType

Methods with New, Changed, or Deleted Arguments

Methods with arguments that have been added to, changed in, or removed from Excel 2000 are listed in the following table.

Method Object Argument
Add Hyperlinks Added ScreenTip and TextToDisplay
CheckSpelling Chart, Range, Worksheet Added SpellLang and removed SpellScript, IgnoreInitialAlefHamza, and IgnoreFinalYaa (the last two arguments were not used in U.S. English versions of Microsoft Excel)
Find Range Removed MatchControlCharacters,MatchDiacritics, MatchKashida, and MatchAlefHamza (these arguments were not used in U.S. English versions of Microsoft Excel)
OpenText Workbooks Added DecimalSeparator and ThousandsSeparator
PivotSelect PivotTable Changed Mode to optional.
PrintOut Chart, Charts, Range, Sheets, Worksheet, Worksheets Added PrToFileName
Replace Range Removed MatchControlCharacters,MatchDiacritics, MatchKashida, and MatchAlefHamza (these arguments weren't used in U.S. English versions of Microsoft Excel)
Sort Range Removed IgnoreControlCharacters, IgnoreDiacritics, and IgnoreKashida (these arguments were not used in U.S. English versions of Microsoft Excel)
TextToColumns Range Added DecimalSeparator and ThousandsSeparator

Language-Specific Properties, Methods, and Functions

The Microsoft Excel 2000 Visual Basic for Applications object model has language-specific elements for use with Asian and right-to-left languages. For more information about language-specific features, type Language-specific information in the Office Assistant or on the Answer Wizard tab in the Microsoft Excel Help window, and then click Search.

Methods that have language-specific arguments are listed in the following table. Methods that have new arguments or fewer arguments than in Microsoft Excel 97 are noted.

Method Object Comments
Add Phonetics  
AddLabel Shapes  
AddTextbox Shapes  
AutoFormat Range  
CheckSpelling Application, Chart, Range, Worksheet Added SpellLang and removed IgnoreInitialAlefHamza and IgnoreFinalYaa
Find Application, Range Removed MatchControlCharacters, MatchDiacritics, MatchKashida, and MatchAlefHamza
GetPhonetic Application  
Replace Range Removed MatchControlCharacters, MatchDiacritics, MatchKashida, and MatchAlefHamza
SetPhonetic Range  
Sort Range Removed IgnoreControlCharacters,IgnoreDiacritics, and IgnoreKashida
SortSpecial Range  

Properties that return or set language-specific attributes are listed in the following table.

Property Object
AddIndent Range, Style
AddressLocal Range
Alignment Phonetic, Phonetics, TextEffectFormat, TickLabels
CharacterType Phonetic, Phonetics
ControlCharacters Application
CursorMovement Application
DefaultSheetDirection Application
DisplayRightToLeft Window, Worksheet
FileFormat Workbook
HorizontalAlignment AxisTitle, ChartTitle, DataLabel, DataLabels, DisplayUnitLabel, Range, Style, TextFrame
IMEMode Validation
International Application
Item Phonetics
Length Phonetics
Phonetic Range
PhoneticCharacters Characters
Phonetics Range
ReadingOrder AxisTitle, ChartTitle, DataLabel, DataLabels, DisplayUnitLabel, Range, Style, TextFrame, TickLabels
Start Phonetics
VerticalAlignment AxisTitle, ChartTitle, DataLabel, DataLabels, DisplayUnitLabels, Range, Style, TextFrame

The following are language-specific worksheet functions:

  • FindB
  • ReplaceB
  • SearchB
  • USDollar