dinsdag 31 december 2013

Excel: Inspired By a Nice Graph in a Dutch News Paper

Inspired by a nice graph from the Volkskrant of last Saturday:


The data from A1:E15:

In procenten Helemaal natuur Beetje natuur Geen natuur
Tropisch regenwoud 95 4 1
De Veluwe 93 7 0
De Waddenzee 91 8 1
Damherten 83 15 2
Schone lucht 78 18 4
Merels 75 23 2
Schapen in de wei 68 30 2
Graanvelden 52 41 7
Een boomrijk stadspark 40 56 4
Medicinale planten 32 49 19
Een klimop in de achtertuin 24 59 17
Muntthee 11 38 51
Bedrijventerrein met bomen 5 41 54
Een hondenuitlaatplaats 3 18 79

You can download the file excelnatuur.xlsx through this link:

vrijdag 13 december 2013

Excel: Line Chart With Markers with Different Colors Above and Below Target

A simple but nice idea. I just could not work out the idea to give the line above different colors when above or below the target line. Myabe somebody has a better idea?


Here are the data from A1:E14:

Year Real Target Above Below
2001 130 100 130 #N/A
2002 120 100 120 #N/A
2003 80 100 #N/A 80
2004 110 100 110 #N/A
2005 90 100 #N/A 90
2006 120 100 120 #N/A
2007 76 100 #N/A 76
2008 111 100 111 #N/A
2009 112 100 112 #N/A
2010 97 100 #N/A 97
2011 95 100 #N/A 95
2012 104 100 104 #N/A
2013 106 100 106 #N/A

You can download the file lijngrafiek.xlsx through this link:

woensdag 11 december 2013

Excel: Bar and Column Graph Combined Gives a banded Chart

When you combine a bar and a column graph you can create this nice example:


Here are the data from A1:C10:

Year Amount Bands
2001  € 120,00 0
2002  € 134,00 8
2003  € 100,00 0
2004  € 123,00 8
2005  € 154,00 0
2006  € 111,00 8
2007  €   98,00 0
2008  € 145,00 8
0

You can download the file ExcelBanded.xlsx through this link:

zondag 8 december 2013

Friesland: chronogrammen in grafmonumenten of muurstenen

Onlangs kreeg ik een e-mail van Hindrik de Jong uit Tjalleberd. Hij had een bericht ontvangen van een Duitse kennis met een grafschift waarin een jaartal vervlochten zat:

Mors est pIo abItVs aD ChrIstI regna

