Loading...

Follow Peltier Tech Blog on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


Question: Extract a Category Label for a Point

I came across a question in the Excel Reddit, Is there a way to select a chart series point and have the label name of that point be copied into a cell?

Answer 1: Chart with Simple Axis

The short answer is the following function: you get .XValues property of the series, which is an array of category labels, and find the element of the array for the particular point:

Function GetCategoryLabel(cht As Chart, iSrsNum As Long, iPtNum As Long) As String
  Dim srs As Series, vCats As Variant
  
  Set srs = cht.SeriesCollection(iSrsNum)
  vCats = srs.XValues
  GetCategoryLabel = vCats(iPtNum)
End Function

You call it like this, passing in the chart, series number, and point number:

Sub TEST_GetCategoryLabel()
  Dim s As String
  Dim cht As Chart, iSrs As Long, iPt As Long
  
  Set cht = ActiveSheet.ChartObjects(1).Chart
  iSrs = 1
  iPt = 3
  
  s = GetCategoryLabel(cht, iSrs, iPt)
  s = "Category Label for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
  MsgBox s
End Sub

This doesn’t stick the label into a cell, like the question asked, but displays the label with a message box. As shown below, Series 1 – Point 3 has the label “C”:

Answer 2: Chart with Multi-Tier Category Labels

It turns out that the chart was a pivot chart, based on a pivot table with several fields in the rows area. Each field contributes a tier of labels to the category axis. The screenshot below shows a Table of data, a Pivot Table based on this Table, and above both a Pivot Chart, with a two-tiered category axis.

If you use the simple approach above, the .XValues don’t distinguish among the tiers, and the labels from different tiers are put together, separated by spaces. In the chart below, Series 1 – Point 3 has the compound label “Alpha C”, according to the simple algorithm.

We need a much more detailed VBA approach. We have to parse the series formula, get the address of the range containing the category labels, then get the corresponding range, figure out how many columns of labels there are, then find the label in each column corresponding to the point. This is complicated by the empty cells in the pivot fields, so we have to keep looking upward until we find a cell with a label.

Here is the more intricate function that extracts an array of category labels:

Function GetCategoryLabels(cht As Chart, iSrsNum As Long, iPtNum As Long) As Variant
  Dim srs As Series
  Dim sFmla As String, vFmla As Variant
  Dim rCats As Range, vCats As Variant, vOutput As Variant
  Dim iRow As Long, iCol As Long, nRows As Long, nCols As Long
  
  Set srs = cht.SeriesCollection(iSrsNum)
  
  sFmla = srs.Formula
  sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
  vFmla = Split(sFmla, ",")
  
  Set rCats = Range(vFmla(LBound(vFmla) + 1))
  vCats = rCats.Value2
  
  ReDim vOutput(1 To UBound(vCats, 2))
  For iCol = 1 To UBound(vCats, 2)
    For iRow = iPtNum To 1 Step -1
      If Len(vCats(iRow, iCol)) > 0 Then
        vOutput(iCol) = vCats(iRow, iCol)
        Exit For
      End If
    Next
  Next
  
  GetCategoryLabels = vOutput
End Function

It’s called in a similar way:

Sub TEST_GetCategoryLabels()
  Dim v As Variant, i As Long, s As String
  Dim cht As Chart, iSrs As Long, iPt As Long
  
  Set cht = ActiveSheet.ChartObjects(1).Chart
  iSrs = 1
  iPt = 3
  
  v = GetCategoryLabels(cht, iSrs, iPt)
  
  s = v(LBound(v))
  For i = LBound(v) + 1 To UBound(v)
    s = s & ", " & v(i)
  Next
  
  s = "Category Labels for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
  
  MsgBox s
End Sub

When we put all the category fields into the rows area of the pivot table, we get three labels for Series 1 – Point 3, “Alpha”, “A”, and “III”:

When we put the fields “Greek” and “Latin” into the rows area of the pivot table and move “Roman” to the columns area, we get two labels for Series 1 – Point 3, “Alpha” and “C”:

When we leave the field “Greek” in the rows area of the pivot table and put “Latin” and “Roman” to the columns area, we get a single label for Series 1 – Point 3, “Gamma”:

The post Extract Labels from Category Axis in an Excel Chart (VBA) appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Ctrl+Select In Excel

When selecting cells and areas in an Excel worksheet, you can select one cell or area with the mouse, then hold the Ctrl key while selecting another cell or area, and the new selection is added to the previous selection. You can select multiple areas at once using this Ctrl+Select sequence.

This is great, until you select one or more cells you didn’t mean to select, or if you just want to deselect something you’ve selected. In Excel worksheets, you can’t use the Ctrl key to unselect something that you’ve selected previously. This is a pain, because you have to start again with your multiple region selection, and hope you don’t mess it up again.

This has been the behavior since Excel 1950 for Univac was introduced.

Ctrl+Select Everywhere Else

In every other application I can think of, when you are selecting objects, you can use Ctrl+Select to add to the collection of selected objects. And if you Ctrl+click on a selected object, it is deselected. This works on objects in PowerPoint, shapes embedded in Word, files and folders displayed in Windows Explorer.

People have grumbled about the inability to deselect a cell in Excel for a long time, and it even has received an entry in Excel UserVoice: Unselecting cells when using Ctrl to select multiple cells.

The New Ctrl+Select Behavior in Excel

Well, the Excel Team was listening, and not long ago they announced that it is possible to Deselect a selection in Excel. According to that announcement, it’s still only available to Insiders, who risk all to get the latest builds with the latest and greatest new features and the occasional “Gotcha!” Read about Office Insiders and What is Office Insider? on the Microsoft Office web site.

I knew that this suggestion had been made in UserVoice, and I might even have heard that Microsoft was working on it. So one day, when I was tooling away as usual, I incorrectly selected some cells, and thought to myself, “When will they ever let me Ctrl+Select to unselect that stupid cell over there?” And I Ctrl+Selected that stupid cell over there, and it was UNSELECTED! Naturally I didn’t really believe it, so I Ctrl+Selected and Ctrl+Deselected that cell about twelve times.

It’s such a seemingly small change, but it removes a frequent and annoying source of frustration.

How Ctrl+Select and Ctrl+Deselect Work

Suppose I have a range of data like that below left, and for some reason I want to select the header row and the rows with even X values. I start by selecting the header row and the row with X = 0.

Now I hold Ctrl and select the row with X = 2 (below left). That’s how Ctrl+Select works.

Then I hold Ctrl and select the row with X = 4, except I’m clumsy and also select the row with X = 5 (below right).

Ordinarily I would just curse at my poor mouse-eye coordination, and go back to the first selection above. But now I can curse, then hold Ctrl and select the row with X = 5. Or rather, DESELECT the row with X = 5 (below left); the resulting selection is shown below right. That’s how Ctrl+Deselect works.

Now I can Ctrl+Select the row with X = 6 (below left), and do what I want with the selected range, which apparently was shading the cells light gold (below right).

Want More Examples?

So I was playing around to suss out the behavior more fully, and I present a few more examples. They all start with a simple rectangular range selected, C2:G6.

Below left, I’m holding Ctrl while selecting part of the previously selected range, C4:E6. The resulting selection is comprised of the two rectangular areas C2:G3 and F4:G6.

So I learned if the cell you click at the beginning of a Ctrl+Select operation is selected, you will deselect any cells in the range you are currently selecting. I selected B3:F7, starting within the previously selected range at cell F3 (shown below left), so all cells in B3:F7 end up not selected (below right).

If the cell you click at the beginning of a Ctrl+Select operation is not selected, you will select all cells in the range you are currently selecting, and no cells become deselected. I selected B3:F7, starting outside the previously selected range at cell B3 (shown below left), so all cells in B3:F7 end up selected, overlapping the original selection of C2:G6 (below right).

