Create, modify and delete validation lists in Excel with VBA

Problem

We want to create, edit or delete validation lists in Excel using VBA.

Solución

• Creating a validation list

For the range $A$2:$A$5 we create a validation list based on $C$2:$C$5.

Sub Create_list()
    With Range("$A$2:$A$5").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$C$2:$C$5"
    End With
End Sub


• Modifying a validation list

For the range $A$2:$A$5 we modify the validation list source now based on $E$2:$E$5.

Sub Modify_list()
    With Range("$A$2:$A$5").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$E$2:$E$5"
    End With
End Sub


• Deleting a validation list

We delete the validation lists for the specified range.

Sub Delete_list()
    With Range("$A$2:$A$5").Validation
        .Delete        
    End With
End Sub

If instead of a range, we'd like to apply the code to a selection:

'Replace Range("$A$2:$A$5").Validation for
Selection.Validation

References

• Validation.Modify method

Crear, borrar y editar listas de validación en Excel con VBA

Problema

Queremos crear, editar o borrar listas de validación en Excel mediante VBA.

Solución

• Creación de una lista de validación - Creamos para el rango $A$2:$A$5 una lista de validación basada en $C$2:$C$5.

Sub Crear_lista()
    With Range("$A$2:$A$5").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$C$2:$C$5"
    End With
End Sub


• Edición de una lista de validación - Editamos el rango con la nueva lista de validación, en nuestro ejemplo $E$2:$E$5.

Sub Editar_lista()
    With Range("$A$2:$A$5").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$E$2:$E$5"
    End With
End Sub


• Borramos la lista de validación del rango - Borramos las listas de validación del rango seleccionado.

Sub Borrar_lista()
    With Range("$A$2:$A$5").Validation
        .Delete        
    End With
End Sub

Si en lugar de un rango queremos aplicarlo una selección:

'Reemplazamos Range("$A$2:$A$5").Validation por 
Selection.Validation

Get Windows user name in VBA

Problem

We'd like to retrieve the current Windows user name in VBA. For instance, based on that value, we could track access, enable login, or personalize the information displayed in our files.

Solution

1. We open the Microsoft Visual Basic Editor: Alt+F11.
2. We copy the following subroutine.

Sub username()
MsgBox Environ("UserName")
End Sub

3. We run it pressing F5.
4. A message box will display the name of the current user.

We can type the following expression into the Immediate window:

?Environ("Username")

We can store the user name in a cell.

Sub usuario()
Sheets("Hoja1").Range("A1") = Environ("UserName")
End Sub

How to remove space between axis and plot area in ggplot2

Problem

We want to remove the space between the axis and the plotting area. The space between the axis and the tick marks.

# data
set.seed(0)
the.df <- data.frame( x = rnorm(800, 50, 10), group = rep(letters[1:8], each = 100))

# Original plot
p <- ggplot(the.df) + 
  stat_density(aes(x = x, linetype = group), geom = "line", position = "identity") +
  xlim(10, 90) + ylim(0, 0.06) +
  scale_linetype_manual(values = c("11", "12", "13", "14", "21", "22", "23", "24")) +
  geom_segment(aes(x = 10, y = 0, xend = 90, yend = 0)) +
  geom_segment(aes(x = 10, y = 0, xend = 10, yend = 0.06))

Solution

Two alternatives

Scale continuous an expand

Instead of xlim(10, 90) + ylim(0, 0.06) we use a continous scale for both axis scale_x_continuous y scale_x_continuous, specifying expand = c(0, 0).

p <- ggplot(the.df) + 
  stat_density(aes(x = x, linetype = group), geom = "line", position = "identity") +
  scale_linetype_manual(values = c("11", "12", "13", "14", "21", "22", "23", "24")) +
  scale_x_continuous(limits=c(10, 90), expand = c(0, 0)) +
  scale_y_continuous(limits=c(0, 0.06), expand = c(0, 0)) +
  geom_segment(aes(x = 10, y = 0, xend = 90, yend = 0)) +
  geom_segment(aes(x = 10, y = 0, xend = 10, yend = 0.06))

coord_cartesian

We can user coord_cartesian instead of scale_x_continuous y scale_x_continuous.

p <- ggplot(the.df) + 
  stat_density(aes(x = x, linetype = group), geom = "line", position = "identity") +
  scale_linetype_manual(values = c("11", "12", "13", "14", "21", "22", "23", "24")) +
  geom_segment(aes(x = 10, y = 0, xend = 90, yend = 0)) +
  geom_segment(aes(x = 10, y = 0, xend = 10, yend = 0.06))+
  coord_cartesian(xlim = c(10, 90), ylim = c(0, .06))

Another example

uniq <- structure(list(year = c(1986L, 1987L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 1986L, 1987L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 1986L, 1987L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L), uniq.loc = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), .Label = c("u.1", "u.2", "u.3"), class = "factor"), uniq.n = c(1, 1, 1, 2, 5, 4, 2, 16, 16, 10, 15, 14, 8, 12, 20, 11, 17, 30, 17, 21, 22, 19, 34, 44, 56, 11, 0, 0, 3, 3, 7, 17, 12, 21, 18, 10, 12, 9, 7, 11, 25, 14, 11, 17, 12, 24, 59, 17, 36, 50, 59, 12, 0, 0, 0, 1, 4, 6, 3, 3, 9, 3, 4, 2, 5, 2, 12, 6, 8, 8, 3, 2, 9, 5, 20, 7, 10, 8), uniq.p = c(100, 100, 25, 33.3, 31.2, 14.8, 11.8, 40, 37.2, 43.5, 48.4, 56, 40, 48, 35.1, 35.5, 47.2, 54.5, 53.1, 44.7, 24.4, 46.3, 37.8, 43.6, 44.8, 35.5, 0, 0, 75, 50, 43.8, 63, 70.6, 52.5, 41.9, 43.5, 38.7, 36, 35, 44, 43.9, 45.2, 30.6, 30.9, 37.5, 51.1, 65.6, 41.5, 40, 49.5, 47.2, 38.7, 0, 0, 0, 16.7, 25, 22.2, 17.6, 7.5, 20.9, 13, 12.9, 8, 25, 8, 21.1, 19.4, 22.2, 14.5, 9.4, 4.3, 10, 12.2, 22.2, 6.9, 8, 25.8)), .Names = c("year", "uniq.loc", "uniq.n", "uniq.p"), class = "data.frame", row.names = c(NA, -78L))

Original plot

ggplot(data = uniq) + 
  geom_area(aes(x = year, y = uniq.p, fill = uniq.loc), stat = "identity", position = "stack") +
  scale_x_continuous(limits=c(1986,2014)) +
  scale_y_continuous(limits=c(0,101)) +
  theme_bw()

Plot without spaces

# Scale_x_continuous y expand = c(0, 0)
ggplot(data = uniq) + 
  geom_area(aes(x = year, y = uniq.p, fill = uniq.loc), stat = "identity", position = "stack") +
  scale_x_continuous(limits=c(1986,2014), expand = c(0, 0)) +
  scale_y_continuous(limits=c(0,101), expand = c(0, 0)) +
  theme_bw() + theme(panel.grid=element_blank(), panel.border=element_blank())

 # coord_cartesian
ggplot(data = uniq) +  
  geom_area(aes(x = year, y = uniq.p, fill = uniq.loc), stat = "identity", position = "stack") +  
  coord_cartesian(xlim = c(1986,2014), ylim = c(0,101))+
  theme_bw() + theme(panel.grid=element_blank(), panel.border=element_blank())

References

• How to remove extra space beyond xlim and ylim in ggplot?
• How to remove space between axis & area-plot in ggplot2?

How to change the stacking order in a stacked bar chart in ggplot2

Problem

We want to control the stacking order of a stacked bar created with ggplot2.

• Original chart
By default the bars are stacked in alphabetical order based on the variable passed on to the argument fill.

library(reshape2)
library(ggplot2)
ra.melt <- melt(ra)
p <- ggplot(ra.melt, aes(x = variable, y = value))
p + geom_bar(aes(fill = quality), stat = "identity") + 
  labs(x = "group", y = "percentage (%)")

We double-check the levels of the variable quality.

levels(ra.melt$quality)

 [1] "A"     "B"     "C"     "D"     "E"     "F"    
 [7] "G"     "H"     "I"     "J"     "K"     "L"    
[13] "M"     "other"

• Data

ra <- structure(list(quality = structure(c(2L, 6L, 13L, 1L, 7L, 5L, 
10L, 4L, 3L, 9L, 11L, 12L, 8L, 14L), .Label = c("A", "B", "C", 
"D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "other"), class = "factor"), GY = c(25.5932389, 13.9567819, 11.7333, 8.6439, 7.8351008, 
    8.29, 6.059643, 3.8608517, 4.3277, 3.1710598, 1.2021144, 
    1.0986329, 0.9369271, 3.2907496), TH = c(23.0215577, 15.8975387, 
    10.5075, 7.8888, 8.8121504, 8.276, 6.4356378, 3.9147685, 
    5.4838, 3.9339386, 1.3921565, 0.742406, 0.8795013, 2.8142444
    ), SZ = c(21.217152, 16.0363831, 11.154, 8.5094, 7.945884, 
    10.2717, 5.4962929, 4.1381946, 5.1141, 3.4053352, 1.8472827, 
    1.0648934, 0.9792861, 2.820096), DZ = c(23.7548859, 14.4732136, 
    12.1292, 10.4722, 8.0838209, 7.0575, 5.144598, 4.174118, 
    5.4004, 3.1940577, 1.4427867, 1.1323864, 0.8986281, 2.6422047
    ), FP = c(19.9848602, 16.420735, 12.9683, 11.2059, 7.5143706, 
    6.8747, 5.8653262, 5.5412901, 4.182, 3.5347168, 1.3838249, 
    0.8811604, 0.8585596, 2.7842562)), .Names = c("quality", 
"GY", "TH", "SZ", "DZ", "FP"), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", 
"14"))

   quality         GY         TH         SZ         DZ         FP
1        B 25.5932389 23.0215577 21.2171520 23.7548859 19.9848602
2        F 13.9567819 15.8975387 16.0363831 14.4732136 16.4207350
3        M 11.7333000 10.5075000 11.1540000 12.1292000 12.9683000
4        A  8.6439000  7.8888000  8.5094000 10.4722000 11.2059000
5        G  7.8351008  8.8121504  7.9458840  8.0838209  7.5143706
6        E  8.2900000  8.2760000 10.2717000  7.0575000  6.8747000
7        J  6.0596430  6.4356378  5.4962929  5.1445980  5.8653262
8        D  3.8608517  3.9147685  4.1381946  4.1741180  5.5412901
9        C  4.3277000  5.4838000  5.1141000  5.4004000  4.1820000
10       I  3.1710598  3.9339386  3.4053352  3.1940577  3.5347168
11       K  1.2021144  1.3921565  1.8472827  1.4427867  1.3838249
12       L  1.0986329  0.7424060  1.0648934  1.1323864  0.8811604
13       H  0.9369271  0.8795013  0.9792861  0.8986281  0.8585596
14   other  3.2907496  2.8142444  2.8200960  2.6422047  2.7842562

Solution

• Stacked in the data frame's original order

ra.melt$quality <- factor(ra.melt$quality, levels = ra$quality)
p <- ggplot(ra.melt, aes(x = variable, y = value))
p + geom_bar(aes(fill = quality), stat = "identity") + 
    labs(x = "group", y = "percentage (%)")

factor(ra.melt$quality, levels = ra$quality)

 [1] B     F     M     A     G     E     J     D    
 [9] C     I     K     L     H     other B     F    
[17] M     A     G     E     J     D     C     I    
[25] K     L     H     other B     F     M     A    
[33] G     E     J     D     C     I     K     L    
[41] H     other B     F     M     A     G     E    
[49] J     D     C     I     K     L     H     other
[57] B     F     M     A     G     E     J     D    
[65] C     I     K     L     H     other
Levels: B F M A G E J D C I K L H other

• In reverse order

ra.melt$quality <- factor(ra.melt$quality, levels = rev(ra$quality))
p <- ggplot(ra.melt, aes(x = variable, y = value))
p + geom_bar(aes(fill = quality), stat = "identity") +  
    labs(x = "group", y = "percentage (%)")

factor(ra.melt$quality, levels = rev(ra$quality))

 [1] B     F     M     A     G     E     J     D    
 [9] C     I     K     L     H     other B     F    
[17] M     A     G     E     J     D     C     I    
[25] K     L     H     other B     F     M     A    
[33] G     E     J     D     C     I     K     L    
[41] H     other B     F     M     A     G     E    
[49] J     D     C     I     K     L     H     other
[57] B     F     M     A     G     E     J     D    
[65] C     I     K     L     H     other
Levels: other H L K I C D J E G A M F B

Alternatively we can use the function fct_rev

library(forcats)
p <- ggplot(ra.melt, aes(x = variable, y = value))
p + geom_bar(aes(fill = fct_rev(quality)), stat = "identity") + 
  labs(x = "group", y = "percentage (%)")

• If we'd like to reverse the stacked order but but keeping the order of the legend, we use the argument position_stack(reverse = TRUE)

p <- ggplot(ra.melt, aes(x = variable, y = value))
p + geom_bar(aes(fill = fct_rev(quality)), stat = "identity", position = position_stack(reverse = TRUE)) + labs(x = "group", y = "percentage (%)")

Referencias

• geom_bar
• stacking bars
• stackoverflow

Related posts

• How to reorder bar charts with ggplot2

Cómo reordenar diagramas de barras en ggplot2

Problema

Queremos reodernar las barras de un diagrama de barras creado con ggplot2. En nuestro ejemplo, se puede observar que las barras no están ni en orden ascendente o descendente.

library(tidyverse)
ggplot(df, aes(x = Position)) + geom_bar()

Data frame

df <- structure(list(Position = structure(c(3L, 3L, 1L, 1L, 1L, 2L), .Label = c("Defense", 
"Striker", "Zoalkeeper"), class = "factor"), Name = structure(c(2L, 
1L, 3L, 5L, 4L, 6L), .Label = c("Frank", "James", "Jean", "John", 
"Steve", "Tim"), class = "factor")), class = "data.frame", row.names = c(NA, 
-6L))

    Position  Name
1 Zoalkeeper James
2 Zoalkeeper Frank
3    Defense  Jean
4    Defense Steve
5    Defense  John
6    Striker   Tim

Solución

Una alternativa es usar reorder para reordenar los niveles del factor. En orden ascendente (n) o descendente (-n) en función del conteo de la columna Position.

• Orden ascendente

df %>%
  count(Position) %>%
  ggplot(aes(x = reorder(Position, n), y = n)) +
  geom_bar(stat = 'identity') +
  xlab("Position")

• Orden descendente

df %>%
  count(Position) %>%
  ggplot(aes(x = reorder(Position, -n), y = n)) +
  geom_bar(stat = 'identity') +
  xlab("Position")

Entradas relacionadas

• Cambiar el orden de un diagrama de barras apiladas con ggplot2

Referencias

• stackoverflow