Hij vroeg zich af of wij zoiets in Friesland wel eens tegengekomen waren. De twee gevallen die mij te binnen willen schieten, zijn die van de muursteen in de kerktoren van de Benedictuskerk te Dantumawoude (http://www.benedictuskerk.nl/?pagina=toren):

theoDorVs V sCheLtInga
Den eerstgeboren
geeft aen ons toren
een eeVWig steen
Voor VaDers been.

en de stichtingssteen van de St. Michaelskerk te Woudsend:

haeC eXstrUCta DeI trInI est In LaUDIbUs aeDes eXstet et In pago grata per enne bonIs


Alle waarden van de hoofdletters, als Romeinse cijfers, vormen het totaal van 1792.

Van een goede vriend hoorde ik dat dit fenomeen chronogram wordt genoemd. Een andere naam is jaardicht (http://nl.wikipedia.org/wiki/Jaardicht)

Ik heb eens rondgekeken naar meer voorbeelden. Via de website http://erfgoedkloosterleven.nl vond ik nog dit voorbeeld uit de Noord Brabantse plaats Sint Agatha:

hIC CVbat henrICVs / saCrI DeCVs orDInIs / Ingens / regIa VIrtVtis / reLLIgIonIs apeX

De jaartallen berekenen we als volgt. Ik doe dit even aan de hand van het eerste voorbeeld. We schrijven het schema van de Romeinse getallen verticaal uit:

M = 1000
D  = 500
C = 100
L = 50
X = 10
V = 5
I = 1

Daar achter noteren we hoe vaak elke letter als hoofdletter voorkomt in het chronogram. In dit geval:

M 1
D 1
C 1
X
V 1
I 4

We komen dan op 1 * 1000 + 1 *500 + 1 *100 + 1*5 + 4*1 = 1609

Zo kunnen we dus van elke chronogram of jaardicht het jaartal berekenen. Ik ben benieuwd of we in Nederland en specifiek in Friesland nog meer van dit soort voorbeelden tegenkomen.


Oudewater: chronogram in paneel communiebank Oudkatholieke kerk 1708

vrijdag 6 december 2013

Excel: Graph with two Y Axis

Saw a graph with two Y-axis in the Dutch magazine Elsevier. This is what I made of it:


The orginal looked like this:


Here are the data from A1:C8

Dec. 2013 Compared to 2012
Germany 1,74 % 0,36 %
Netherlands 2,06 % 0,45 %
France 2,17 % 0,13 %
Italy 4,08 % -0,42 %
Spain 4,15 % -1,16 %
Portugal 5,87 % -1,70 %
Greece 8,54 % -7,39 %

You can download the file ExcelTwoYAxis.zip through this link:

donderdag 5 december 2013

Word VBA: Autotext and Built In Building Block Entries

The first script deletes all autotext entire from the normal.dotm

Sub DeleteNormalAutoTextEntries()
    'goal: delete all autotext entries from normal.dotm
    Dim i As AutoTextEntry
    'wissen autotextentries
    For Each i In NormalTemplate.AutoTextEntries
        i.Delete
    Next
End Sub

The second script deletes all built in building block entries from the template built-in building blocks.dotx

Sub DeleteBuiltinBuildingblockentries()

    'Word 2010 and up
    'goal: delete all elements from built-in building blocks.dotx

    On Error Resume Next 'necessary while deleting from the template causes errors

    Dim objTemplate As Template
    Dim i As Integer 'counter
    Dim bb As Word.BuildingBlock 

    Set objTemplate = Templates(1) 'templates(1) = built-in building blocks.dotx
    For i = 1 To 10 'you need more loupes while it simply doed not work to delete all in one round
        For Each bb In objTemplate.BuildingBlockEntries
           bb.Delete
        Next
    Next
    objTemplate.Save 'save template
End Sub

The last script imports given auto text entried from a two column table in a Word document:

Sub ImportAutotextFromWordTable()
    'goal: load autotext entries from Word document table 
    'two columns: the first with the entry lable, the second with the entry text

    On Error GoTo errormessage
    Dim oTable As Table
    Dim oRow As Row
    Dim name As String
   
    Application.Documents.Open ("C:\autotext.docx") 'this file needs to be in a given map
    For Each oRow In ActiveDocument.Tables(1).Rows
        name = Left(oRow.Cells(1).Range.Text, Len(oRow.Cells(1).Range.Text) - 2)
        If Left(oRow.Cells(2).Range.Text, Len(oRow.Cells(2).Range.Text) - 2) < 256 Then
            NormalTemplate.AutoTextEntries.Add name:=name, Range:=Selection.Range
            NormalTemplate.AutoTextEntries(name).Value = Left(oRow.Cells(2).Range.Text, Len(oRow.Cells(2).Range.Text) - 2)
        Else
            ' If the length of the text is greater than 255 characters,
            ' insert it into the active document and add it as a new
            ' AutoText Entry.
            With Selection.Range
                .Collapse
                .Text = Left(oRow.Cells(2).Range.Text, Len(oRow.Cells(2).Range.Text) - 2)
                .Select
                NormalTemplate.AutoTextEntries.Add _
                   name:=name, Range:=Selection.Range
                .Delete
            End With
        End If
    Next
   
    Exit Sub
   
errormessage:
    MsgBox "File C:\autotext.docx is missing"

End Sub

zaterdag 30 november 2013

Excel: Pie Chart With Two Different Pies

In Excel it looked impossible two create a pie chart with two different pies in one chart. And of course I did not want to paste two pies together. By using the type Pie of Pie I managed to do it. Could not yet find an example of this on the internet.


Here are the data from A1:D7:

Fruit Vegetables
Apples 1 Lettuce 7
Pear 2 Cabbage 8
Bananas 3 Spinach 9
Grapes 4 Endive 10
Cherries 5 Carrots 11
Strawberries 6 Sprouts 12

You can download the file ExcelTwoCircles.zip through this link:

vrijdag 29 november 2013

Excel: Separated X axis

Very nice graph in a Dutch newspaper (de Trouw) which I remade in Excel.


Here are the data from A1:D5:

Liquor Price (%) Liquor Sales (%) tussen
2012 2,4 -4,0 -1,5
2013/1 3,8 -10,4 -1,5
2013/2 3,7 -9,6 -1,5
2013/3 3,8 -8,1 -1,5
  • Create a Stacked Bar Diagram based on the range A1:D5.
  • Set the color of the fourth part to No fill.
  • Add a line graph based on column two.
  • Add labels for this line.
  • Choose Above for the labels.
  • Set the color of the line graph to No color.
  • Create a name label2:
=Blad1!$C$2:$C$5+Blad1!$D$2:$D$5
  • Add this name to the graph as a line graph.
  • Add labels for this line.
  • Choose Below for the labels.
  • Set the color of the line graph to No color.
  • Add a another line graph based on column four.
  • Add this line to the Secondary Axis.
  • Change the secondary Y-axis in a way that the horizontal axis crosses at -1,5.
  • Both axes should run from 6 to -14.
  • Remove both Y-axis.
  • Change both X-axes so that the line is black.
  • Remove the labels of the secondary horizontal axis.
  • Add two boxes and link those to B1 and C1 respectively.
  • Format the boxes and the font color like I did in the graph.
Then, the outcome should look like the picture.

You can download the file ExcelUpDown.zip through this link:

donderdag 28 november 2013

Word: Aanpassen van het lint in Word 2010 via XML en VBA

1.1     Inleiding

In de volgende voorbeelden gebruiken we de Office Custom UI Editor en Word template add-ins. We gebruiken deze manier omdat:

§   de Custom UI Editor automatisch de vereiste Open Office XML bestandsrelaties maakt
§   we dan geen additionele ontwikkelsoftware als Visual Studio nodig hebben
§   en we gewoon gebruik kunnen maken van VBA om het lint aan te roepen

De Office Custom UI Editor kunne we gratis downloaden via OpenXMLDeveloper.org: Custom UI Editor

Het aanpassen van de linten van Word zoals hier getoond, kunnen we toepassen op een los Word sjabloon of via een Word add-in.

1.2     Voorbeeld 1

Het eerste voorbeeld is tamelijk simpel. We gaan hier een losse knop op een tab verbergen. We gaan de knop Macros op de tab Beeld verbergen.

§   Maak een leeg sjabloon dat macros kan bevatten: voorbeeld1.dotm
§   Sluit het document en sluit Word.
§   Start de Office Custom UI Editor.
§   Open het sjabloon voorbeeld1.dotm.
§   Voeg de juiste Office Custom UI Part toe.


§   Plak het volgende RibbonXML script in het CustomUI venster.

  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
         <tabs>
         <tab idMso="TabView">
           <group idMso="GroupMacros" visible="false" />
         </tab>
         </tabs>
    </ribbon>
  </customUI>

§   Sla het bestand op.
§   Sluit het Custom UI Editor bestand.

Als we Voorbeeld1.dotm nu openen, zullen we zien dat de knop Macros op de tab Beeld verdwenen is.

1.3     Voorbeeld 2

We gaan nu op de tab Start knoppen toevoegen, verwijderen en herschikken. Op de tab Start zien we een groep Klembord vooraan en achteraan een groep bewerken. Die twee gaan we combineren in een nieuwe groep Bewerken en Klembord.

§   Maak een leeg sjabloon dat macros kan bevatten: voorbeeld2.dotm
§   Sluit het document en sluit Word.
§   Start de Office Custom UI Editor.
§   Open het sjabloon voorbeeld2.dotm.
§   Voeg de juiste Office Custom UI Part toe.
§   Plak het volgende RibbonXML script in het CustomUI venster.

  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
         <tabs>
         <tab idMso="TabHome">
           <group idMso="GroupEditing" visible="false" />
           <group idMso="GroupClipboard" visible="false" />
           <group id="CustomGroup" label="Bewerken en Klembord" insertBeforeMso="GroupFont">
              <splitButton idMso="PasteMenu" size="large" />
                <button idMso="Cut" />
                <button idMso="Copy" />
                  <control idMso="FormatPainter" />
                  <separator id="Sep1" />
                  <button idMso="FindDialog" />
                  <button idMso="ReplaceDialog" />
                  <menu idMso="SelectMenu" />
              <dialogBoxLauncher >
                <button idMso="ShowClipboard" />
              </dialogBoxLauncher>
           </group>
         </tab>
         </tabs>
    </ribbon>
  </customUI>

§   Sla het bestand op.
§   Sluit het Custom UI Editor bestand.

Als we Voorbeeld2.dotm nu openen, zullen we zien dat de knoppen de tab Start zijn samengevoegd.

1.4     Voorbeeld 3

In het laatste voorbeeld laten we zien hoe we eigen knoppen kunnen maken en deze toevoegen aan een eigen tab.

§   Maak een leeg sjabloon dat macros kan bevatten: voorbeeld3.dotm
§   Sluit het document en sluit Word.
§   Start de Office Custom UI Editor.
§   Open het sjabloon voorbeeld3.dotm.
§   Voeg de juiste Office Custom UI Part toe.
§   Plak het volgende RibbonXML script in het CustomUI venster.
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="RibbonControl.Onload">
    <ribbon>
         <tabs>
         <tab id="CustomTab1" label="My Tab">
           <group id="CustGrp1" label="Macros" >
              <button id="Btn1" label="Show Visual Basic Editor" imageMso="VisualBasic"
                    onAction="RibbonControl.MyBtnMacro" />
            <dropDown id="DD1" label="Run Macro"
                      getItemCount="RibbonControl.GetItemCount"
                      getItemLabel="RibbonControl.GetItemLabel"
                      getSelectedItemIndex="RibbonControl.GetSelectedItemIndex"
                      onAction="RibbonControl.MyDDMacro" />
        </group>
        <group id="CustGrp2" label="Shortcuts" >
          <toggleButton id="TB1" size="normal"
                        getImage="RibbonControl.getImage"
                        getLabel="RibbonControl.getLabel"
                        onAction="RibbonControl.ToggleonAction"
                        getPressed="RibbonControl.buttonPressed" />
          <toggleButton id="TB2" size="normal"
                        getImage="RibbonControl.getImage"
                        getLabel="RibbonControl.getLabel"
                        onAction="RibbonControl.ToggleonAction"
                        getPressed="RibbonControl.buttonPressed" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

§   Sla het bestand op.
§   Sluit het Custom UI Editor bestand.

Nu gaan we naar het tweede deel van de oplossing: het VBA project.

§   Open voorbeeld3.dotm
§   Open de VB Editor (ALT+F11).
§   Voeg twee standaard projectmodules toe.
§   Noem deze RibbenControl en Macros.
§   Plak de volgende code in RibbonControl.

Option Explicit
Public myRibbon As IRibbonUI

Sub Onload(ribbon As IRibbonUI)
  'Creates a ribbon instance for use in this project
  Set myRibbon = ribbon
End Sub

'Callback for DropDown GetItemCount
Sub GetItemCount(ByVal control As IRibbonControl, ByRef count)
  'Tell the ribbon to show 4 items in the dropdown
  count = 4
End Sub

'Callback for DropDown GetItemLabel
Sub GetItemLabel(ByVal control As IRibbonControl, Index As Integer, ByRef label)
  'This procedure fires once for each item in the dropdown. Index is _
   received as 0, 1, 2, etc. and label is returned.
  label = Choose(Index + 1, "Select from list", "Macro 1", "Macro 2", "Macro 3")
End Sub

'Callback DropDown GetSelectedIndex
Sub GetSelectedItemIndex(ByVal control As IRibbonControl, ByRef Index)
  'This procedure is used to ensure the first item in the dropdown is selected _
   when the control is displayed
  Select Case control.id
    Case Is = "DD1"
      Index = 0
    Case Else
      'Do nothing
  End Select
End Sub

'Callback for DropDown onAction
Sub myDDMacro(ByVal control As IRibbonControl, selectedID As String, selectedIndex As Integer)
  Select Case selectedIndex
    Case 0
      'Do nothing
    Case 1
      Macros.Macro1
    Case 2
      Macros.Macro2
    Case 3
      Macros.Macro3
  End Select
  'Force the ribbon to restore the control to its original state
  myRibbon.InvalidateControl control.id
End Sub

'Callback for Button onAction
Sub MyBtnMacro(ByVal control As IRibbonControl)
  Select Case control.id
    Case Is = "Btn1"
      Macros.ShowEditor
    Case Else
      'Do nothing
  End Select
End Sub

'Callback for Toogle onAction
Sub ToggleonAction(control As IRibbonControl, pressed As Boolean)
  Select Case control.id
    Case Is = "TB1"
      ActiveWindow.View.ShowBookmarks = Not ActiveWindow.View.ShowBookmarks
    Case Is = "TB2"
      'Note: "pressed" represents the toggle state. So we could use this instead.
      If pressed Then
        ActiveWindow.View.ShowHiddenText = False
      Else
        ActiveWindow.View.ShowHiddenText = True
      End If
      If Not ActiveWindow.View.ShowHiddenText Then
        ActiveWindow.View.ShowAll = False
      End If
  End Select
  'Force the ribbon to redefine the control wiht correct image and label
  myRibbon.InvalidateControl control.id
End Sub

'Callback for togglebutton getLabel
Sub getLabel(control As IRibbonControl, ByRef returnedVal)
  Select Case control.id
    Case Is = "TB1"
      If Not ActiveWindow.View.ShowBookmarks Then
        returnedVal = "Show Bookmarks"
      Else
        returnedVal = "Hide Bookmarks"
      End If
    Case Is = "TB2"
      If Not ActiveWindow.View.ShowHiddenText Then
        returnedVal = "Show Text"
      Else
       returnedVal = "Hide Text"
      End If
  End Select
End Sub

'Callback for togglebutton getImage
Sub GetImage(control As IRibbonControl, ByRef returnedVal)
  Select Case control.id
    Case Is = "TB1"
      If ActiveWindow.View.ShowBookmarks Then
        returnedVal = "_3DTiltRightClassic" 'The idMso of a built-in control
      Else
        returnedVal = "_3DTiltLeftClassic" 'The idMso of a built-in control
      End If
    Case Is = "TB2"
      If ActiveWindow.View.ShowHiddenText Then
        returnedVal = "WebControlHidden" 'The idMso of a built-in control
      Else
        returnedVal = "SlideShowInAWindow" 'The idMso of a built-in control
     End If
  End Select
End Sub

'Callback for togglebutton getPressed
Sub buttonPressed(control As IRibbonControl, ByRef toggleState)
  'toggleState (i.e., true or false) determines how the 'toggle appears _
   on the ribbon (i.e., flusn or sunken).
  Select Case control.id
    Case Is = "TB1"

      If Not ActiveWindow.View.ShowBookmarks Then
        toggleState = True
      Else
        toggleState = False
      End If
    Case Is = "TB2"
      If Not ActiveWindow.View.ShowHiddenText Then
        toggleState = True
      Else
        toggleState = False
      End If
  End Select
End Sub

§   Plak de volgende code in Macros:

Option Explicit

Sub ShowEditor()
ShowVisualBasicEditor = True
End Sub

Sub Macro1()
MsgBox "Macro 1 running" 'Replace this with whatever code you would wish to run.
End Sub

Sub Macro2()
MsgBox "Macro 2 running" 'Replace this with whatever code you would wish to run.
End Sub

Sub Macro3()
MsgBox "Macro 3 running" 'Replace this with whatever code you would wish to run.
End Sub

§   Sla de sjabloon op.
§   Sluit deze.
§   Sluit Word.
§   Zet de sjabloon in de start-up directory van Word.
§   Open Word.
§   Test de zelfgemaakte knoppen.

1.5     Voorbeeld 4

In onderstaand voorbeeld bepaalt de property startFromScratch of de tab als enige op het lint tevoorschijn komt of niet (True = als enige; False = naast de andere tabs).

De property insertBeforeMso bepaalt op welk plek de tab tevoorschijn komt.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="OME" label="OME" keytip="OME" insertBeforeMso="TabHome">
         <group id="Zaaknummers" label="Zaaknummers">
           <button id="ZaaknummersAanpassen" label="Zaaknummers aanpassen" imageMso="HappyFace" size="large" onAction="ctlZaaknummersAanpassen" />
         </group>
         <group id="Brievenmacros" label="Brievenmacro's">
           <button id="OpenenVordering" label="Openen vordering" imageMso="FileOpen" size="large" onAction="ctlOpenenVordering" />
           <button id="NieuwePagina" label="Nieuwe pagina" imageMso="FileNew" size="large" onAction="ctlNieuwePagina" />
           <button id="RegelsWeghalen" label="Regels weghalen" imageMso="InkEraseMode" size="large" onAction="ctlRegelsWeghalen" />
           <button id="PrintenEnkel" label="Printen enkel" imageMso="FilePrint" size="large" onAction="ctlPrintenEnkel" />
           <button id="PrintenMeer" label="Printen meer" imageMso="PageSetupPageDialog" size="large" onAction="ctlPrintenMeer" />
         </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

De tab OME ziet er dan zo uit:


1.6     Zelfgemaakte tabs in de Office 2010 Backstage View

Om dit voormekaar te krijgen, hebben we een iets andere opbouw van het XML bestand nodig.
Onderstaande is een voorbeeld van XML code voor het aanpassen van de Backstage view.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
   <backstage>
      <tab id="customTab" label="Sample Tab">
         <firstColumn>
             <group id="customGroup" label="Custom Group">
                <topItems>
                   <button id="customButton" label=" Load &amp;&amp; Return" imageMso="BevelShapeGallery" onAction="OnAction" isDefinitive="true" />
                </topItems>
             </group>
          </firstColumn>
       </tab>
   </backstage>
</customUI>

Nog een voorbeeld:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" >
   <backstage>
      <button id="saveBtn" label="Load &amp;&amp; Return" imageMso="SourceControlCheckIn" keytip="Z" insertAfterMso="FileSaveAs" onAction="OnAction" isDefinitive="true" />
   </backstage>
</customUI>

Met bijbehorend VBA:

Sub OnAction(control As IRibbonControl)
    Dim wdApp As Word.Application
   
    Set wdApp = GetObject(, "Word.Application")
    wdApp.Documents.Open FileName:="C:\< your path here>\MyResume.dotx", ReadOnly:=True, AddtoRecentFiles:=False
End Sub

1.7     Overzicht van de plaatjes voor de knoppen

Dit kunnen we vinden via de link http://soltechs.net/CustomUI/imageMso01.asp