If I then Ctrl+Select D4:E5, which is completely enclosed by the previous selection, that small square region is excised from the selection.

The new selection is easier to see if I then shade the selected cells below.

Finally, it’s impossible to unselect all cells on the active sheet. Well, you can, if you select an object on the sheet, such as a shape, a picture, or a chart. But an active worksheet has an active cell.

Below left, C2:G6 was selected, and I am Ctrl+Selecting the same range, from the bottom left corner to the top right. The new active cell is C6, the first cell of my last Ctrl+Select.

Pretty cool, isn’t it?

A Plug for Excel UserVoice

This is the third UserVoice success story I’ve written about, joining Plot Blank Cells and #N/A in Excel Charts and User Voice Fixes Pivot Table Default Settings. There have been several other UserVoice suggestions implemented by the Excel team, as well as numerous being planned and under review.

UserVoice is a great way to make suggestions about issues or new features in Excel. Don’t just start by making a suggestion, though. Search for your idea, and if it’s already there, vote for it and add a comment. Ideas are grouped by platform (Windows, Mac, Online, etc.) and by topic (Charting, Formatting, Pivot Tables, etc.). Among other criteria, when evaluating user feedback, the team looks for ideas with lots of votes.

The post Ctrl+Select in Excel Gets Smart appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Plot Blank Cells in Excel Charts

A common problem around web forums and blogs is how to plot blank cells in Excel charts. There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart.

I discuss the “plot blank cells” issue in several tutorials in this blog: Mind the Gap – Charting Empty Cells, Area Chart With Gap, Another approach to plotting gaps in Excel charts, and VBA Approaches to Plotting Gaps in Excel Charts, among others.

tl;dr

A new feature in Excel 2016, Show #N/A As An Empty Cell, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells.

Excel’s Hidden and Empty Cell Settings Dialog

You can easily tell Excel how to plot empty cells in a chart. Right click on the chart and choose Select Data, or choose Select Data from the ribbon. Click on Hidden and Empty Cells in the bottom left of the Select Data Source dialog that appears. The Hidden and Empty Cell Settings dialog appears. There are three options for Show Empty Cells As: Gaps, Zero, and Connect Data Points with Line.

There is also an option to Show Data in Hidden Rows and Columns. By default Excel will not chart data in rows or columns which have been hidden, but checking this box causes hidden cells to be plotted.

I’ll explain what the Empty Cells settings mean below, including some of their idiosyncrasies.

What’s a “Blank Cell”, Anyway?

Part of the confusion over this whole issue is what Excel considers a blank cell.

People will type a space character in a cell and wonder why that’s not blank, it looks blank. The answer is, because it is simply a transparent piece of text.

People will enter a formula like =IF(A1>0,A1,"") and wonder why it’s blank, because it makes a cell look blank too. The answer is, because the cell contains a formula. But it returns “”, why isn’t that blank? Because “” is a piece of text, albeit zero characters long.

Sometimes a null string (i.e., “” without the quotes) gets imported into a cell, and people don’t see anything, so they wonder about that too. As above, it’s a piece of text.

But this is all confusing, because different Excel functions treat “” differently. For example, if cell A1 contains “”, ISBLANK(A1) is FALSE, but COUNTBLANK(A1) is 1. So is it a blank, or isn’t it? Well, a chart plots it as zero, so for my money, it’s not a blank at all.

The fact is, there is no magic function in Excel, no BLANK() or NULL(), which would cause a cell to behave consistently as a blank cell.

Use #N/A Instead of Blanks

A partial solution to the problem is to write a formula that displays #N/A instead of “” to prevent drawing a point. For example, =IF(A1>0,A1,NA()) instead of =IF(A1>0,A1,""), where the function NA() displays #N/A in a cell. However, this is of limited use, since it doesn’t really mimic the behavior of a blank cell, and it works differently for different chart types. People also avoid this approach because they think the #N/A value in a cell is ugly, even though it is informative. Sometimes the advice for this is to use conditional formatting to hide the error; I tend to use a light gray font to de-emphasize it without hiding it altogether.

Care must be taken with the #N/A approach. =IF(A1>0,A1,NA()) and =IF(A1>0,A1,#N/A) both return the error value #N/A, which as an error is centered in a cell with General Horizontal Alignment (see my post Check Your Data: Is It Numeric or Text to learn why General Horizontal Alignment is important). However, =IF(A1>0,A1,"#N/A") returns a text label “#N/A”, which as text is left aligned in a cell, and will be plotted like any text, as a zero value.

This is the behavior which has changed in very recent versions of Excel 2016. I also believe that when these changes were implemented, some inconsistencies crept into this behavior.

Other Workarounds

There are numerous other ways to get around the inability to plot a formula as a blank cell.

In Another approach to plotting gaps in Excel charts (on this blog), Roberto Mensa, Kris Szabó, and Gábor Madác of the FrankensTeam showed an elegant and sometimes fragile technique that used defined range names that would substitute a blank cell for any cell containing #N/A.

In the comments to another post on my blog, which I cannot now locate, another reader suggested creating a range that linked multiple times to each value in the original range, so each point appears three times. The middle time it is actually a blank cell, which the first and third time it links to the original. If the original is #N/A, we are left with a sequence of #N/A-Blank-#N/A, and if the #N/A is next to a blank cell, it does not get an interpolated line segment. Clever but cumbersome.

Among the VBA approaches I’ve seen (including those I wrote about in VBA Approaches to Plotting Gaps in Excel Charts) are routines that step through the data and clear any cells with #N/A or text, so they plot as blanks, or step through the chart and format any points with undesired markers or line segments to show neither. Of course, if the data changes, then other routines must be run to restore any formulas which were deleted and reformat any points. Cumbersome, and most examples you’ll find were hacked together by non-programmers who just needed something, anything, to get it done.

New in Excel 2016: Plot #N/A as Blank in Excel Charts

In a recent build of Excel 2016, the behavior of #N/A in a chart’s values has changed. It is now possible to make Excel plot #N/A values as empty cells. The new feature was announced on the Microsoft Office blog in Display empty cells, null (#N/A) values, and hidden worksheet data in a chart. I’d like to think it was partly in response to my suggestion on Excel UserVoice, Give us a proper NULL() worksheet function. I was asking for a general function that would make any Excel formulas or charts treat a given cell as a blank cell, but my main interest was in the ability to chart the result of a function as a blank cell.

When the feature was first introduced to a subset of Insiders, there was no option; #N/A was always treated like a blank cell in a chart. The Hidden and Empty Cell Settings dialog was unchanged from previous builds (shown earlier).

Once Microsoft was confident that the feature had no show-stopping bugs, they rolled it out more broadly. Insiders were also treated to a new Hidden and Empty Cell Settings dialog, which features a checkbox to Show #N/A as an empty cell.

Notice that there is a space in the old dialog where the new checkbox appears. This space was cleared in Excel 2013, but only utilized recently.

This new feature has been rolled out not only to Office 365 subscribers, but also to regular Excel 2016 users. On my Office 365 laptop I have the full-blown Show #N/A As An Empty Cell behavior. On my non-Office-365 laptop, I get the new behavior, but I still do not have the new dialog, so I can’t toggle it on and off.

Now let’s see how this plays out in Excel charts.

Plot Blank Cells in Common Excel Chart Types

Here is how a blank cell is plotted in a line chart, for all three options. Left, for Show empty cells as: Gap, there is a gap in the blank cell’s position. Center, for Show empty cells as: Zero, there is an actual data point (a marker and a data label) with a value of zero in the blank cell’s position. Right, for Connect data points with line, there is a line connecting the points on either side of the blank cell. Perfect.

