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
Willem, the code already categorizes the exported tasks. With the line .Categories = myTask.Project, the appointment is assigned to a category with the name of the project file. If you change this line to e.g. .Categories = "Mandatory Appointment", it would be assigned to the category "Mandatory Appointment". If this category already exists in Outlook, the according color will be displayed in your Outlook calendar. If not, there will be no color assigned. In this case the category will be shown in the All Categories dialogue as … (not in master category list). Of course you can also add categories to the existing master list and assign a color by VBA, but I have no code snippet available doing this. Shouldn't be too complicated, though. I am sure you can find the according code snippet in the web.
1 reply
Laurent, many thanks for your comment and detailed explanation. Interesting approach, although I do not really understand what you mean by "hover technique". Are you referring to Jordan Goldmeier's roll over technique (a UDF inside a hyperlink)? I am sure your approach works well and it is an interesting alternative. I am looking forward to see your workbook. However, the main idea of this article was a feature allowing the user to zoom exactly into an interactively defined area. Furthermore, with my approach, all other parts of the dashboard stay visible, i.e. you can still see and use the form controls, the legend and - if we have a dashboard with more than one chart – the entire dashboard. Having said that: I agree, it comes with a lot of disadvantages.
1 reply
Image
Interactive zooming in and out on Charts in Microsoft Excel Continue reading
Posted Jul 16, 2015 at Clearly and Simply
Asha, have a look at the workbook #2 in the follow-up post mentioned at the end of the post above. This workbook excludes the n/a checked items from the calculation of the completion rate. If you want to change this, you have to adjust the function CompletionRate. The function has 2 nested IF clauses within the For Next loop. Delete And rngCheckList(lngRowCount, lngColumns) <> C_NA from the condition of the first (outer) IF Add Or rngCheckList(lngRowCount, lngColumns) = C_NA to the condition of the second (inner) IF. This should do the trick. No offense, but I would also recommend having a look here: http://www.netlingo.com/word/shouting.php
1 reply
Christie, sure, send me your workbook with the data and a description of your requirements by email (see the link at the top of the blog) and I will see what I can do.
Toggle Commented Jul 13, 2015 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Mahendra, the macro UpdateFilterDropDown is assigned to the combo box (filter drop down). Every time you use the drop down, the macro is executed and inside this sub the main sub (CreateTextPivotTable) is called. If you have a second filter drop down in your workbook, you have to assign the macro to this one too.
1 reply
Connie, myStartPosition and myOverwritePosition are named ranges the code is referring to. If the code stops saying "Method Range of Object Global failed", you probably did not define these names in your workbook or the names in the workbook and the references in the code are different (a typo, maybe).
1 reply
Mahendra, unfortunately no. I do not have the time at the moment to implement change requests or enhancements. I am sorry.
1 reply
Fiosco, go to the VBE, select the form frmProgress, right click, select view code and in the code comment out or delete the line "On Error Resume Next". Run the macro again. If the code stops and you get an error message, click on Debug to get to the VBA and check at which line the code stopped (yellow background). Post another comment here or send me an email and describe what happened without the On Error statement.
Toggle Commented Jul 8, 2015 on Bring your tasks in a row at Clearly and Simply
1 reply
Mahendra, first you need 2 more parameter cells on the worksheet [control]: one to define the dimension used as the second filter and one for the target cell of the second filter drop down. You also have to define a name for the target cell of the second filter. Next, you have to adjust the VBA code. I can't explain every step in a comment. Search for "filter" in the code. Wherever you find it (variable, loop, comment, etc.), you basically have to duplicate the code. E.g. you need the variable varFilterElements twice, you need ddFilterDropDown twice, etc. In the section "Define Filter" you have to do all steps for both filters and so forth. Sounds complicated, but you have to explore and adjust only 2 subs (UpdateFilterDropDown and CreateTextPivotTable) with a total of only 220 lines of code.
1 reply
Image
Interactive radial or rectangular area selection on a map of the United States in Microsoft Excel Continue reading
Posted Jul 7, 2015 at Clearly and Simply
Image
An improved version of the example workbook exporting Excel Dashboards to other applications Continue reading
Posted Jul 6, 2015 at Clearly and Simply
Api, have a look here: https://msdn.microsoft.com/EN-US/library/office/jj228306.aspx
1 reply
Image
How to export Microsoft Excel Dashboards to PowerPoint, Word, PDF, XPS or Pictures Continue reading
Posted Jul 5, 2015 at Clearly and Simply
Tony, have a look at this article: Spice up your Choropleth Maps with Excel
Toggle Commented Jul 3, 2015 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Image
How to create Cartograms in Microsoft Excel Continue reading
Posted Jun 30, 2015 at Clearly and Simply
Hitika, the checkboxes are no "real" checkboxes. The cells contain the letter "R" or "£" and are formatted with font type Wingdings2. With this font type an "R" looks like a checked box and "£" looks like an unchecked box. If you are changing the Conditional Formatting of the columns with the "checkboxes", you have to make sure the Conditional Formatting rule formats the cell with font type Wingdings2 (instead of e.g. Automatic). If you want to print only the rows with checked boxes, you would need an extra VBA sub. This sub would loop through the list, hide all rows with unchecked boxes, print the sheet and then unhide the rows again.
1 reply
Rusty, good to hear you got it working. HLOOKUP, VLOOKUP, OFFSET, INDEX. There is always more than one way to skin the cat. I am preferring INDEX to fetch values from cell ranges, because it usually is the fastest option, but this doesn’t matter in your case. "And where are you teaching classes?" Only here. I am doing onsite trainings in Germany from time to time, but I guess this won't help you. No videos or even a comprehensive online course. I am sorry. If you are looking for a good online Excel academy, I am recommending Chandoo's Excel School: Chandoo's Excel School And, for the more advanced user, Daniel Ferry's Excel Hero Academy: Excel Hero Academy Both are well worth the money. If you are interested, please have a look. Please be advised that I am not an affiliate and I do not get money for these recommendations.
1 reply
Rusty, I think you can do this without changing the VBA code: To use different threshold ranges depending on the selected measure, I would recommend to define the ranges somewhere on the worksheet control and then use INDEX formulas to get the currently selected threshold range in the first column of the defined name myMapValueToColor based on the currently selected measure (i.e. the value in cell C15). If the measures have different number formats, you have to adjust the formulas creating the legend texts (D19:D28 on the control worksheet) based on the currently selected measure (e.g. by using a CHOOSE formula). Finally, if the different ranges have a different numbers of bins, you would have to change the named range myMapValueToColor. It now refers to a fixed cell range (B19:C28). If the measures have different numbers of bins, you would replace this range reference by an OFFSET formula based on the number of bins of the currently selected measure. I hope this will be helpful.
1 reply
Nik, I forgot to mention: Regarding my first idea described above, i.e. resizing the cloud: what you could do is to create the cloud, copy it, insert it as a picture (e.g. PNG file), delete the group of textboxes and then resize the PNG image to the desired size. All this does not require too much extra code and shouldn't be a problem in terms of performance. This way, the font sizes would be adjusted by resizing, but you would lose the tooltip feature.
1 reply
Nik, my implementation does not manage the size of the outcome. The data is sorted descending by word count and the algorithm only considers the first n words (n = the user-defined maximum of words). The code then mindlessly adjusts the font sizes of the words according to their values and inserts them in the word cloud if possible, i.e. if they fit into the defined rectangle shpWordCloud. If a word does not fit into this rectangle, it will simply be skipped. Depending on the size of the rectangle, the number of words and the defined minimum and maximum font sizes, it can either be that words are skipped or that a lot of real estate is unused, i.e. the word cloud is much smaller than the defined rectangle. If I understand your requirement correctly, you want to make sure that the final word cloud has the same size as the defined rectangle shpWordCloud, right? I have thought about this during the development of the code, too, but this is way more complex than it may sound. The first idea which came to my mind was taking out the parts of the code checking whether the inserted word fits into the rectangle (and thereby make sure all words are inserted), group all words and resize the group to the size of the rectangle. This sounds easy to implement, but there is a major problem: resizing a group of textboxes does not adjust the font sizes of the texts, i.e. the size of the word cloud would then fit to the size of the rectangle, but there would either be a lot of empty space between the words (if you increased the size of the cloud) or a lot of overlaps (if you decreased the size). The challenge is an optimization problem: you would not use user-defined, fixed font sizes anymore, but rather enhance the code to let the algorithm find the optimal maximum and minimum font size in order to get to a word cloud fitting perfectly into the rectangle. That's tricky, because you only know the exact size of a word after you inserted it. I.e. the algorithm would have to run in several iterations until it finds the optimal maximum and minimum font size. This is possible, but it needs quite some extra coding and it would seriously hit the performance of the algorithm. I am sorry, but I do not see a silver bullet here.
1 reply
Javed, URL actions in Tableau are just passing links (URLs) to the Web Object (i.e. embedded browser) on the dashboard. You can use dimensions or measures from your data and / or parameter values to create a dynamic URL in a Calculated Field and pass this field to the Web Object in the URL action, like I did in the examples 2, 4, 5 and 6 above.
1 reply
Nick, unfortunately, no. As far as I know there is no fix. Microsoft does not ship the Web Components Library anymore. You could try to download and install it separately (see my reply to Sg above), but no guarantee that this will work in your environment.
1 reply
Image
The Cartogram Data Generator: a free Excel Tool to create your own polygon data for plotting Cartograms in Tableau Continue reading
Posted Jun 20, 2015 at Clearly and Simply
Vineeth, I just tried and it works fine for me. It sounds strange that it isn't possible in Desktop but works on Public. Maybe a security setting or firewall rule on your computer?
1 reply