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

The Excel Team Blog

Sort By: Most Recent | Most Viewed | Most Commented

  • Building an Asset Tracking Application in Excel Services – Part 3b of 5

    Today's author, Dan Parish, continues his discussion on building an Excel Services solution. In Part 3a I walked through how to create the SharePoint List that stores the data users enter. In this post, I will walk through how to create the Excel Services User Defined Function (UDF) that will pull this information from the SharePoint List into Excel Services. Overview Out of the box, Excel Services does not support pulling in data stored in SharePoint Lists. However, you can add this functionality...
    Filed under: ,

  • Community Clips

    The folks over in Office Labs are testing out a new idea called Community Clips and I, personally, think it's a fantastic idea. It's a community site where anyone, not just Microsoft employees, can upload "how to" videos that take you step-by-step through accomplishing a particular task. Think of it as YouTube meets Office Online . If, like me, you're a visual learner, this is a great way to learn new tips and tricks on how to do any number of things with Microsoft products...
    Filed under:

  • Building an Asset Tracking Application in Excel Services – Part 3a of 5

    Today's author, Dan Parish, continues his discussion on building an Excel Services solution. Part 3 turned out to be a bit longer than we had anticipated so we've split it up into two parts: 3a and 3b. Thus far in this series, I have given an overview of the solution , and detailed how to build the Excel workbook that is the core of how it works. In today's post, I will walk through building the SharePoint List that users will input their machine information into. Overview In order to...
    Filed under: ,

  • Building an Asset Tracking Application in Excel Services – Part 2 of 5

    Today's author, Dan Parish, a Program Manager on the Excel Services team, continues on with his multi-part post about building an application using Excel Services. In my last post I gave an overview of the asset tracking application that I am going to spend the next four posts describing how to build. The core of the application is the workbook it is based on. The workbook is what really makes this application work, since it is what pulls in the data users entered, figures out who has and hasn't...
    Filed under: ,

  • VBA Focus Group at TechEd

    Do you use VBA (or any other extensibility mechanism) in Office and are you headed to TechEd? If so the we would like to talk with you in an "invitation only" meeting. This is your chance to share your feedback and influence the work that Microsoft is doing in the programmability space. You must be available for either: TechEd Dev: Held at 4:30 on Thursday, June 5th TechEd ITPro: Held at 3:00pm on Monday, June 9th If interested, please respond to the following questions to Amanda.Silver...

  • Building an Asset Tracking Application in Excel Services – Part 1 of 5

    Today's author, Dan Parish, a Program Manager on the Excel Services team, will discuss over the next few posts how he built an asset tracking application using Excel Services. At the SharePoint Conference in Seattle in March, I did a presentation entitled "Leveraging Excel Services to Build Rich SharePoint Applications". The talk included a demonstration of how to build a SharePoint application that used Excel Services to solve a data collection scenario in which group administrators...
    Filed under: ,

  • Ten Tips for Using Excel Services

    A new MSDN article about Excel Services was just released. If you do any kind of work with Excel Services, whether you are just getting started with it or trying to do something a bit more advanced, this article is a pretty good read because it covers some of the more common pitfalls that we hear from customers. The following topics are discussed: Making an Excel Workbook Compatible with Excel Services Configuring External Data Connections Configuring Excel Services to Work with Kerberos Saving an...

  • New Technical Article on Excel Services

    There's a new article up on MSDN that describes a custom solution built by the IT department here at Microsoft that uses Excel Services. Rather than re-paraphrase, I'll "borrow" liberally from Shahar's description : The solution is probably one of the most advanced implementation of Excel Services I have seen to date and includes lots of ingenious customizations - the white paper, specifically, talks about how to use Excel Services as a landing pad for data and then get the...

  • PivotTables: Calculated Items

    Today's author: David Gainer, a Program Manager on the Excel team. PivotTables are designed to help users make sense of large amounts of data by providing an easy way to build a summarized report. In addition, PivotTables can be rearranged easily, so that once you have some summary data in a PivotTable, you can look at the same information in many different ways with only a few mouse clicks. For a slightly less brief overview of PivotTables as well as a list of the improvements made to the application...
    Filed under: ,

  • 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...
1 2 3 4 Next >
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.