Here is how Excel plots a blank cell in a column chart. Left, for Show empty cells as: Gap, there is a gap in the blank cell’s position. Center, for Show empty cells as: Zero, there is an actual data point (or at least a data label) with a value of zero in the blank cell’s position. Right, since it makes no sense to connect points with a line if the points are columns or bars, the Connect data points with line option (also known as the Interpolate option) is disabled; I’ve denoted this by using gray italic text in the chart title. You can still have that option in a column chart, though; for example if you start with a line chart with that option selected and then change the chart type, or if you apply the option with VBA. Since the interpolate option is disabled, Excel defaults to leaving a gap.

I’ve modified the column charts by extending the Y axis minimum to -2, and setting the Horizontal Axis Crosses property to -2. The tick label at Y=0 is colored red to draw attention to the altered axis settings.

The bars start at the axis (at Y=-2, not at Y=0), and extend upward to the Y value. We can now also clearly see the column with a value of zero, exactly what we signed up for by selecting Show Empty Cells as Zero.

Here’s how blank cells are plotted in an area chart. Left, for Show empty cells as: Gap, there is a gap in the blank cell’s position. Center, for Show empty cells as: Zero, there is an actual data point (or at least a data label) with a value of zero in the blank cell’s position. Right, Excel decides it makes no sense to connect points with a line (though it might have made sense to fill in the area as if there were a line), the Connect data points with line option (the Interpolate option) is disabled. You can still have that option in an area chart, the same as you can in a column chart. The interpolate option is disabled, so Excel defaults to plotting a zero-value point, not leaving a gap as in the column chart.

As I did with the column charts above, I’ve modified the area charts by extending the Y axis minimum to -2, and setting the Horizontal Axis Crosses property to -2 (note the red label at Y=0). You can clearly see that there is a data point at zero corresponding to the blank cell in the Y values.

Plot Null String in Excel Charts

I’ll illustrate the problem in one set of line charts. In the data ranges below, the cell for the value corresponding to category “c” is a null string, created using the formula =””, as shown below the first chart. Regardless of the Show Empty Cells setting, the null string (and any text) is plotted as a zero value data point, with a marker and data label.

This is what people are trying to plot as a gap.

Plot #N/A (as #N/A) in Common Excel Chart Types

Since time immemorial, in a line chart, no marker is drawn for a #N/A value, but a line is drawn connecting the markers on either side. So if all you wanted was to suppress drawing of a marker, this is great. If the series has lines connecting points and you want the line to connect adjoining points, this is great. If you don’t want the line but want a gap, this doesn’t do it. All three charts are the same: #N/A isn’t a blank cell, so it shouldn’t care what Show Empty Cells As setting the user has selected.

The Don’t Show NA As Empty Cell in the chart titles is to distinguish legacy behavior from the new Show NA As Empty Cell behavior I will discuss below.

In a column chart, no data point is plotted (which is great). However, if you have data labels on the series, you’ll get a label floating above zero. Here the label reads #N/A because the Show Values option is selected for the data labels. As with line charts, all three charts are the same.

In an area chart, there are inconsistent behaviors. The three charts should be the same, but the Show Empty Cells As Gaps also shows #N/A as a gap, even though it shouldn’t recognize #N/A as a blank. In the other cases, unlike the column and line charts, it plots #N/A as zero; unlike the column chart, it does not display a data label.

I suspect that some of the behaviors for plotting #N/A as #N/A have been adjusted from earlier versions by the new #N/A as Blank feature, but I have not bothered to track down my suspicions.

Plot #N/A as Blank Cells in Common Excel Charts

This feature works pretty much as advertised. When enabled, the #N/A value is treated just like an empty cell. I will not show all combinations as I have above, but just a few highlights.

In the line charts below, we see #N/A values treated As Gaps, As Zeros, and Interpolated.

In the column charts below, we see #N/A values treated As Gaps and As Zeros, with Interpolated mimicking the As Gaps behavior.

In the area charts below, we see #N/A values treated As Gaps and As Zeros, with Interpolated mimicking the As Zeros behavior.

Just like in the examples shown for blank cells.

Excruciating Detail: Plotting Blanks and #N/A Values in Stacked Excel Charts

This section was moved to the end, because it seemed to present too much information at once, and confuse more than clarify. But now that you’ve seen the comparisons above, it might be safe to continue.

Plot Empty Cells in Stacked Excel Charts

These settings work differently in stacked charts. Excel’s rationale is that you can’t plot a gap, because the point stacked on top of the gap might have a value, and it needs something for it to be stacked on. If you are trying to plot an empty cell as a gap, and it isn’t working, make sure you aren’t using a stacked chart type. I should note that Excel does not apply this rationale consistently.

One of the most confusing chart types is a stacked line. People choose it when selecting a chart type, not realizing that each series’ values are plotted on top of previous values. (It’s much easier to notice stacked series and comprehend the data in a stacked column or area chart).

This is how Excel plots a blank cell in a stacked line chart (it has only one series, but it’s still a stacked line chart). The only option you can select is Show as Zeros (though you can apply the other settings through the roundabout means described earlier), and what you always get is a point at zero.

In a stacked column chart, you have both the As Gap and As Zero options available. As Zero results in a zero-height data point (i.e., bar), which we can see because of the data label; As Gaps seems to lack this data point, in conflict with Excel’s apparent decision that it needs a zero point to stack subsequent point on. The disabled Interpolate setting reverts to As Gaps. Note that in the stacked column chart, the bars start at zero, not at the axis.

When you add Series Lines to the stacked column chart (please don’t use these confusing bits of clutter), the series lines connect the bars on either side of the gap (left and right), and connect with the zero-height bar in when plotted As Zero (center).

If we stack another series of bars atop our series with the blank value, we see two things. The second bar has no problem stacking on the place a missing bar would be, and the series lines show an interesting crossing pattern.

Like the line chart, the stacked area chart only has the As Zeros option available, and you can see the zero-value point where the blank cell goes.

Plot #N/A in Stacked Excel Charts

In a stacked line chart, #N/A is plotted as zero, since you need a point on which to stack other series. The #N/A zero points have markers but no data labels.

In a stacked column chart, the behavior is similar to plotting of blank cells. Even though the #N/A is supposedly not treated as a blank, there are different behaviors based on the Show Empty Cells setting: As Gaps results in no apparent point, because there is no data label, while the As Zeros setting apparently plots a bar 0 units high, evidenced by the #N/A label. Unlike in the unstacked column charts above, bars start not at the X axis but at zero, so we can’t actually see the zero-height bar. Connect Points With Line is undefined as before, but reverts to the As..

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Is Your Data Numeric or Text?

It’s important that you check your data before charting it. One common problem with data is that it may look like numbers but get treated as text.

I’ll illustrate with a simple example provided by a customer. He emailed me to say he was having trouble with my software, and none of his charts looked right.

This is a snapshot of the worksheet he provided (the data has been changed, but not enough to affect this discussion). There are two features of this data: first, everything is left-aligned. Second, many of the cells with numbers also have small green triangles in their top left corners: these are error flags. I’ll discuss each of these in turn.

One of your first steps to validate your data, even if you don’t see such clues as left-alignment or error flags, is to make a quick chart with it.  In the chart below, we see that only the first category shows visible bars. This corresponds to the first numeric row which does not have error flags. Since Excel plots text values as zero regardless of any apparent numbers in that text, I suspected that the error flags had to do with numbers stored as text.

In addition to numbers stored as text, a quick diagnostic chart made this way can uncover blank cells, rows, and columns. It can help highlight keying errors; for example, deep in a table, you might not notice a misplaced decimal point in 12345.6789, which is supposed to be 1234.56789, but the chart will show one point wildly out of scale with its neighbors.

Fix Horizontal Alignment

The first thing I usually do is clean up any gratuitous formatting. This usually means inconsistent fonts and font sizes, bold, italic, colors, borders, and the like. You can format your output tables, the ones used in reports and on web sites, however you like. Or however your boss likes. But your working data should be as clean as possible.

