Object Model Changes for Developers Between Excel 2003 and Excel 2007

  • Article

There a lot of changes between Excel 2003 and Excel 2007 for developers.  Here are some highlights that I found interesting in the object model.  Note that you should use the trick I blog about in this post for more detailed comparisons.  There is also a nice resource on MSDN to see object model changes here: https://msdn.microsoft.com/en-us/library/bb149069.aspx

New Object Model Objects

Excel 2007 introduces 51 new objects to the object model. These new objects are, listed alphabetically: AboveAverage, Action, Actions, ChartFormat, ChartView, ColorScale, ColorScaleCriteria, ColorScaleCriterion, ColorStop, ColorStops, ConditionValue, Connections, Databar, DialogSheetView, FormatColor, HeaderFooter, Icon, IconCriteria, IconCriterion, IconSet, IconSetCondition, IconSets, LinearGradient, ModuleView, MultiThreadedCalculation, ODBCConnection, OLEDBConnection, Page, Pages, PivotAxis, PivotFilter, PivotFilters, PivotLine, PivotLineCells, PivotLines, Ranges, RectangularGradient, Research, ServerViewableItems, SheetViews, Sort, SortField, SortFields, TableStyle, TableStyleElement, TableStyleElements, TableStyles, Top10, UniqueValues, WorkbookConnection, and WorksheetView.

Events

Application.AfterCalculate is a new event in Excel 2007 that is raised when all calculations that are pending are complete. This event can be use to determine if all workbooks have been completely updated by any calculations or queries that are in progress.

New Functions in Excel 2007

There are 98 new functions in Excel 2007. The new functions in alphabetical order are AccrInt, AccrIntM, AmorDegrc, AmorLinc, AverageIf, AverageIfs, BesselI, BesselJ, BesselK, BesselY, Bin2Dec, Bin2Hex, Bin2Oct, Complex, Convert, CountIfs, CoupDayBs, CoupDays, CoupDaysNc, CoupNcd, CoupNum, CoupPcd, CumIPmt, CumPrinc, Dec2Bin, Dec2Hex, Dec2Oct, Delta, Disc, DollarDe, DollarFr, Duration, EDate, Effect, EoMonth, Erf, ErfC, FactDouble, FVSchedule, Gcd, GeStep, Hex2Bin, Hex2Dec, Hex2Oct, IfError, ImAbs, Imaginary, ImArgument, ImConjugate, ImCos, ImDiv, ImExp, ImLn, ImLog10, ImLog2, ImPower, ImProduct, ImReal, ImSin, ImSqrt, ImSub, ImSum, IntRate, IsEven, IsOdd, Lcm, MDuration, MRound, MultiNomial, NetworkDays, Nominal, Oct2Bin, Oct2Dec, Oct2Hex, OddFPrice, OddFYield, OddLPrice, OddLYield, Price, PriceDisc, PriceMat, Quotient, RandBetween, Received, SeriesSum, SqrtPi, SumIfs, TBillEq, TBillPrice, TBillYield, WeekNum, WorkDay, Xirr, Xnpv, YearFrac, YieldDisc, and YieldMat.

New Built-in Document Properties

Content type
Content status
Document version
Language

Table versus List versus ListObject

Tables were introduced in Excel 2003 but in that version of Excel they were called lists. In Excel 2007, the naming was changed in the Excel UI to Table. However, the object model object representing a table is called ListObject reflecting the earlier naming in Excel 2003. No matter what you call them (Tables/Lists/ListObjects) they are a key feature of Excel that is well supported by VSTO—VSTO allows you to do complex data binding to Table.

There are also some big additional changes that go beyond object model like Ribbon support, application level task panes, and ClickOnce support. But these are some highlights in the object model.