Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

April 2008 - The Excel Team Blog

Sort by: Most Recent | Most Viewed | Most Commented

  • Append Multiple Text Files into a Worksheet without Code

    Today's author: Mark Gillis, an Excel and SharePoint writer, who's been through six versions of Office, survived to tell the tale, and picked up a thing or two along the way. Excel doesn't have an easy way to append multiple text files into one worksheet through the user interface. From time to time I hear customers asking how to do this in an easy way. Do you have to use Access or VB code to solve this problem? No. There's a way to do it in Excel by using a simple SQL statement in...
    Filed under:

  • Improving Sheet Selection

    Today's author: Sam Radakovitz, a Program Manager on the Excel team that enjoys creating VBA applications in Excel. A bit ago I got a question through email asking if we could change the 'grid selection color' in Excel. Or at least I called it 'grid selection color', it's basically what the cells look like when you select a block or multiple blocks of them: As you can see, the color is a bit light and hard for some folks to see, especially on a laptop if you're looking...
    Filed under:

  • Building the Excel Media Player – Part 3

    Last post we talked about how to use Excel as a flexible development environment and touched on how to use ActiveX Controls to enhance your solution and using tables programmatically to manipulate data. In this post we are going to drill into how to add custom Ribbon UI, context menus. Custom Context Menu In order to make playlist creation a lot easier in the Excel Media Player, I added the following custom right click context options to my Tables. To see how to do this open the MediaPlayer.xlsm, press Alt + F11 and double click ThisWorkbook. You will see the following code in the Workbook_Open() event: AddMenuItem "Add Selected Song", "AddSelected" AddMenuItem "Add Selected Artist", "AddArtist" AddMenuItem "Add Selected Album", "AddAlbum" AddMenuItem "Add Current Filter", "AddFilter" And Addmenu item this is defined below as: Private Sub AddMenuItem(title As String, command As String) Dim x As CommandBarControl Set x = Application.CommandBars("List Range Popup").Controls.Add(1, temporary:=True) x.Caption = title x.OnAction = command x.Visible = True End Sub CommnadBars("List Range Popup") is the bar for right click context menu for Lists/Tables. For cells, you can use CommandBars("Cells") instead. Using Status Bar and Application.ScreenUpdating When you are doing a lot of things in the background programmatically such as bringing in a lot of data (such as a media library) or modifying lots of charts, or anything that is time intensive, you probably want to do a combination of 3 things: Update status with info Turn off screen updating DoEvents Update Status: Excel's status bar can be written to by setting Application.StatusBar, so if you are in a loop it is useful to do something like Application.StatusBar = "Now Importing Item " & i & " of " & MC.Count so that the end user will know approximately the time remaining for your operation. One thing to note, if you don't set Application.StatusBar = "" the status bar will keep your last value until you exit the application (not just the workbook) or something else writes to the status bar. Turn off Screen updating If you are moving around lots of data and the user doesn't need to see what is going on set Application.ScreenUpdating = "False" before your code and Application.ScreenUpdating = "True" after your code completes. With this setting you will gain a slight performance increase and the user will not see flickering and data moving do to your code changes. DoEvents If, on the other hand, you want to allow the user to continue to use the app while the code is running (ie, if you have an animated object or a long loop) then you can sprinkle DoEvents into your loops or methods, for Example: While Range("isRunning").Value DoEvents Animate Wend Where I define Animate to modify a few objects. Finish up with custom Ribbon UI Before I go into detail on this topic, I want to point out a great primer that Sam Radakovitz wrote a few months back on Dynamic Ribbon Galleries . This is a great primer on how to customize the ribbons as are the links he provides are relevant to this post as well: "Office Fluent Ribbon Developer Portal http://msdn2.microsoft.com/en-us/office/aa905530.aspx Tools and Utilities for the 2007 Microsoft Office System http://msdn2.microsoft.com/en-us/office/aa905356.aspx The RibbonX we will create will live in the document and travel with it. We will have to use a tool to get the RibbonX in the document. The one I'll use for this example is called the "Office 2007 Custom UI Editor". You can download it freely from here: http://openxmldeveloper.org/articles/CustomUIeditor.aspx " Custom UI Editor is a very light weight tool for giving you a few suggestions, validating CustomUI XML and generating sample VBA callback code. If you are going to be doing add-in development or extensive Ribbon UI customization, I highly recommend using VSTO 2008 instead of this tool. You can find info here: http://msdn2.microsoft.com/en-us/library/d2tx7z6d.aspx After you install the CustomUIeditor, open the attached "MediaPlayer.xlsm" to see the CustomUI.xml: < ?xml version = " 1.0 " encoding = " utf-8 " ?> < customUI xmlns = " http://schemas.microsoft.com/office/2006/01/customui " onLoad = " Mediaplayer.xlsm!OnRibbonLoad " > < ribbon startFromScratch = " true " > < tabs > < tab id = " settingsTab " label = " Media Player " > < group idMso = " GroupThemesExcel " visible = " true " /> < group id = " LibrarySettings " label = " Library " > < button id = " GetLibrary " label = " Get Library From WMP " onAction = " Mediaplayer.xlsm!GetFromWMP " image = " bones " Read...
    Filed under: ,

  • Building the Excel Media Player – Part 2

    Last post we talked about how to use Excel as a flexible development environment and touched on the benefits of using Office graphics to build your UI. This post goes into how to use ActiveX controls in Excel and how to contain and manipulate data in tables to drive your application. Using ActiveX Controls First we need to enable that hidden Developer Tab, Click Office Button, Excel Options and Toggle 'Show Developer tab in the Ribbon' on. Now click Developer Tab, Insert, on the bottom right corner is a 'More Controls' option. Here you will find a large variety of controls from File Browsers to Calendar controls, buttons, Internet Explorer; you can even register your own custom controls for using in Excel. We require music, let's search for Windows Media Player and draw it on the grid. This provides us with full media player functionality as well as access to its object model in VBA. The full Media Player functionality is out of scope but you can learn more by looking at the reference here: http://msdn2.microsoft.com/en-us/library/bb248315(VS.85).aspx , but we are going to briefly talk about tying our custom UI to control to it. Let's use the Play button as an example: Right click on the your 'play' button and click 'Assign Macro'. Since I named my play button 'Play' the default name is perfect 'Play_Click', click new. This will open VBA which you can access at any time by pressing Alt + F11. To begin using our control let's start simply by accessing pressing play by adding the following to your Play_Click method. Sheet1.WindowsMediaPlayer1.Controls.Play WindowsMediaPlayer1.Controls will contain almost all the media control functionality we need, but let's add some volume control. On the developer tab insert a scroll bar. Right click the scrollbar and click properties. Volume usually goes from 0-100, set this to min and max. Using the selection pane, rename the scrollbar to 'Volume' and finally double click the scrollbar and add this code: Sheet1.WMP.settings.Volume = Sheet1.Volume.Value That is all it takes to control volume. Before we go any further we need music to play, we have two options. Either search directories or grab the information directly from the Media Player. To further demonstrate ActiveX we'll be doing the latter, but first we need a place to store this data, what better place than a Table. Using Tables for data manipulation Ok, so now we have our basic front end UI, and a basic understanding on how to use ActiveX in our project, let's add a few tables and some data so that this media player can do something. In the application I have 2 main tables, one called library and one called playlist . To create a table, Select your range and press ctrl + T, or insert Tab,Ttable. I added a table that contains headers for all the useful information I might want to know about media, most important to us now are ID, Title, Artist, and Source URL. To populate this table I used Windows Media Player again, you can browse the directory directly but to further elaborate on how to use ActiveX with Tables I decided to do it this way: Dim MC, i Dim LList As ListObject Set LList = Sheets("Library").ListObjects("Library") Set MC = Sheets("player").WMP.mediaCollection.getAll Sheets("settings").ListObjects("playlist").DataBodyRange.Delete LList.DataBodyRange.Delete For i = 0 To MC.Count - 1 LList.ListRows.Add().Range.Value = Array(i, _ MC.Item(i).getItemInfo("name"), _ MC.Item(i).getItemInfo("artist"), _ MC.Item(i).getItemInfo("album"), _ MC.Item(i).getItemInfo("sourceURl"), _ MC.Item(i).getItemInfo("duration"), _ MC.Item(i).getItemInfo("genre"), _ MC.Item(i).getItemInfo("filetype"), _ MC.Item(i).getItemInfo("UserPlayCount"), _ MC.Item(i).getItemInfo("AlbumPicture"), _ MC.Item(i).getItemInfo("UserRating"), _ MC.Item(i).getItemInfo("UserLastPlayedTime"), _ MC.Item(i).getItemInfo("FileSize")) Application.StatusBar = "Now Importing Item " & i & " of " & MC.Count Next i First off, manipulating data in a table is much easier than doing the same in a simple range. Tables give you the ability to add calculated columns, reference columns by their name rather than location, add a row to the end as a simple array, and treat the entire table as an object, rather than a piece of your grid. This is just a touch of what tables can do for you. You can find more information here . For the most part you can rely on ListObject.ListRows.Add() which will (by default) return the last ListRow in your table. In the above example I am setting the array by passing the value an array of strings that I get from the Windows Media Player media collection object. You can learn more about the Table/Listobject OM here . Now we have a list of songs as populated from our...
    Filed under: ,

  • Building the Excel Media Player – Part 1

    Today's author, Jon Adams, a Tester on the Excel team. A copy of the spreadsheet discussed in this post can be found as an attachment at the bottom of this post. As you are probably well aware, Excel is a very versatile tool and is often used in ways that are hard to imagine. This blog post is a follow up of Joe's post "Unusual (but cool!) Uses of Excel" . I am going to show you how to use Excel for rapid application development using the new Office Art graphics Engine, ActiveX, Table Objects, VBA (Excel OM and ActiveX), and a touch of custom Ribbon code. The complete solution description will be split up over several posts, with the following topics covered in each: Part 1 Building UI using Office Graphics Part 2 Using ActiveX Controls Table Manipulation Data Analysis Part 3 Context Menus Ribbon UI Since I am an avid music geek and spend way too much time organizing my media library, our sample project is going to be a fully functional Media Player. Before I get started I will answer a question that I am sure is burning in your mind. Why in the world would I write such an application in Excel? Flexibility : I can quickly create mash-ups of my music library data with web service data with a few more clicks and determine things like 'listening habits' and do pivots on things like artists playing in the area. Best of all, this can all be done outside of a development environment and without compiling. Office Graphics : First off, as a tester, I wanted to create something that exercised as much of the Office Graphics engine that was kind of fun. Secondly, the new office graphics engine is much more professional looking and provides the end user with a lot more flexibility right out of the box. I wanted to come up with an interesting solution that showcased the power of the new graphics engine. Now let's get started. Download the file (link at the bottom of this post), play with it and get a feel for how it works. You will have to enable macros in order for the application to work. Building UI using Office Graphics Excel 2007 completely changed what is possible with graphics on the grid, and I will show you some of the features you can use to make an elegant solution. First let's break apart this UI. Choose a background and frame your UI: The background can be as a simple as a grey box to frame some controls or in this case I decided to use a picture. If you are building a tool for your company, you can use your corporate logo as a watermark. After the size is set by your frame, you can clean things up by holding Alt and dragging resizing to line up with the grid. For a crisper UI, I like to hide the rest of the rows. Select the first column header after your picture and press ctrl + right to select the remaining columns. Repeat for rows. This gives you something like this: If you wish to hide the column/row headers you can go to Excel Options (Alt + T, O) and toggle "Show row and column headers". Let's create the side-pane now. This simply consists of 5 rectangles grouped and a series of shapes for the playback controls. To make this step easy I suggest using the new Selection Pane (Home, Find and Select, Selection Pane) . This will help you to create deep grouped objects, and assign them useful names for later in the tutorial. Now add a few shapes to the grid. On the insert tab, shapes dropdown, you have a choice of many shapes for our UI. I added 6 rectangles, an equal sign and a few chevron symbols. I also went ahead and typed a few simple titles in the rectangles and was able to get this. If you multi-select the objects (hold down CTRL while you click the objects you want to select), you can right click and group them to make it a little easier to move them together. Now in the selection pane you can name your groups and your objects for better organization. I renamed all of my objects in the tree view and grouped them for easier use later and it looks like this in the selection pane. Notice the visibility toggles. When building UI it is quite useful to be able to just hide an entire group while working on something else. Ok, now let's make this look a little better with some snazzy effects. Some effects work on the group as a whole, such as reflection and glow, whereas others, such as bevel and some scene3d effects, will apply to each object individually. Let's start by selecting an object and clicking the format tab. Let's try adding a group effect. From the Shape Effects dropdown add a glow. The Format Tab gives us a lot of options, and maybe some canned preset will work but let's dive deeper. Press Ctrl + 1 to bring up the advanced formatting dialog. In the 3-D format tab, Top Bevel, the first bevel creates a very button like illusion. For a more metallic look change lighting to flood and material to metal . Now onto the fill tab; If you add a gradient it will be applied to the entire group. I also like to use transparency to create a more...
    Filed under: ,

  • Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”

    We sometimes get mails from our customers claiming to have found a calculation error in Excel, when in fact the calculation isn't wrong, but the side effects of binary floating point precision make it seem that way. Today's author: Jessica Liu, a Program Manager on the Excel team, discusses the way Excel performs calculations, explains why sometimes you see answers you may not expect, and provides some tips on how to avoid rounding issues. Take a look at the following table: I want to be able to quickly identify the cases where the absolute difference is greater than or equal to 0.005. So I apply a conditional formatting rule on the absolute difference column to format values greater than or equal to 0.005 to be red. As a scan down the table, I notice that the value of 0.005 is not highlighted. I check over my conditional formatting rule and the formula I used to calculate the absolute difference (=ABS(A2-B2)), they seem to be correct. I then increase the precision of the absolute difference column in order to get more precise results. I discover my results have changed. Why does 1.320 - 1.3190 = 0.0049999999999999? Have you ever encountered a similar situation where your spreadsheet does not give you the result you were expecting for a seemingly simple calculation? You have checked over your calculations and still cannot figure out where it went wrong. Well the scenario you are facing may be due to floating point precision. Overview Excel was designed in accordance to the IEEE Standard for Binary Floating-Point Arithmetic ( IEEE 754 ). The standard defines how floating-point numbers are stored and calculated. The IEEE 754 standard is widely used because it allows-floating point numbers to be stored in a reasonable amount of space and calculations can occur relatively quickly. The advantage of floating over fixed point representation is that it can support a wider range of values. For example, a fixed-point representation that has 5 decimal digits with the decimal point positioned after the third digit can represent the numbers 123.34, 12.23, 2.45, etc… whereas floating-point representation with 5 digit precision can represent 1.2345, 12345, 0.00012345, etc… Similarly, floating-point representation also allows calculations over a wide range of magnitudes while maintaining precision. For example, Floating-point representation that has 4 digit precision: 1.1x10 -1 x 1.1x10 -1 = 1.44 x 10 -2 Fixed-point representation that has 4 digit precision with the decimal point positioned after first digit: 0.110 x 0.110 = 0.012 All numbers expressed in floating-point format are rational numbers. Irrational numbers such as π or , or non-terminating rational numbers must be approximated. The number of digits of precision also limits the accuracy of the numbers. Excel store 15 significant digits of precision. For example, the number 1234567890123456 cannot be exactly represented if 15 digits of precision are used. The IEEE 754 floating-point standard requires that numbers be stored in binary format. This means a conversion must occur before the numbers can be used in calculations. If the number can be represented exactly in floating-point format, then the conversion is exact. If not, then the conversion will result in a rounded value which will represent the original value. Numbers that appear exact in the decimal format may need to be approximated when converted to binary floating-point. For example, the fraction 1/10 can be represented in the decimal format as the rational number 0.1. However, 0.1 cannot be represented precisely in binary floating-point of finite precision. 0.1 becomes the repeating binary decimal 0.0001100110011…, where the sequence 1100 repeats infinitely. This number cannot be represented in a finite amount of space. So in Excel, it is rounded down by approximately 2.8E-17 when it is stored. Structure of a Floating Point Number A floating-point number is stored in binary in three parts within a 65-bit range: the sign, the exponent, and the mantissa. The Sign Bit The sign stores the sign of the number (positive or negative). 0 represents a positive number while 1 represents a negative number. The Exponent The exponent stores the power of 2 to which the number is raised or lowered. The exponent field needs to be able to represent both positive and negative exponents. To avoid having to store negative exponents, a bias value is added to the actual exponent. The bias for double-precision numbers is 1023. For example, a stored value of 1000 indicates an exponent of 1000 - 1023, or -23. The Mantissa The mantissa stores the actual number. It is composed of an implicit leading bit and the fraction bits. The storage size of the mantissa determines how close two adjacent floating point numbers can be. The mantissa and the exponent are stored in separate components. The precision of a number varies depending on the size of the mantissa. Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E...

  • Statistics and Business Data: Detecting Unexpected Values

    Today's author, Gabhan Berry, a Program Manager on the Excel team. A copy of the spreadsheet discussed in this post can be found as an attachment at the bottom of this post. Introduction When faced with a table of data, what kind of techniques do we employ to extract information from it? Maybe the first thing we do is create summaries. We might total up the columns or we might chart the data. Perhaps, we might even create a PivotTable and drill down into subtotals. All in all, summarizing data is useful. It's easy to understand and easy to do. Let's say that we're running a store and we have a small dataset of the sales we made in the first quarter. Using Excel we can easily create summary reports telling us how many products we sold to different types of people; how many to men and how many to women; how many to people over 40 and how many to people under 40. All these totals are useful. We can compare them and search for interesting differences. We might notice that we sold more products to men than we did to women or that managers bought more of our products than other professions. But can we get more information than this? Did managers buy an unusually high number of products compared to other professions? Is this number so high that it might indicate a bias of some kind? The answers to these types of questions enable us to make better business decisions. Perhaps if we know that our products are significantly more attractive to managers we might change our advertising policies to target managers more than other professions. You may think that we need data warehouses and data mining tools to answer these types of questions. But that's not necessarily true. Data warehousing and data mining are excellent tools, but so is Excel and Excel contains some sophisticated statistical functions which we can use to help us answer these types of questions. Understanding the Basics First of all, let's take a look at our data set. Our data contains customer information which we have gathered over the last quarter of trading. Each time we made a sale we recorded certain information about the customer. For instance, we recorded their Marital Status, their Gender, their Education level and their Occupation (amongst other fields). We have 1,000 records in our data set. Now let's say that we are interested in analyzing the Occupation field. It would be useful to know whether or not certain professions tend to buy more of our products than other professions. This would be a key piece of business intelligence for us. But how do we decide this? First of all, let's build some summaries. Let's count how many sales we made to each profession and each profession's percentage of the overall sales and display these numbers in a table. We'll also insert a pie chart to give some simple, visual indication of the proportions sold to each profession. With this done, let's now ask ourselves the question again: Do certain professions buy more of our products than other professions? We can see that Professional and Skilled Manual occurred more frequently in the data than the other professions and that Manual occurred the least. But is this enough information to enable us to generalize and state that Professional and Skilled Manual buy more of our products in general than the other professions? Well, not really. Why? Because before we can state anything about these totals we need to have some benchmark to compare them to. We need to have some way of calculating expected numbers for each profession. If a profession had more sales than the expected number then that takes us one step closer to being able to state that there is an unexpected difference between the amounts of products sold to each profession. To determine the expected values we have to think a little about probability. For any sale, what is the probability of the occupation being Professional? There are 5 occupations and each sale can only be assigned one occupation so the probability of it being Professional is 1 in 5 i.e., 20%. In fact the probability for each occupation is 20%, assuming there is no bias in the data. So, for any set of unbiased data, we would expect 20% to be Professional, 20% Clerical, 20% Management, 20% Manual and 20% Skilled Manual. Thus, for 1000 records, we would expect each occupation to have 200 sales. But common sense tells us that we're very unlikely to get exactly 200 sales for each profession. So what we really mean is that we expect to get approximately 200 sales for each profession. Looking at the actual data, we see that Clerical appears in 177 of the 1000 records. That's a difference of -23 from the expected value. Is this close enough to 200 to be considered approximately 200? Skilled Manual has 255 records; a difference of +55. Is this difference big enough to be significantly different ? In our data there are 255 Skilled Manual records. What is the probability of this? That is, what is the...
    Filed under:

  • Office Open XML, a.k.a. IS 29500

    As everyone who has followed this blog already knows, we introduced a new file format with Office 2007 (and Excel 2007) with the extension .xlsx called Office Open XML (OOXML). Microsoft submitted the new file format to Ecma International, and in December 2006, that file format was approved as ECMA-376 , and Ecma submitted the standard to ISO/IEC JTC 1 for fast-track processing to become an ISO standard. Over the last 15 months, many conversations have taken place between Ecma and the National Bodies that make up ISO, and comments from those National Bodies were addressed at a Ballot Resolution Meeting during the last week of February 2008. Through the month of March, the National Bodies then had the opportunity to consider the changes made to OOXML at the meeting, and today ISO officially announced the results of the voting. As stated on the ISO web site : Approval required at least 2/3 (i.e. 66.66 %) of the votes cast by national bodies participating in the joint technical committee ISO/IEC JTC 1, Information technology, to be positive; and no more than 1/4 (i.e. 25 %) of the total number of ISO/IEC national body votes cast to be negative. These criteria have now been met with 75 % of the JTC 1 participating member votes cast positive and 14 % of the total of national member body votes cast negative. The Excel team views this as a very positive development, and is committed working with both Ecma and ISO to continually evolve and develop OOXML as an international standard. More information is available for those interested: ISO press release: ISO/IEC DIS 29500 receives necessary votes for approval as an International Standard Brian Jones blog: Open XML Overwhelmingly Approved as an ISO / IEC standard (IS 29500): the end of the file formats war Read More...
    Filed under:
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.