One important formatting element is horizontal alignment. Forcing left alignment, or even more commonly, center  alignment, can mask characteristics in the data. A better choice is general alignment, because this will align all text to the left, all numbers to the right, and errors in the center. This general alignment by data type is a great visual indicator of possible issues in the cells.

The selected text below is horizontally left-aligned, forced by the left-aligned icon highlighted in the ribbon.

To change to general alignment, simply click once on the highlighted alignment icon. This changes to general alignment, and unhighlights all of these icons.

You can also format the cells, and on the Alignment tab of the dialog, under Horizontal, choose General.

Below you can see the effect of applying general horizontal alignment. The cells which plotted correctly are now right-aligned the way we expect numbers to look. The cells with error flags are still left-aligned, indicating that the numbers are somehow stored as text.

Fix the Errors

Sometimes a person will disable Error Checking because of all those unsightly green flags littering their worksheet. And maybe the flags are ugly, but nothing is as ugly as an undetected error that causes other problems in your model. Hiding error flags to make your spreadsheet pretty is like trying to sweep all those error flags under the industrial carpet of your cubicle. What’s more attractive is a worksheet that has Error Checking enabled and also has no error flags.

First, you should ensure that Error Checking is enabled. Click the Excel File tab, choose Options, then click Formulas. Make sure the “Enable background error checking box” is checked.

When Error Checking is enabled, not only do you get a green flag in the top corner of the cell, but if you select the cell, Excel pops up a little traffic symbol (a yellow diamond icon with an exclamation point) telling you to examine the active cell.

When you mouse over this error icon, it pops up a description of the error. And as I suspected from the start, “The number in this cell is formatted as text”.

When you click on the icon, a menu drops down which provide some options for you. The best option in this particular list is “Convert to Number.”

That Was Easy!

When you click Convert to Number, the cells all lose their green flags and become right-aligned. The chart looks nice, too, without all those text values being plotted as zero.

The post Check Your Data: Is It Numeric or Text appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Someone in the Excel Reddit was asking about making a combination chart by adding a scatter chart series to a bubble chart. He wanted to draw a customized trendline, and you can’t connect points with lines in a bubble chart. You can add a trendline to a bubble chart, but you are limited to the standard options.

But you can create a “scatter bubble chart” if you adjust the size of the markers in a scatter chart. It can be a bit tedious to do this manually: you need to figure out how large to make each marker (and as Barbie once famously said, “Math is hard”) and then you have to format each one.

Fortunately, it takes a fairly simple VBA procedure to turn a scatter chart into a bubble chart. The code at the end of this post does the following tasks:

  1. Extract the series formula of the first series of the active chart (if no chart is selected, the program aborts).
  2. Determine the range containing the Y values.
  3. Find the range containing the bubble values. If Y values are in a column, the program uses the next column; if Y values are in a row, it uses the next row.
  4. Ask the user how large the largest bubble should be (the default is 40).
  5. Loop through the points in the series, calculating the size of the marker. Markers are scaled based on the square root of the bubble values, so their areas are proportional.

Here is an Excel bubble chart made with a simple three-column data set. the first column contains the X values, the second contains the Y values, and the third contains the Z values that set the bubble size.

Here is an Excel scatter chart made using the first two columns of the range.

Press Alt+F8 to open the Macro dialog, select the macro ScatterBubble, and click Run.

The macro asks how large the largest bubble should be.

Finally, here is the scatter chart with resized markers.

Nearly indistinguishable from the bubble chart shown above. I used a largest bubble size of 37 to make it match so closely.

Here is the VBA procedure ScatterBubble which created the Scatter Bubble Chart above.

Sub ScatterBubble()
  ' convert a scatter chart into a scatter bubble chart
  Dim sFmla As String, sYvals As String
  Dim vFmla As Variant
  Dim rYvals As Range
  Dim rBubbles As Range
  Dim iPt As Long
  Dim Zmax As Double, Zmin As Double, Bmax As Variant
  
  Const BubbleMaxDefault As Double = 40
      
  If ActiveChart Is Nothing Then
    Exit Sub
  End If
      
  ' extract Y value range from series formula
  sFmla = ActiveChart.SeriesCollection(1).Formula
  vFmla = Split(sFmla, ",")
  sYvals = vFmla(LBound(vFmla) + 2)
  Set rYvals = Range(sYvals)
  
  ' determine bubble size data range
  If rYvals.Rows.Count > 1 Then
    ' multiple rows, must be one column -> use next column
    Set rBubbles = rYvals.Offset(, 1)
  ElseIf rYvals.Columns.Count > 1 Then
    ' multiple columns, must be one row -> use next row
    Set rBubbles = rYvals.Offset(1)
  Else
    ' one cell only -> indeterminate
    MsgBox "Cannot determine the bubble size data range.", _
      vbOKOnly + vbCritical
    GoTo ExitSub
  End If
  
  ' validate
  If WorksheetFunction.Count(rBubbles) <> _
    WorksheetFunction.Count(rYvals) Then
    ' inconsistent data
    MsgBox "The bubble size data is not consistent with the Y values data.", _
      vbOKOnly + vbCritical
    GoTo ExitSub
  End If
        
  Zmax = WorksheetFunction.Max(rBubbles)
  Zmin = WorksheetFunction.Min(rBubbles)
  If Zmin <= 0 Then
    ' can't have negative diameter bubbles
    MsgBox "The bubble size data range contains negative values. " _
      & "Bubble sizes cannot be negative.", vbOKOnly + vbCritical
    GoTo ExitSub
  End If
  
  ' ask user for largest bubble size
  Bmax = Application.InputBox("What is the largest bubble size (max = 72 points)?", _
    "Bubble Size", BubbleMaxDefault, , , , , 1)
  
  If TypeName(Bmax) = "Boolean" Then
    ' user canceled
    GoTo ExitSub
  End If
  
  Bmax = Val(Bmax) ' make sure it's numeric
  If Bmax < 0 Then
    ' can't have negative diameter bubbles
    MsgBox "The maximum bubble size must be positive.", vbOKOnly + vbCritical
    GoTo ExitSub
  ElseIf Bmax > 72 Then
    MsgBox "The maximum bubble size is 72 points.", vbOKOnly + vbInformation
    Bmax = 72
  End If
  
  ' loop through and resize markers
  For iPt = 1 To ActiveChart.SeriesCollection(1).Points.Count
    ' scale area of bubble to area of largest bubble
    ActiveChart.SeriesCollection(1).Points(iPt).MarkerSize = _
      Sqr(rBubbles.Cells(iPt) / Zmax) * Bmax
  Next iPt
      
ExitSub:
End Sub

The post Excel Scatter Bubble Chart Using VBA appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The Problem.

There are many annoyances when designing a VBA project to run on both Windows and Mac computers. One of the most noticeable is related to the difference in screen resolution between the two platforms. On the Mac, it’s 96 dpi, where each dot represents a pixel. In Windows, screen resolution is 72 dpi (dots per inch) and each dot represents a “point”, while pixels are still 96 per inch. Confusing the issue is that VBA in Windows uses points as the measurement unit for designing UserForms, while VBA on the Mac uses pixels. The result is that without applying any correction, UserForms that come out just right in Windows are only 75% as large on the Mac, making them difficult to read.

For example, here is a UserForm designed in Windows, opened in Windows. The text might seem a bit small to me now that my eyes are getting older, but it’s still perfectly legible.

Here is that same UserForm opened on a Mac. The header text is fine, but the text in the dialog itself is too small to read comfortably.

In the past, I supported separate add-ins, one for Windows and the other for Mac. The dialogs for each were appropriately sized, and I took care of other coding idiosyncrasies in the separate files. But it’s a lot of extra work to lug around two separate versions of every file. Every adjustment you make has to be made twice, and it’s hard to remember what changes you’ve made across large projects.

The Solution: UserForms For Mac And Windows.

