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
Introduction New Objects New Properties (by Object) New Properties (Alphabetic List) New Methods (by Object) New Methods (Alphabetic List) New Events Hidden Objects Hidden Properties and Methods Methods with New, Changed, or Deleted Arguments Language-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