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
David, the code cannot detect the count of constant and formula cells, the conditionally formatted cells, the cells with data validation, etc. as long as the worksheets are protected. However, it should show the count of used cells, the count of tables, Pivot tables, charts, lines of VBA code, etc. even if the sheets are protected. Are the sheets protected in your workbook? If so, unprotect them and try again. If this does not solve your problem, you can send me your workbook by email (email-link see the top of the blog) and I will have a look.
1 reply
Ahmed, all checklist versions posted in the comments and in the article with the checklist compilation are more or less slight variations of the code provided in the posted above, i.e. they are all working along the same basic idea and only a few lines of code are changed or new (compared to the original version). If you want to have the functionality of two or more of the different versions in one workbook, you have to identify the differences in the code and combine the codes in one workbook. All workbooks and codes are open, i.e. without password protection.
1 reply
Ahmed, as I wrote in my reply to Nicki, I do not have the time to implement this request. I do not know if Nicki enhanced the VBA code to accomplish this. Even if this is the case, I doubt Nicki will revisit this article and post the solution. I am sorry.
1 reply
CB, it has been removed from the data on purpose. The used algorithm only handles continuous areas ("An algorithm to construct continuous area cartograms" by J. Dougen-ik, N. Chrisman and D. Niemeyer), i.e. it cannot be used to create cartograms of maps with regions consisting of more than one polygon.
Toggle Commented Sep 7, 2019 on Cartograms in Tableau at Clearly and Simply
1 reply
cakesnake, I would create a Calculated Field which extracts the numbers from the strings, e.g.: REGEXP_EXTRACT([Value], '(\d+)') If you then sort the view by this Calculated Field, you should get what you want.
Toggle Commented Sep 2, 2019 on Dynamic Sorting with Tableau at Clearly and Simply
1 reply
TML, many thanks for your comment and your kind words. Much appreciated. Actually, I have noticed this problem, too, but did not mention it in the article. My bad, I am sorry. The alignment of the numbers in the message box is done by inserting tabs (vbTab) in the strings used for the message box. Unfortunately, this does not work properly for all screen resolutions. Truth be told, I have no idea how to fix this issue. All you can do is inserting additional or deleting existing vbTabs in the MsgBox statement at the end of the code by trial and error until the text in the Message Box looks good on your machine. As I said, I do not know how to make this work for all screen resolutions. I am sorry.
1 reply
Tanapan, Jonathan Drummey wrote a fantastic blog post including a solution for your problem: Formatting Time Durations in Tableau
1 reply
Emma, I am sorry, no. The code is open, though, so please feel free to adjust or enhance the features as you like.
1 reply
Wenqia, unfortunately, no. The algorithm implemented in the Cartogram Data Generator Tool posted above only handles continuous areas ("An algorithm to construct continuous area cartograms" by J. Dougenik, N. Chrisman and D. Niemeyer). I.e. the tool cannot be used to create cartograms of maps with regions consisting of more than one polygon. You can either keep only the largest polygon of each region and remove the others or you need to implement another algorithm.
1 reply
Gee, please see paragraph 3 in the section "Step 3: Collect the Polygons" of the article.
1 reply
I can’t reproduce your complaint. The link is valid.
1 reply
Raj, the texts "Hierarchy Level 1" and "Hierarchy Level 2" are the names of the Calculated Fields and I do not know of a way how to directly make names of Calculated Fields dynamic according to a parameter selection. There is a workaround, though, based on additional Calculated Fields, additional worksheets and floating sheets on a dashboard. I think this might be a way to get what you want. Have a look at this Tableau Knowledge Base article: Tableau KB: Creating Dynamic Column Headers
1 reply
Varsha, I am sorry, but I do not understand your question. The blog post describes a technique (working only in Office 2010 and earlier) to embed Tableau workbooks in PowerPoint. There is no "power point generated out of Tableau", so I do not understand what your question is referring to.
1 reply
Christoph, the current coordinates of the mouse (when hovering over the chart) are calculated by the VBA sub myEmbeddedChart_MouseMove in the class module clsChartEvent.
1 reply
Juan, did you also resize the ActiveX label control?
1 reply
Angela, I used the technique with Excel 2010 / Windows 7, Excel 2013 / Windows 7, Excel 2016 / Windows 10 and nowadays with Excel 365 / Windows 10 and never had the problem you described. You can send me your workbook by email and I will check if I can reproduce the error on my machine, if you want.
1 reply
Savy, you have to adjust the range of the name [myProjectInfoRange] in the Name Manager.
Toggle Commented Mar 25, 2019 on Bring your tasks in a row at Clearly and Simply
1 reply
Savy, I can’t reproduce the problem. I just checked and exported a Project task with 10,000 words in the notes (more than 60,000 characters) to Outlook and it was correctly and completely exported. Not truncated, neither at the beginning nor at the end. I have no clue why this does not work for you.
1 reply
Savy, this is possible, too, but it requires quite some additional coding. The code would first run through all selected project tasks and create a unique list of all resources, the tasks have been assigned to. Then it would run through this list of resources and all selected tasks and compile a list of tasks for each resource. Finally, it would run through all resources again and send one Outlook task containing the information of all Project tasks to this resource. Not impossible, but a lot of extra coding necessary. The code is unprotected, so please feel free to adjust it as you like.
1 reply
Savy, If you want to create a task and an appointment, select the tasks and simply click on Selection to Outlook Tasks first and then on Selection to Outlook Appointments. If you want to save the second click, you can also combine the code inside the two VBA procedures into one.
1 reply
Scott, in the example workbook posted above, an Active X Label and a XY Scatter chart are sitting on top of the map (which is a group of shapes). If you want to get rid of the map, simply open the Selection Pane (ALT-F10), click on the group "Land and Sea", press Delete and the map is gone.
1 reply
K.O., many thanks for your comment. You are absolutely right: if you only change a small fraction of the values, writing them directly to the cells tends to be faster than populating the entire array to the range. If you take this to the extreme, changing only one cell is certainly faster than writing an array with a couple of thousand elements, even if you would use the Select statement (which you never would do, would you?). But where is the threshold / break-even? I haven’t done a detailed analysis, but here is an educated guess: method 6 (writing the array in one go) is usually around 90% faster than method 3 (cell by cell with a range variable). My wild guess would be that the threshold is probably somewhere around 10%. If you are changing less than 10% of the values, you will be faster writing them directly to the cells. If your code changes more than 10% of the cells, writing the array in one go will probably be the better choice. As I said, just a wild guess, no detailed analysis done. And it does not consider the additional time the code needs to decide whether to write directly or to populate the entire array. Finally: I cannot reproduce the numbers you mentioned in your comment. Writing an array with 2,000 rows and 30 columns to a range takes 0.15 seconds on my machine. Even 20,000 rows and 30 columns do not take more than 1.6 seconds. I can’t reproduce the 6 seconds you mentioned but this may have to do with your setup or implementation. Hope this helps a little bit.
1 reply
Colijn, simply add the following 2 lines to the code (before the .Save statement): .ReminderSet = True .ReminderTime = .DueDate & " " & CDate(#8:00:00 AM#) These two lines set the reminder to 8am on the due date of the task.
1 reply
Utku, this is possible. There are different options: Option 1: Format the fill color during the creation of the shapes The following line of code (line 77) currently assigns a dark grey fill color of the lines to all freeform shapes: .Line.ForeColor.RGB = RGB(100, 100, 100) If you want to have individual line colors for each shape, you need to define the color coding for the shapes (countries) somewhere on a worksheet (in tab [Data] for instance) and then change the code to use the defined line color per shape. Option 2: Format the fill color of lines after the creation of the shapes For this option, you would add a loop at the end of the code (after all shapes have been created), loop through all shapes and format the lines as specified somewhere on a worksheet.
1 reply
Greg, actually, there wouldn't be much to change in the implementation if you would want to compare e.g. a selected region of a state to the entire state. The main question would be the user interface: how would you enable the user to easily select the region he/she wants to compare to the entire state? There are options to enable the user to select regions using the mouse. Have a look here: Selecting and Highlighting Areas on Excel Charts However, this is designed for the selection of an area on a real Excel chart. Easily selecting areas which are nothing else than freeform shapes on an Excel worksheet are a completely different story. I am sorry, but I do not think there is an easy way to implement your idea.
1 reply