I have developed a simple routine that is called from each UserForm’s initialize event, which changes the size, position, and font of each control by a factor of 4/3. The text still may not always be perfect, since you can only specify whole number font sizes, so I suppose I should always round up the font size. I do make sure when I design the form that controls are more than large enough in case the text takes up extra space. Aside from this, it works pretty nicely.

Here is a UserForm designed in Windows, but which uses the resizing routine so it displays legibly on either computer. First, it is shown opened in Windows.

Below it’s shown opened on the Mac. The dialogs are equally legible, and the text came out pretty much the same. Sometimes a large block of text may not wrap the same, so if it really matters, you should hard code your line breaks by typing Ctrl+Return while entering the text.

Note: While Microsoft has substantially improved the VB editor on the Mac, you still can’t work with UserForms on the Mac. You have to build them into your file in Windows and them move the file to the Mac.

The Code.

This calls the routine from the UserForm’s code module:

Private Sub UserForm_Initialize()
  #If Mac Then
    ResizeUserForm Me
  #End If
End Sub

The #If Mac Then and #End If structure indicates a section of code which is conditionally compiled. When using a Mac, the constant Mac is True, so the code between #If and #End If is compiled and run, so the UserForm is resized. In Windows, this code is ignored, and the UserForm is displayed without rescaling.

The global resizing factor is placed in an appropriate place in the declarations section of a regular code module:

Public Const gUserFormResizeFactor As Double = 1.333333

And this routine in a regular code module does the resizing:

