This is Robert's Typepad Profile.
Join Typepad and start following Robert's activity
Join Now!
Already a member? Sign In
Robert
Munich, Germany
Interests: new media, optimization, strategy, forecasting, business intelligence, project management, financial planning, business plans, telecommunications, revenue management, business performance management, simulation, pricing, intelligent data analysis, business cases, revenue assurance, cable operator, operations research
Recent Activity
Sumit, you have to calculate the position of today within your timeline and use the relevant constants in the VBA code to get the position of your vertical line. Let's say your timeline goes from January to December and today is the last day of September, i.e. 75% of the entire project is over. The horizontal position of your vertical "today line" is then calculated as follows: cnstLeft + cnstStepsWidth + 75% * cnstTableWidth Finally add a vertical line at this position with the desired length. I hope this helps.
1 reply
Image
The slides and workbooks of my presentation on “Charts beyond Excel’s Chart Wizard” at SQL Saturday in London 2014 Continue reading
Posted 4 days ago at Clearly and Simply
Rob, if you get an error on the WebBrowser statement, I assume you are using Excel 2013, not Excel 2010, right? Microsoft disabled scriptable ActiveX components inside sheets since Excel 2013 and the approach of using a web browser on a worksheet is not working anymore. If you are indeed using Excel 2010, I have no clue why the code should not support the .Navigate method. The second issue you are reporting is indeed a problem. I noticed that, too. Have a look at the end of the previous post, where I am stating that this workbook is pushing Excel's computational capability to its limits. The out of memory issue may occur from time to time. I do not have a solution for this. I am sorry.
1 reply
Robert, thanks for your comment and your kind words. Unfortunately, no I didn't. I made some considerable enhancements for one of my clients (like more than one dimension in row and column headers, more filters, heat mapping the data by color, better sorting and many more). Since this was part of a paid project, I am not allowed to share it here. But I never looked into eliminating duplicate values. This wouldn't be much of a problem, I guess, just a lot of extra coding necessary. Anyway, the code isn't protected, so if you are familiar with VBA, you could give it a try on your own.
1 reply
Asit, to build your own Choropleth Map have a look at Tushar’s original article: Conditional colors of shapes and/or read the how-to blog posts here: Build your own Choropleth Maps with Excel Create Excel Choropleth Maps from Shape File If you want to display the values on the map, you have to add a textbox for each region on the map, position it manually in the center of the region on the map and link the textbox to the cell containing the corresponding value.
Toggle Commented Nov 16, 2014 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Beno, sure this is possible. I never tried, but I found this solution on VBA Express: VBA Express – Export from Excel to MS Project
Toggle Commented Nov 11, 2014 on Bring your tasks in a row at Clearly and Simply
1 reply
asa, have a look at Michael's answers to Andrew's and Kevin's comments above.
1 reply
Chris, 1. The month displayed in the header is assigned in this part of the code: With objMonthHeader With .TextFrame.TextRange .Text = intMonthNumber There is more than one way to skin the cat. For one, you could add a CASE statement here assigning the month name to .Text based on the month number. 2. The VBA code uses constants (cnstStepsWidth and cnstRowHeight) to define the width and height of the row header. It does not adjust automatically, but it wraps the text inside the textbox. You can change these constants to other values in order to make the header bigger, but you probably would also have to change other constants to make sure the Gantt Chart still fits on the slide.
1 reply
Jonathan, 1. Delete all shapes (counties and states shapes, i.e. the visible and the invisible) you do not need 2. Delete all rows on the worksheet of all counties / states you do not need 3. Run the Recreate Shape Index sub This should do the job.
1 reply
Charles, you could use LEFT and FIND to get the substring left to the searched word and embed this substring into the formula 38 – Extract Last Word.
Toggle Commented Oct 29, 2014 on String Calculations in Tableau at Clearly and Simply
1 reply
Eric, the changes work with Excel 2003, too. I will send you the XLS version by email in a minute. No donation area here. Thanks for offering, though.
1 reply
Eric, have a look here: Download Check List All per Column
1 reply
rperrett, the code in the model uses the property .Fill.ForeColor.RGB to color the shapes. If you want to define this value based on the RGB values, you can use this user defined function: Function udf_RGB (bR As Byte, bG As Byte, bB As Byte) As Long udf_RGB = RGB(bR, bG, bB) End Function You pass the R, G and B value of the color you want to the function and the function will return the color value, e.g. udf_RGB(100,100,100) will return 6,579,300 (a gray) udf_RGB(0,175,100) will return 6,598,400 (a green)
1 reply
Geethu, you also have to copy the code from the Microsoft Project Object "ThisProject". Those subs create the menus when opening the project file and delete them before closing. After copying the code to "ThisProject" you have to save and exit the file and reopen it again and you should see the menu.
1 reply
Eirik, no sweat. Have a look here: Download Gapminder Excel Replica with Trails (843.9K) You can select any of the highlighted countries to show the trails over time. Same techniques as used in the original workbook, just 3 more data series in the chart and 3 more checkboxes.
1 reply
Petr, don't open the .EMF file with Excel. Insert it as a picture (Insert | Pictures).
1 reply
Xavier, you are right, shapefiles for ZIP-codes are harder to find than administrative areas. Some sites are selling ZIP-code shapefiles, but I do not know of a site where you could download ZIP-code maps for free. Wikimedia Commons is usually a good resource, but no guarantee. I am sorry.
1 reply
Andrew, if you sort filtered data, only the visible rows will be sorted, not all the rows. The check of the sort order in the code, however, refers to the entire range, i.e. the visible and invisible rows. If you sorted by one column (descending) and double click again to reverse the sort order, the check will most likely return that the data is not sorted (since only the visible rows have been sorted). I.e. it will sort the data again descending by default and you never get to an ascending sort order. How could you solve this? There is more than one way, I guess, but you could write additional code to check if a filter is applied, remove the filter before sorting, sort the data and then set the filter again. I never tried, but I think this should work.
1 reply
Liam, adjusting the workbook to another timeline (more or less years or months instead of years) isn't too complicated for most features. Basically you have to change the ID (combining the country and the year) and change the values of myMin and myMax to fit to your new timeline. Having said that, changing the rectangle timeline above the bubble chart takes more time. You would need more or less rectangles with a certain naming convention (e.g. "Rectangle_Jan" instead of "Rectangle_1950"), change the labels of the "axis" beneath the rectangles and adjust the VBA code which extracts the year or month from the freeform name. Not really complicated, but some changes necessary.
1 reply
Erin, sure, this would be possible, but it requires some extra VBA coding and I do not have the time to do this. Having said that, it is not that much effort to add extra sections manually.
1 reply
G, I am sorry, but I do not have the time to debug your code. I did a quick search and I think the easiest way would be to create the appointment in your default calendar and move it to your "Milestone" calendar afterwards. Have a look here to get started: http://www.slipstick.com/developer/copy-new-appointments-to-another-calendar-using-vba/
1 reply
Paul, you are right. The code in my workbook uses the Windows function Sleep for the delay in milliseconds and this function isn’t available on Mac. You could either delete the declaration of Sleep and all calls of the function, but then the speed of the animation cannot be controlled anymore (e.g. forward and fast forward would be the same). The other option is to use Application.Wait instead of Sleep.
1 reply
Image
A Gapminder-lookalike animated chart in Microsoft Excel, based on the generic Motion Chart Excel Template Continue reading
Posted Sep 6, 2014 at Clearly and Simply
Peter, you do not need the faster version for a World Map with only 200 countries. Tushar's original approach is fast enough to color those very quickly. Having said that, if you still want to transfer the World Map to the workbook posted above, deleting the existing shapes and data, inserting the World Map Shapes, deleting the not required rows on the data sheet and running the Recreate Shape Index sub is pretty much all you have to do.
1 reply
G, have a look here, for instance: http://www.slipstick.com/developer/working-vba-nondefault-outlook-folders/
1 reply