Freezed rows and incorrect display of active cells in Excel 2010
08.07.2010
Like in my previous post, I found this strange behaviour when testing some sample files for my next article about PowerPivot in a computer magazine. In some cases, Excel 2010 does not correctly display the selected cell, especially if you are freezing rows. This behaviour does not appear in Excel 2007.
To reproduce the error, you may first create a new workbook in Excel 2010 and store the file. It does not matter whether you choose the old file format XLS or the new modern XLSX format. I created the two sheets „Fixed“ and „Standard“ in my workbook and formatted some cells as you can see in the screenshots below. Please note, that only Excel 2010 in German is currently available for me, but I think, this will be suffisent for the demonstration.
The areas I to IV are containing merged cells. And the pane was freezed between row 7 and 8. If you select cell D2 by clicking with the mouse on this cell, then select the worksheet „Standard“ and go back to the sheet „Fixed“, the selected cell is correctly displayed.
An interesting effect occurs if you click on other cells. The follwing series of screenshots shows this behaviour of Excel 2010. First, I selected area I, as you can see below:Continue…
German translation error for the zoom selection in Excel 2010
08.07.2010
When testing the sample files for an article for the well known german computer magazine c't, I noticed a small but confusing translation error in Excel 2010.
The function „Zoom to Selection“ is incorrectly translated to „Fenster einfrieren“ in German instead of „Zoommodus: Auswahl“ (like in Excel 2007). Unfortunately „Fenster einfrieren“ means „Freeze panes“ and this feature is located next to the incorrect button on the ribbon tab. However, when clicking on the incorrect translated button, the right function „Zoom to Selection“ is executed, as you can see on the two screenshots below for Excel 2007 and 2010 in German.
The head line for the help window is also incorrect in german, the help text is however correct.
Since I could not remember whether the error was already present in the beta of Excel 2010, I tested this in a virtual machine with the Beta installed. Interestingly, there, the designation of the functon is correct.
Positioning of controls using VBA, Bug in Excel 2010?
08.07.2010
When testing an own developped VBA application in Excel 2010, I noticed a curiosity, which I personally would classify as a bug in Excel 2010.
My application is using some form controls, especially checkboxes which are hidden or unhidden per code. And the cells containing the controls are also hidden or unhidden per code. The error in Excel 2010 appears when following steps are done:
- Hide the form controls per code (Visible = False).
- Hide the rows containing the controls.
- Save and close the workbook.
- Open the workbook again.
- The form controls are now all on top and lost their original positions.
This problem does not appear in Excel 2007. Now, for getting sure that this is actually a problem of Excel 2010, I wrote a small sample application, which positioned 9 checkboxes as shown in the screenshots below. Please note, that I haved used the german version of Excel for the screenshots as the english version of Excel 2010 is not available yet for me. The properties for each control are set to "Move but don't size with cells" (in German "Nur von Zellposition abhängig"). The two screenshots below show the worksheet in Excel 2007 and Excel 2010 in the initial state without any influence from VBA code.
Continue…
The administrative Templates for Office 2010 and Office 2007
21.03.2010
Microsoft offers for several Office versions the administrative tempates, which especially allows system administrators to maintain and configure various settings and options for Microsoft Office in a very convenient way. The templates are can be downloaded for free at the Microsoft Download Center.
So, everyone can use them, for example at home or in the small office. However, if you use them, please do this very carefully. Most settings are explained in the templates and also accessible using the Office interface. However, some of them are only accessible (excepted some uncomfortable registry hacks) via the administrative templates and you should know what you are doing before changing these values.
In this article, I will explain how to use the administrative templates on a local computer and show some sample settings for Office 2007 and Office 2010. If I got some spare time, I will report about more settings for the Office applications in further posts. First, we have to download the adminstrative templates for each Office version and install them. This can be done by just executing the downloaded file, which unpacks the templates to a folder you can choose. The following image shows a screenshot for the folder structure after unpacking the files:
Continue…
Comparison of GAMMALN() in Excel 2010, 2007 and 2003
15.03.2010
Some time ago, I read the interesting blog post „Function Improvements in Excel 2010” on the Microsoft Excel Team Blog. The reports about the changed formulas in Excel 2010 and about the improvements made to the calculation algorithms for these formulas. I’m not a statistician and I can’t really use all those statistical formulas, but I’m curious and I would like to examine how the results returnes by the affected formulas distinguish in the different Excel Versions. For doing this, I have choosen the GAMMALN() function because this formula just only needs one argument. You can download a sample workbook at the end of this article.
Continue…
The changes to AxisTitle and ChartTitle in Excel 2010 VBA
05.03.2010
In Excel 2007, it is possible to link a chart or an axis title to a cell by using a formula. However this was not possible when using VBA. The correponding objects have now been extended in Excel 2010, as you can see in the sample file which can be downloaded at the end of this article. The following image shows a screenshot of this sample file.
Continue…
The changes to the AddIn objects in Excel 2010
22.02.2010
Today, I had a closer look on the changes to the “AddIn” object and the new object “AddIns2” in Excel 2010. The “AddIn” object has been only extended by the property “IsOpen”, allowing to check programmatically if a specific Add-In is loaded or not. The new object “AddIns2” lists, unlike the already existing object “AddIns”, all available Add-Ins regardless if they have been installed or not. The following screenshot demonstrates this behaviour. First, I loaded the Add-In “Test.xla” by opening the file with the file open dialog.
Continue…
The new VBA features in the Workbook object in Excel 2010
18.02.2010
Based on the results of my tools for comparing the VBA functions between Excel 2010, Excel 2007 and Excel 2003, I think the next logical step should be the systematic testing of new methods, events, properties, and constants.Unfortunately, the documentation for the new VBA features is still incomplete on my installed version. However the purpose of some new features can often be guessed by their name. But I think this will not always be possible.
The last days, I took a look on the objects „Workbooks“, „Workbook“ und „AppEvents“. While I did not find changes for the workbooks collection, the „Workbook“ object includes some new events, methods and properties which seems to be very interesting.
Continue…
VBA Objects found in the MSO.DLL for 2010, 2007 and 2003
16.02.2010
Microsoft Office also includes a global library, which you usually should find in the "Microsoft Shared\OfficeNN" folder of your Common Files folder. My tools retrieves the objects, methods, etc you can find in the downloadable file at the end of this post.
Continue…
VBA Objects in Access 2010, Access 2007 and Access 2003
16.02.2010
I also scanned the different versions of MS Access and stored the results in an Excel file.
Continue…
VBA Objects in PowerPoint 2010, 2007 and 2003
16.02.2010
Today, I have scanned the PowerPoint VBA Libraries for the versions 2010, 2007 and 2003 and exported the results to a separate Excel file, which you can download at the end of this article.
Continue…
VBA Objects in Word 2010, Word 2007 and Word 2003
15.02.2010
I have now scanned the Word 2010, 2007 and 2003 VBA Libraries on my system and exported the results to a separate workbook. You will find the download link for this file at the end of this article.
Continue…
VBA Objects in Excel 2010, Excel 2007 and Excel 2003
15.02.2010
In my previous post „Excel VBA Type Library Scanner“, I had presented my application for scanning and parsing type libraries. However, my application needs the TLI Library „TlbInf32.dll“ to be installed and registered on the system where my application will run. For those people who do not want to install the TLI Library or have not access to it, I have now scanned the Excel 2010, 2007 and 2003 VBA Libraries on my system and exported the results to a separate workbook. You will find the download link for this file at the end of this article.
Continue…
Excel VBA Type Library Scanner
15.02.2010
In my article “The most important changes to VBA in Excel 2010“ from 08/05/2009, I had already reported about some changes to the VBA object model in Excel 2010. Also, I had started the development of an application for parsing and analysing all VBA objects, methods, properties and constants of the VBA libraries included in the Office apps. Just recently, I got the time to finish the development of this application.The application is freeware and can be downloaded at the end of this article. The output tables are protected, however the password can be found in code which is unprotected and can be viewed by everyone. Please note, that the workbook itself and the copyright sheet are also protected by a password and this password is not publicly accessible.
The application referencies to the TLI Library „TlbInf32.dll“ in the windows system folder providing functions for scanning type libraires. Important note: the TLI Library needs to be installed and correctly registered on your system. My application can not work without this library. And please note that this library is not included in the download archive.
Continue…
Comparison and function list of Excel 2010, 2007 and 2003
16.01.2010
Some people from my english speaking audience asked me a few days ago, if it may be possible to translate my german article published on 11/27/2009 about the comparison between the functions included in Excel 2003, 2007 and 2010. The file listing the functions of the different Excel versions is also presetted to English and can be downloaded at the end of this article. Below a screenshot from the worksheet:
Continue…
The Office 2010 Developer Training Kit Beta
09.01.2010
Recently, I found the download link for the „Office 2010 Developer Training Kit“ on Twitter and on the german blog from Tom Wendel. The Training Kit has been mainly created and tested by Paul Stubbs, Girish Raja, Steve Fox, Donovan Follette and Chris Mayo. As far as I know, the Training Kit is only available in English (I'm from Germany). The Training Kit provides many useful and interesting informations about Office 2010 and also exercises for developers.
Continue…
Word and the legal dispute i4i against Microsoft
27.12.2009
While visiting my favourite blogs, I discovered the very interesting article „What is "Custom XML?" ... and the impact of the i4i judgment on Word“ from Gray Knowlton published on 23. December 2009. One day before, the U.S. Court of Appeals for the Federal Circuit upheld the trial court verdict rendered in favor of plaintiff i4i Limited Partnership and also upheld the permanent injunction issued against Microsoft barring future sales of Microsoft Word products containing the Custom XML features.
You can see the affected features of Word in question if you insert custom XML tags to your documents. They are made visible in the document by being highlighted in pink as shown in the screenshot above. The judgement will now have some impacts on these features in future versions of Word.
Continue…
Networkdays.Intl() and Workday.Intl() in Excel 2010
12.12.2009
This article is mainly a translation of my correspondung German article. However, as I used the German version of Office 2010 for taking the screenshots and this will not be really suitable for my english speaking audience, I will first change the display and help files language in Office 2010 from German to English. This can easily be done, as the language packs are now available. Thanks to my friends on Twitter for this info.Then I will check out the new Excel 2010 functions NETWORKDAYS.INTL() and WORKDAY.INTL().
Continue…
The Office 2010 VBA Code Compatibility Inspector - OCCI
12.12.2009
As mentionned in my previous article, I'm following the excellent blog posts from Gray Knowlton. Gray already presented there the „Microsoft Office 2010 Code Compatibility Inspector - shortly OCCI“, I would like to report about my experiences with this tool. This application is currently at its beta stage, so we can't expect all will work fine.First, lets download the application from the Microsoft Download Center and the unpack the contents of the zip archive. I my case, trying to unzip the file to an inexistent folder on my hard disk leads to an error message telling me that the folder can not be created. No matter, I unzipped the archive contents to the folder proposed by OCCI. This works. The we can call the OCCI installer.
Continue…
The Office 2010 Application Compatibility Program Tool - OEAT
12.12.2009
I'm regularly visiting the blog from Gray Knowlton who leads the product managment team for Office Developers. Among other very interesting articles about MS Office 2010, he had announced two applications, I have been impatiently waiting for. These apps are now available and can be downloaded from the Microsoft Download Center.The first application help the users and administrators to evaluate and check out incompatibilities when installing or upgrading to MS Office 2010. The second application inspects your VBA Code for incompatibilites. This article presents the „Office Environment Assessment Tool - shortly OEAT“. In a further post, I will present the „Office VBA Compatibility Inspector“.
First we have to download OEAT from the Microsoft website. After the download, just unzip the OEAT application in a folder of your choice. When running the tool, you'll see following screen:
Continue…
Overview of my articles about the Office TP
10.12.2009
This article lists the most important articles I have posted about the Technical Preview of Office 2010. At this point, thanks to all visitors and the positive feedback via email. Office 2010 Beta is now publicly accessible and I will also write some articles about the new version. However, most informations of my previous posts are also applicable to the Beta. I have sorted this list by publishing date.
Continue…
Office 2010 Beta website online
14.11.2009
The Office 2010 Beta Version Website is now online. However ths link for downloading the beta version does not work, but we are sure this will be changed in a few days. You can also find more information about the new and improved features of Microsoft Office and the Office WebApps on the site.
Once the download is available, we will also take a closer look at Office 2010 and write about our experiences on our blog; so, please stay tuned :-)
VBA Performance in Excel 2010 - Part 1
27.09.2009
I'm currently developing a tool for comparing the VBA performance of Excel 2010 with Excel 2007 and Excel 2003. In its actual version, this tool is able to measure the time used when running nested loops and when creating shapes. In this first article of a series I would like to presents my first results.
The first test I have done, was to run a nested loop in the three versions of Excel. I used Windows API functions to measure the time, as they allow more precise measurements. The test can be repeated several times to become a little more meaningful. The tool stores the test results in a report sheet, which also calculates the average of the tests results.
Continue…
First impressions of the Excel Web App
27.09.2009
Yesterday, I translated and published my article how to access to the Technical Preview of the Office Web Apps. Today, I will write about my first impressions of the Excel Web App. If you are not already participant to the Office Web Apps TP, please read the article Office Web Apps Technical Preview where you will find some explanations how to register for and use the TP.Let's now have a look at the Excel Web App. First, I login to Windows Live and navigate to SkyDrive as you can see in the following picture. As I'm usually using Windows Live in German, I had to change the language to English. If you don't know how to change your language, this is also explained in the article mentionned above.
For the current test, I used the Firefox 3.5 Browser. On one hand, I was curious if the Excel Web App will work properly in another browser than the Internet Explorer and on the other hand, Firefox is quite common in Germany. Let's navigate to the folder „My Documents“. Then we have to click on „New“ and select „Microsoft Excel Workbook“ for creating a new workbook.
Continue…
Office Web Apps Technical Preview
26.09.2009
A few days ago, I wrote this article in German, here is now my translation for our english speaking friends. According to the Microsoft Office 2010 Newsgroup and the Microsoft Office 2010 Engineering Blog, the Office Web Apps have been made available and can be tested. Even if they primarly targeted a selected group of users, with a little trick, everyone who has an account on Windows Live can get access to the Office Web Apps.At this stage, only Excel, Word, Powerpoint and OneNote are available. Actually the functionality is modest, but I think, they are working hard at Microsoft to implement more functions. By the way, according the french blog Backstage 2010, the official names of the apps will be „Excel Web App“, „Word Web App“, „PowerPoint Web App“ and „OneNote Web App“. The suite will be designated „Office Web App“.
In the rest of this article I will describe how to get access to the Office Web Apps TP from a German Windows Live account. As you probably know, I am participating in the technical preview of Office 2010. So, for testing if I can get access to the Office Web App from a regular Windows Live account, I created a new one.
Continue…
An introduction to the Gemini Add-In for Excel 2010
08.09.2009
The „Project Gemini“ is the code name for an Excel 2010 Add-In, which allows the user to use real „Self Service Business Intelligence“. Gemini consists of the three following components:| - | An Excel Add-In with own interface and own ribbon. If you use the Add-In, you'll be able to imports millions of rows, create relationships between them, calculate values in new columns using formulas and create attractive Pivot Charts. You don't need to learn database query languagues like T-SQL or MDX. And, in additionl, you can publish your solutions on a sharepoint server. |
| - | There is also an Add-In availaible for Sharepoint 2010. This Add-In allows you, for example, to manage the workbooks published with Gemini, assign some rights or create more reports. Please note that the Excel Services should be installed and activated. |
| - | The last component is the new memory engine, using a column-based compression. Exactly this engine allows you quickly and easily manage and include the millions of rows. |
Microsoft let me also participate to the Gemini preview; thanks for that! The Gemini Team provided to the participants some samples and tutorials for the first steps in Gemini. In these samples are included data files for use with the Microsoft SQL Server and also an Access Database for the people who have not access to a SQL Server. Throughout this article, I lean to some steps of the tutorial. For simplicity, I used the Access Database for my first steps with Gemini. Another reason is that on one hand I'm not an expert for SQL Server and an another hand, I should have to install it, which is not possible at this time.
Continue…
The new features for image editing in Excel 2010
06.09.2009
Today we will have a look on the new image editing functions of Excel 2010. Please note, that in general the discussed features below also applies to the other programs in Office 2010 like Word or Powerpoint. In addition to a comparison of the image precessing features of Excel 2007 and Excel 2010, we will also discuss the new Background Removal Tool.
In a first step, I opened Excel 2010 and inserted an image in a worksheet. I choosed an image which I personally use for my avatar in some forums. And I colored the excel sheets' background to green.
Continue…
More interesting new features in Excel 2010
16.08.2009
In our previous contributions we have already - sometimes more or less detail - presented some new features in Excel 2010. However there are some more new features (such as screenshots) we would like to talk about in this and subsequent articles. We will compare the features of Excel 2007 with them in Excel 2010. As I usually use the german version of Excel 2007, I first changed the language to English before writing this article. Some of the figures below show Excel 2007 in the background and Excel 2010 in the foreground for better comparing. So, let us begin by comparing the ribbons. Please note, that you can click on some images to load a larger view.
Continue…
The most important changes to VBA in Excel 2010
10.08.2009
Update from 02/18/2010: I have recently published some new articles about this subject. You can reach these posts by clicking on one of the following links:
- Excel VBA Type Library Scanner
- VBA Objects in Excel 2010, Excel 2007 and Excel 2003
- VBA Objects in Word 2010, Word 2007 and Word 2003
- VBA Objects in PowerPoint 2010, 2007 and 2003
- VBA Objects in Access 2010, Access 2007 and Access 2003
- VBA Objects found in the MSO.DLL for 2010, 2007 and 2003
Continue…
The changes to the Conditional Formattings in Excel 2010
10.08.2009
Some changes and additions were made to the Conditional Formatting dialogs and options. In this article we will briefly present the main points. For doing this, I first created a sheet, entered some values and then choosed the menue "Conditional Formatting" from the "Home" tab. Then I selected "Data Bars" from the submenue.
Continue…
The most important changes for Excel 2010 Charts
10.08.2009
Let's have a look to the most important (in our opinion) changes for Excel 2010 Charts. First, from out view, the best news first: a double-click on on the diagram or elements in the diagram, such as axes, columns or titles calls the appropriate dialogue with the settings! This feature was disabled in Excel 2007 and is now available again. Well done :-)
The settings dialog is also, like in Excel 2007, not modal, so you can select other chart elements and the dialog adapts respectively. According to Microsoft, the speed of calculation and presentation of charts in Excel 2010 and have been improved. In addition, more data points should be displayed.
That was something I was curious about, so I initially created a chart with 9999 data points in Excel 2010
Continue…
Using Word 2010 and Word 2007 on the same system
10.08.2009
In some cases - if Word 2010 and Word 2007 are installed on the same system - it may happen that Word 2007 tries to reconfigure itself by opening the installer first each time you start Word 2007. I also encountered this problem on a parallel installation of Word 2007 and Word 2003.
This behaviour of Word 2007 can be avoided by creating and/or setting the registry entry "NoRereg" for Word 2007. Ok, this tip isn't very new, however I'll mention it here, because this problem with 2007 was a little bit annoying.
Please consider this important note: changing values in the Windows Registry can in worst case make your software or operating system unusable. So be careful, we assume no liability for any damages.
Continue…
The Equation Editor in Excel 2010
09.08.2009
Let's have a look to the Equation Editor in Excel 2010. That's right, this editor is now also available in Excel 2010 and is similar to the equation editor already present in Word. However, we can only insert a new equation in Excel 2010 if we have inserted a textbox before. At this point, thanks to Ken Puls from the Office 2010 TP Newsgroups for this tip:-)Ok, let's insert a new textbox like shown in the next figure:
Continue…
The changed copy and paste options in Excel 2010
09.08.2009
Microsoft has modified the copy and paste functionality in the different Office 2010 applications. Let's have a look how this works in Excel 2010.
As you can see, some new icons were added. And the best: like in other functions (for example for changing the font size), when hovering with the mouse over icons you'll see the changes in real time.
Continue…
The new functions in Excel 2010
09.08.2009
In this article, we will talk about the modified or extended and new functions/formulas in Excel 2010. Most extensions have been made in the statistics category. Please note, that we are testing the Technical Preview version of Excel 2010, so some functions may be added, removed or modified in the final version.Like in Excel 2007, Excel 2010 uses IntelliSense when entering a function in a cell. For those, who don't know what IntelliSense ist, I took a screenshot when entering the function.
Continue…
The new Sparklines in Excel 2010
09.08.2009
Let's today have a look on a new interesting feature of Excel 2010: Sparklines. These are small intense, simple, wordlike graphics for presenting trends and variations associated with some measurement. Sparklines normally don't show axes or values. There are some products for older versions of Excel, you'll find a link to an open source product at the end of this article. Following table figures out the different types of Sparklines in Excel 2010.| - | Line | : | Used to display a line chart in a cell. |
| - | Column | : | Used to display a column chart in a cell. |
| - | Win/Loss | : | Used to display a win/loss chart in a cell, the columns have the same height. |
Continue…
The new options in Excel 2010
09.08.2009
Some new options were added to Excel 2010 which we would like to present in this article. Please note, that the current version of Excel 2010 is a Technical Preview, so some options may be changed, removed or added in the final version. We call call the Excel Options using the Office Backstage. The item "General" in the dialog is selected first, as you can see in the following figure.
Continue…
The Office Backstage in Excel 2010
08.08.2009
After starting Excel 2010, the software presents itself as you can see in the following figure. At first glance, the Ribbons and symbols seems not to have been changed. However, we can notice that the Office Button has now a new look.
Continue…
The new Excel 2010 Splash Screen
01.08.2009
After installing Office 2010 we were very curious how it looks like. When starting Excel 2010, we are welcome by the new Splash Screen, which also shows the load progress of the installed Add-Ins.Note, if you're opening an Excel file by double clicking in the Windows Explorer, the load progress is additionally shown in the Splash Screen. However, double clicking the files doesn't work on all Windows installations.
Blog
News