Sub ResizeUserForm(frm As Object, Optional dResizeFactor As Double = 0#)
  Dim ctrl As Control
  Dim sColWidths As String
  Dim vColWidths As Variant
  Dim iCol As Long

  If dResizeFactor = 0 Then dResizeFactor = gUserFormResizeFactor
  With frm
    .Height = .Height * dResizeFactor
    .Width = .Width * dResizeFactor

    For Each ctrl In frm.Controls
      With ctrl
        .Height = .Height * dResizeFactor
        .Width = .Width * dResizeFactor
        .Left = .Left * dResizeFactor
        .Top = .Top * dResizeFactor
        On Error Resume Next
        .Font.Size = .Font.Size * dResizeFactor
        On Error GoTo 0

        ' multi column listboxes, comboboxes
        Select Case TypeName(ctrl)
          Case "ListBox", "ComboBox"
            If ctrl.ColumnCount > 1 Then
              sColWidths = ctrl.ColumnWidths
              vColWidths = Split(sColWidths, ";")
              For iCol = LBound(vColWidths) To UBound(vColWidths)
                vColWidths(iCol) = Val(vColWidths(iCol)) * dResizeFactor
              Next
              sColWidths = Join(vColWidths, ";")
              ctrl.ColumnWidths = sColWidths
            End If
        End Select
      End With
    Next
  End With
End Sub
Example Workbook.

I’ve posted a simple workbook that illustrates this technique. Download it by clicking the icon below.

When you open the workbook, you’ll see a blank worksheet with three buttons.

The first button opens the Windows-only UserForm shown in the first and second figures above, in the description of the problem.

The second button opens the Windows-and-Mac UserForm shown in the third and fourth figures, in the description of the solution.

The third button opens another Windows-and-Mac UserForm which also has a simple mechanism to call the ResizeUserForm procedure, and resize the UserForm on the fly. Here is it opened in Windows.

Enter a number in the New Form Magnification box (for example 2) and click Apply, and the form is resized accordingly.

Enter 1.333333, the ratio between pixels and points, and this is how it will be resized for the Mac, though it’s too large for Windows.

Enter 0.75, the inverse of the above, and you get a sense for how small it would be on the Mac without rescaling. Yeah, way too small.

Here is the same form opened on a Mac. Note that it’s already resized to 1.333333. Resized nicely.

Let’s see how it looks at 200%. Pretty large.

If you enter 1 in the box, you can see how the form would look without resizing.

In all of your real projects, you should apply this technique to all of your UserForms. Before deploying them to your users, you should make sure that the UserForms that look fine in Windows rescale properly on a Mac. Occasionally text wraps differently or certain other controls may need adjustments. But this eliminates the need for separate UserForms for Mac and Windows.

The post Designing UserForms for Mac and Windows appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The Problem

I received an email from a university professor who wanted to show a histogram of model residuals overlaid with a normal distribution curve. He wanted the histogram to be displayed as filled bars.

You can follow along with this tutorial using the sample workbook at the link below:

The Normal Curve

The normal curve data is shown below. C1 and C2 have the normal distribution mean and standard deviation. Column E has the values for which we’ll plot the normal distribution (from -380 in cell E3 to 380 in cell E41), and column F has the calculated distribution values. Cell F3 has this formula

=NORM.DIST(E3,$C$2,$C$3,FALSE)

and this is copied down to cell F41. Cell C5 has the maximum calculated distribution value (which is in cell F22), and cell C6 has the maximum height of any of the histogram bars. These latter values are used in column G, which “normalizes” the normal curve to the histogram, using this formula in cell G3:

=$C$6/$C$5*F3

which is filled down to cell G41. We normalize these normal distribution values so that the normal curve and the histogram can be plotted on the same vertical axis scale.

Chart 1 is an XY Scatter Chart (lines and no markers) showing the normal data from columns E and F:

Chart 2 shows the normalized normal data from columns E and G:

We’ll return to chart 2 in a moment.

The Histogram

The residual data is shown in P2:Q8, where P3:P8 holds the bin edges, and Q3:Q7 the counts between these values:

I’ve reformulated the residual data in columns S and T so I can plot the outlines of the histogram columns. This data is plotted in an XY Scatter chart, chart 3:

This is right out of my tutorial, Histograms Using Excel XY Charts.

If you don’t needed your histogram to be shaded, you could easily combine these two data sets into a simple XY Scatter chart (chart 4):

But shaded histogram bars require a little bit more work, as described in my tutorials Filled Histograms Using Excel XY-Area Charts and Histogram Using XY and/or Area Charts. To include the normal curve, you’ll need a combination chart, which I’ll show in the next section.

For the combination chart, I’ve modified the residuals data in columns AC and AD. The X values cannot be negative (I’ll tell you why later), so I’ve increased the X values by 400. I’ve also included extra zero values at the minimum (X = 0) and maximum (X = 800).

Chart 5 shows this data in a Line chart:

Chart 6 shows this data an Area chart:

I’ve formatted the area chart series with a medium blue outline (the default is no outline), and a light blue fill with 50% transparency, to let the gridlines show through.

We’ll ignore the line chart (chart 5), since it results in unfilled bars, and continue with the area chart (chart 6). Even though the area chart doesn’t give us nice bars (yet), we’ll beat on it until it does.

In Chart 7, I’ve set my X axis scale to be a Date scale:

The values in column AC are now treated as dates. In Excel there are no such thing as negative dates, so we had to offset these histogram X values by 400, so they are all greater than or equal to 0. A Date scale axis automatically uses the minimum and maximum X values as its endpoints (0 and 800), the X values are plotted proportionally, and each time an X value is repeated, the Y values are plotted on the same vertical line at that X value. This gives us our nice histogram bars.

Combination Chart with Normal Curve and Histogram

Chart 8 is the original normal curve from chart 2:

Copy the residuals data in AC:AD, select the chart, and use Paste Special so the data is plotted as a new series with X values in the first column and series name in the first row:

Chart 9 is the result. The histogram is plotted as a second XY Scatter series, and it’s offset to the right by 400.

In chart 10, I’ve changed the series type of the histogram data to an Area chart, and moved it to the secondary axis. It looks weird, but it’s too early to panic.

In chart 11, I’ve formatted the secondary horizontal axis (top of the chart) so it uses a Date axis scale. Don’t worry about formatting this axis. The bars are in the right place, just colored outside, not inside, the outlines.

In chart 12, I’ve hidden the secondary horizontal axis (don’t delete it). I did this by changing the line to no line, and changing the labels to no labels.

In lucky chart 13, I’ve selected and deleted the secondary Y axis (right side of the chart). This makes the area chart fill below the line, so inside the bars. And the chart is complete.

Related Topics

Here a few tutorials about Histograms:

Here is one about filling below a normal curve:

Here are some tutorials about shading of charts:

The post Histogram With Normal Curve Overlay appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Peltier Tech Charts for PowerPoint 3.0 is Ready for Beta Testing

I’ve talked about it in the past. If you work all the time in Excel, you probably don’t care, but if you work a lot in PowerPoint, and go back and forth between PowerPoint and Excel to get your data, then this might be interesting.

Description of Peltier Tech Charts for PowerPoint

Peltier Tech Charts for PowerPoint makes it easy to use Excel data in PowerPoint charts. When you initiate any of its features, the program detects the data range in the active Excel worksheet, and prepopulates it in the PowerPoint dialog. You can use the default PowerPoint data, you can use a static copy of the Excel data, or you can use dynamic links to the Excel data. If you change the linked data in Excel, you need to save the workbook before the chart will update in PowerPoint.

Peltier Tech Charts for PowerPoint is compatible with Office 2016 (regular and Office 365), Office 2013, and Office 2010 for Windows only. Office 2016 for Mac is not yet supported, but I hope to make that happen soon. Office 2007 is nearing the end of its support life (Microsoft will drop it later in 2017), so I am pre-emptively removing it.

I’m calling this version 3.0, because that’s the current version of Peltier Tech Charts for Excel. I’m also calling it the Advanced edition, even though it is lacking enough features to really be called “advanced”, because it will contain advanced features, and more features than a future “standard” edition.

I am hoping this will go commercial within a month as Peltier Tech Charts for PowerPoint 3.0B – Advanced Edition, even if it is light on features. Whoever licenses the Advanced edition can always get the latest update with the latest features. But while it is still light on features, it will have a lower price. I think I’ll offer licensees of the Excel software a good discount as well.

Details forthcoming.

About the Charts for PowerPoint Beta Program

This is a beta testing program, so not everything is finished, and not everything works as expected. You use it at your own risk. If you use it on that important presentation for the boss, and you wipe out this year’s sales to date, that’s on you.

This is a beta testing program, so the software will change frequently, and sometimes drastically. If you report a bug, I will pay particular attention to your report. If you make a suggestion, I will also pay close attention.

If you have strong feelings about which of my Excel features I should implement next in PowerPoint, let me know. In fact, this would help me prioritize my efforts.

I already know some things are not behaving ideally. There is a bug in PowerPoint 2016 that interferes with my feature that updates an existing PowerPoint chart with new Excel data. Getting the Excel data and putting it into PowerPoint seems to take way too long, especially in PowerPoint 2016 and 2013. Marimekko labeling is also slow. Sometimes the add-in seems unstable, and PowerPoint will go “Not Responding” for a while, or maybe forever?

Try Peltier Tech Charts for PowerPoint Beta

The download uses a shopping cart and checkout system since it’s integrated with the Peltier Tech system, but there is no charge, and no financial details are collected.


About Charts for PowerPoint Beta Features

Eventually Peltier Tech Charts for PowerPoint will incorporate most of the features of its more mature sibling, Peltier Tech Charts for Excel.

I have tested this software extensively on my own computers, mostly in Windows 8.1 and 10, and mostly in PowerPoint 2016. I’m counting on beta testers using other versions of Windows and Office, and who can give me feedback on these combinations.

Detailed installation instructions are given below. The current roster of features includes:

Update Existing PowerPoint Chart with Excel Data

Select a PowerPoint chart, click the button, and select the Excel data you want plotted in that chart.

This feature works great in PowerPoint 2013 and 2010. Unfortunately there is a bug in PowerPoint 2016 that lets my add-in (or any VBA) change the data, but then changes it back. Anyway, Microsoft is aware of the bug, and I hope to see it fixed soon. I also have an idea for a workaround, but I need to find time to test it.

Create New PowerPoint Chart with Excel Data

Click the button, select a chart type, and select the Excel data you want plotted in that chart.

This feature works great in PowerPoint 2016, 2013, and 2010. Detailed instructions are given below.

Create Peltier Tech Waterfall Chart in PowerPoint with Excel Data

Click the button, select your options and data, and out pops a waterfall chart on your slide. Detailed instructions for creating a waterfall chart are given below.

Create Peltier Tech Stacked Waterfall Chart in PowerPoint with Excel Data

Like the waterfall feature, except it’s a stacked waterfall.

Create Peltier Tech Marimekko Chart in PowerPoint with Excel Data

And this time it’s a Marimekko chart.

Detailed Installation Instructions

In PowerPoint, you can simply drag the add-in from Windows and drop it onto PowerPoint, and PowerPoint will install it. But there’s a more formal installation protocol, as follows.

Download the zip file, and unzip it into the User Library Directory

This is

    C:\Users\[USERNAME]\AppData\Roaming\Microsoft\AddIns

but you can get there by typing this

    %appdata%\Microsoft\AddIn

into the address bar of a File Explorer window.

Here is my User Library, filled with top secret projects.

Install the Add-In

In PowerPoint, click on the File tab and select Options near the bottom of the list. In the Options dialog, click on Add-Ins

Make sure that “PowerPoint Add-Ins” is selected in the dropdown at the bottom, and click Go to open the Add-Ins dialog.

I already have two highly classified add-ins installed.

Click Add New to open a File Browser window to hunt for add-ins to install. Fortunately there’s not much hunting involved, because the Browse window opens in the User Library.

Select the desired add-in and click OK.

The add-in is now installed (it’s in the list) and loaded (the box is checked).

The Peltier Tech ribbon tab can now be found at the top of the PowerPoint window.

The Peltier Tech dropdown has a few built-in subject lines for emails you can send to me.

Detailed Instructions to Insert a New Chart

The screenshot below shows the PowerPoint window on the left, beside the Excel window on the right. You don’t need to align the windows this way; my program can find them. Click on any shrunken image to view at full size.

Click the “Create New Chart” button to launch the dialog. Select your chart data, desired chart type and style, and whether you want the chart on the active slide or a new slide.

If you need to change the data selection, click the drop button next to the address, and my program will activate Excel and provide a small dialog where you can refine the selection. Now select a new range; you can even change the worksheet or workbook.

Finally click OK and your new chart appears momentarily.

Detailed Waterfall Chart Instructions

The screenshot below shows the PowerPoint window on the left, beside the Excel window on the right. Click on any shrunken image to view at full size.

Click the “Waterfall Chart” button to launch the dialog. Select your chart data, waterfall chart options, and whether you want the chart on the active slide or a new slide.

If you need to change the data selection, click the drop button next to the address, and my program will activate Excel and provide a small dialog where you can refine the selection. Now select a new range; you can even change the worksheet or workbook.

Finally click OK and your waterfall chart will appears shortly.

The post Peltier Tech Charts for PowerPoint Beta appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Peltier Tech Blog by Jon Peltier - 4M ago
Chart VBA Samples

I answered a few questions on Stack Overflow this morning, and before I deleted the test workbook, I thought I’d share a few of the chart VBA procedures. All of these have to do with applying fill colors to Excel chart series.

Reverse Default Colors

In the first example, from VBA Code To Change Fill Color Of Series On Chart, the user wanted to reverse the default colors in his chart. There were only three series, so it wasn’t too complicated.

His data looked like this, and the default chart color scheme is shown next to the data. The desired (reversed) color scheme is shown below the default: note that the last series is blue and the first is gray.

A further requirement is that, in case there are only two series, the second should be blue and the first orange.

And here is my simple chart VBA procedure for this:

Sub ReverseDefaultColors()
  Dim iSrs As Long, nSrs As Long
  
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  Else
    With ActiveChart
      nSrs = .SeriesCollection.Count
      ' work backwards from last series
      For iSrs = nSrs To 1 Step -1
        Select Case nSrs - iSrs
          Case 0 ' last series
            .SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
                msoThemeColorAccent1
          Case 1 ' next to last series
            .SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
                msoThemeColorAccent2
          Case 2 ' etc.
            .SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
                msoThemeColorAccent3
        End Select
      Next
    End With
  End If
End Sub
Format According to Performance

In the second example, from the same Stack Overflow post as above, the user wanted to color series based on series name. “On Time” should be medium green, “In Tolerance” should be light green, and “Late” should be red. We all know about red-green color vision deficiencies and that blue-orange is a preferred color scheme, but I mentioned that and still used red and green in my response.

Here is the desired outcome:

And here’s the chart VBA example:

Sub ColorGreenToRed()
  Dim iSrs As Long, nSrs As Long
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  Else
    With ActiveChart
      nSrs = .SeriesCollection.Count
      For iSrs = 1 To nSrs
        ' only format series whose names are found
        Select Case LCase$(.SeriesCollection(iSrs).Name)
          Case "on time"
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = _
                RGB(0, 176, 80) ' Green
          Case "in tolerance"
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = _
                RGB(146, 208, 80) ' Light Green
          Case "late"
            .SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = _
                RGB(255, 0, 0) ' Red
        End Select
      Next
    End With
  End If
End Sub
Reapply Automatic Colors

In the third example, from VBA Chart series set Color Fill to “Automatic”, the user often highlights some chart data, and would like an easy way to reapply the automatic colors. This is like clicking Automatic in the Format Data Series task pane. The way to do this is to reapply the default scheme colors.

This shows how the colors appear for the first 24 series in a chart (coincidentally the number of letters in the Greek alphabet). There are six theme colors, denoted in VBA by the constants msoThemeColorAccent1 through msoThemeColorAccent6 (which resolve to 5 through 10 and which correspond in the default Office 2013/2016 theme as blue, orange. gray, gold, blue, and green). The first set of six series use these colors as is, the second set of six darken them considerably, the third set lighten them somewhat, and the fourth set darken them but not so much as the second set. Beyond 24 series, there are other shades, but by then the chart is so cluttered that we’ll just ignore them.

Here is my reply in Stack Overflow:

Sub ReapplyDefaultColors()
  Dim iSrs As Long, nSrs As Long
  Dim iThemeColor As MsoThemeColorIndex
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  Else
    iThemeColor = msoThemeColorAccent1
    With ActiveChart
      nSrs = .SeriesCollection.Count
      For iSrs = 1 To nSrs
        .SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
            iThemeColor
        iThemeColor = iThemeColor + 1 ' msoThemeColorAccent2, 3, 4, etc.
        If iThemeColor > msoThemeColorAccent6 Then
          ' recycle colors
          ' should also adjust brightness
          iThemeColor = msoThemeColorAccent1
        End If
      Next
    End With
  End If
End Sub

Well, I thought, we can use a little math to get the theme colors and brightness levels, so let’s modify the loop.

Sub ReapplyDefaultColors1()
  Dim iSrs As Long, nSrs As Long
  Dim iThemeColor As MsoThemeColorIndex
  Dim iBrightness As Double
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  Else
    With ActiveChart
      nSrs = .SeriesCollection.Count
      For iSrs = 1 To nSrs
        iThemeColor = msoThemeColorAccent1 + (iSrs - 1) Mod 6
        Select Case Int(iSrs - 1) / 6
          Case 0
            iBrightness = 0
          Case 1
            iBrightness = -0.4
          Case 2
            iBrightness = 0.2
          Case 3
            iBrightness = -0.2
        End Select
        With .SeriesCollection(iSrs).Format.Fill.ForeColor
          .ObjectThemeColor = iThemeColor
          .Brightness = iBrightness
        End With
      Next
    End With
  End If
End Sub

And then, I thought of the deprecated but still available syntax for formatting charts from Excel 97-2003. Will the old .ColorIndex = xlAutomatic command still work with the new color system of Excel 2007-2016?

Sub ReapplyDefaultColors2()
  Dim iSrs As Long, nSrs As Long
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
  Else
    With ActiveChart
      nSrs = .SeriesCollection.Count
      For iSrs = 1 To nSrs
        .SeriesCollection(iSrs).Interior.ColorIndex = xlAutomatic
      Next
    End With
  End If
End Sub

As a matter of fact, it does.

The post Chart VBA Examples appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

A user of my Peltier Tech Charts for Excel 3.0 asked recently whether this program could perform interactive formatting of all series 2 in a set of charts to the same color.

Well, my program does many things, but it can’t do this. Such a highly specific feature is hard to support in a general purpose program. Next, someone will want to change all series 1 or series 3 or series named “Values”, or they’ll want a different color. It’s difficult to program for all of the various possibilities. (We can try, as I’ll show later.)

But if you need something particular like this, it’s not hard to spin your own VBA code.

Simple Code for Interactive Formatting

For my user’s request, it was as simple as filling a cell (say, H5) with a certain color, then changing the lines for each “Series2” in his charts to this color. The code looks like this:

Sub ColorSeries2LikeCellH5()
  Dim iColor As Long
  Dim shp As Shape
  Dim chob As ChartObject

  ' get the color
  iColor = ActiveSheet.Range("H5").Interior.Color

  If Not ActiveChart Is Nothing Then
    ' active chart (one chart selected)
    ActiveChart.SeriesCollection(2).Format.Line.ForeColor.RGB = iColor
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    ' multiple charts selected
    For Each shp In Selection.ShapeRange
      If shp.HasChart Then
        shp.Chart.SeriesCollection(2).Format.Line.ForeColor.RGB = iColor
      End If
    Next
  Else
    ' no chart selected so do all charts
    For Each chob In ActiveSheet.ChartObjects
      chob.Chart.SeriesCollection(2).Format.Line.ForeColor.RGB = iColor
    Next
  End If
End Sub
Which Chart to Format?

Let’s look a little closer at one piece of this code, which determines whether to work on one chart (the active chart), multiple selected charts (selected using Ctrl+Click or Shift+Click), or all charts on the active sheet (if no chart was selected). I’ve color-coded the explanations and the code sections.

If one chart is selected, then there is an active chart. The strange line of code “If Not ActiveChart Is Nothing Then” really means “If ActiveChart Is Something Then“, so we should process the active chart.

If multiple charts are selected, the selection consists of a collection called “DrawingObjects“, so we should process each shape in this collection (which is also a “ShapeRange“), provided that shape contains a chart.

Finally, if neither of these conditions are met (that is, no chart is selected), we’ll process each chart object on the active sheet. If there are no chart objects, the code will just finish up.

  If Not ActiveChart Is Nothing Then
    ' active chart (one chart selected)
    ' --> Process active chart
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    ' multiple charts selected
    For Each shp In Selection.ShapeRange
      If shp.HasChart Then
        ' --> Process each selected shape that contains a chart
      End If
    Next
  Else
    ' no chart selected so do all charts
    For Each chob In ActiveSheet.ChartObjects
      ' --> Process each chart object on the active sheet
    Next
  End If

How do I know all of this stuff? Well, I’ve been around for a long time, and I’ve swiped code from a lot of experts.

The code above is pretty good, but it’s not very flexible. If you need to get the color from a different cell, you need to edit the command that references cell H5. You need similar changes if you need to format a different series. This is fine if it’s your own code, but not if the user is your colleague who isn’t VBA-savvy.

Better, Flexible Interactive Formatting Code

So let’s add a couple mini-dialogs to ask the user what we should do. This makes it truly interactive formatting.

Which Cell Has the Desired Color?

First we’ll use Application.InputBox to ask the user which cell is filled with the color we want to use.

  On Error Resume Next
  Set rng = Application.InputBox("Select a cell filled with the desired line color", _
      "Select a Colored Cell", , , , , , 8)
  If rng Is Nothing Then GoTo ExitSub
  On Error GoTo 0
  iColor = rng.Interior.Color

This pops up the following dialog:

The 8 as the last argument for InputBox tells VBA to expect the user to supply a range.

We use “On Error Resume Next” because if the user cancels the InputBox, “Set rng =” will return an error. Bypassing the error, if the user cancels, rng will not be defined, so we will branch to ExitSub, a label at the end of the procedure.

Which Series Should We Change?

Next, we’ll use Application.InputBox to ask the user which series to change.

  vSrs = Application.InputBox("Enter the series number or name to be changed", _
      "Identify Series", , , , , , 3)
  If TypeName(vSrs) = "Boolean" Then GoTo ExitSub

This produces another dialog:

Using 1 as the last argument for InputBox tells VBA to expect a number, while using 2 tells VBA it’s text; we’ve used 3, which means number or text (3 = 1 + 2).

If the user cancels, what is returned is the Boolean value False (not the text value “False”). So we test for a Boolean, and if we get one, we branch to the ExitSub label as above.

The Improved Routine

So here’s the new and improved routine:

Sub ColorSeriesLikeCell()
  Dim rng As Range
  Dim vSrs As Variant
  Dim iColor As Long
  Dim shp As Shape
  Dim chob As ChartObject

  ' get cell filled with desired color
  On Error Resume Next
  Set rng = Application.InputBox("Select a cell filled with the desired line color", _
      "Select a Colored Cell", , , , , , 8)
  If rng Is Nothing Then GoTo ExitSub
  On Error GoTo 0
  iColor = rng.Interior.Color

  ' identify the series
  vSrs = Application.InputBox("Enter the series number or name to be changed", _
      "Identify Series", , , , , , 3)
  If TypeName(vSrs) = "Boolean" Then GoTo ExitSub

  If Not ActiveChart Is Nothing Then
    ' active chart (one chart selected)
    ActiveChart.SeriesCollection(vSrs).Format.Line.ForeColor.RGB = iColor
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    ' multiple charts selected
    For Each shp In Selection.ShapeRange
      If shp.HasChart Then
        shp.Chart.SeriesCollection(vSrs).Format.Line.ForeColor.RGB = iColor
      End If
    Next
  Else
    ' no chart selected so do all charts
    For Each chob In ActiveSheet.ChartObjects
      chob.Chart.SeriesCollection(vSrs).Format.Line.ForeColor.RGB = iColor
    Next
  End If
  
ExitSub:
End Sub

Well, that’s pretty nifty. But what if I have something besides a line chart?

Best, Versatile Interactive Formatting Code

Now we’ll add the capability to check each series chart type, and use the appropriate formatting syntax to change the line color, the marker color, or the fill color. To keep from repeating a lot of this code for checking series chart type, I’ll introduce a function to call to change the series formatting. Using a function also means that if I need to change or enhance the feature, I only need to change it one time in the function, and not everywhere I use the feature.

What’s the Chart Type?

I’ll pass the series and the color into the function, and it will determine based on the series chart type. The function will have this form:

Function RecolorSeries(srs As Series, iClr As Long) As Boolean
  ' assume True unless otherwise
  RecolorSeries = True
  
  Select Case srs.ChartType
    Case [Line] and [Scatter with Line Only]
      ' --> change line color
      
    Case [Line with Markers] and [Scatter with Lines and Markers]
      ' --> change line color
      ' --> change marker color (foreground and maybe background)
      
    Case [Scatter with Markers Only]
      ' --> change marker color (foreground and maybe background)
      
    Case [Bar], [Column], and [Area]
      ' --> change fill color
      
    Case Else
      ' did not reformat a series
      RecolorSeries = False
  End Select
End Function

We can call it like this (which is how I’ll call it)

  RecolorSeries [series], [color]

and it will run like a sub, or like

  bDidItWork = RecolorSeries([series], [color])

and it will still run like a sub, but then return the value True to bDidItWork if it formatted a series and False if it did not. You could then notify the user if one or more series were not changed because of their chart type.

Should We Fill the Marker?

We want to change the MarkerForegroundColor of any markers, but the MarkerBackgroundColor only if it is reasonable to fill the marker. For the Plus, X, and Star markers, filling the marker results in a big ugly square marker where the Plus, X, or Star foreground is not distinguished from the background. We could probably get more creative based on whether the existing background color matches the foreground, but this is just a blog post, eh?

So anyway, after we do the MarkerForegroundColor, we test for the marker style:

      Select Case srs.MarkerStyle
        Case xlMarkerStylePlus, xlMarkerStyleStar, xlMarkerStyleX
          ' fill should not be changed
        Case Else
          ' --> change the fill
      End Select
The Reformatting Function

All fixed up the function looks like this:

Function RecolorSeries(srs As Series, iClr As Long) As Boolean
  ' assume True unless otherwise
  RecolorSeries = True
  
  Select Case srs.ChartType
    Case xlLine, xlLineStacked, xlLineStacked100, _
        xlXYScatterLinesNoMarkers, xlXYScatterSmoothNoMarkers
      ' change line color
      srs.Format.Line.ForeColor.RGB = iClr
      
    Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100, _
        xlXYScatterLines, xlXYScatterSmooth
      ' change line color and marker color
      srs.Format.Line.ForeColor.RGB = iClr
      srs.MarkerForegroundColor = iClr
      Select Case srs.MarkerStyle
        Case xlMarkerStylePlus, xlMarkerStyleStar, xlMarkerStyleX
          ' fill should not be changed
        Case Else
          srs.MarkerBackgroundColor = iClr
      End Select
      
    Case xlXYScatter
      ' change marker color
      srs.MarkerForegroundColor = iClr
      Select Case srs.MarkerStyle
        Case xlMarkerStylePlus, xlMarkerStyleStar, xlMarkerStyleX
          ' fill should not be changed
        Case Else
          srs.MarkerBackgroundColor = iClr
      End Select
      
    Case xlBarClustered, xlBarStacked, xlBarStacked100, _
        xlColumnClustered, xlColumnStacked, xlColumnStacked100, _
        xlArea, xlAreaStacked, xlAreaStacked100
      ' change fill color
      srs.Format.Fill.ForeColor.RGB = iClr
      
    Case Else
      ' did not reformat a series
      RecolorSeries = False
  End Select
End Function
The Finished Main Routine

The main routine now looks like this, and it works in conjunction with the function above:

Sub RecolorSeriesLikeCell()
  Dim rng As Range
  Dim vSrs As Variant
  Dim iColor As Long
  Dim shp As Shape
  Dim chob As ChartObject

  ' get cell filled with desired color
  On Error Resume Next
  Set rng = Application.InputBox("Select a cell filled with the desired color", _
      "Select a Colored Cell", , , , , , 8)
  If rng Is Nothing Then GoTo ExitSub
  On Error GoTo 0
  iColor = rng.Interior.Color

  ' identify the series
  vSrs = Application.InputBox("Enter the series number or name to be changed", _
      "Identify Series", , , , , , 3)
  If TypeName(vSrs) = "Boolean" Then GoTo ExitSub

  If Not ActiveChart Is Nothing Then
    ' active chart (one chart selected)
    RecolorSeries ActiveChart.SeriesCollection(vSrs), iColor
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    ' multiple charts selected
    For Each shp In Selection.ShapeRange
      If shp.HasChart Then
        RecolorSeries shp.Chart.SeriesCollection(vSrs), iColor
      End If
    Next
  Else
    ' no chart selected so do all charts
    For Each chob In ActiveSheet.ChartObjects
      RecolorSeries chob.Chart.SeriesCollection(vSrs), iColor
    Next
  End If
  
ExitSub:
End Sub

The post Interactive Formatting of a Chart Series with a Cell’s Color appeared first on Peltier Tech Blog.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview