PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT

Hola a todos!

En el post de hoy voy a tratar de ayudar y dar respuesta a un lector que me comentaba la necesidad de usar SmartArt para generar un diagrama de Gantt personalizado.

(*Antes de entrar en materia, si estáis usando Excel 2010 debéis leer el post completo dado que tendréis que realizar una pequeña modificación en el código).

En un principio, y teniendo en cuenta la naturaleza y objetivo de un diagrama de Gantt, creo que la mejor solución es utilizar una estructura de jerarquía horizontal, para ello me voy a basar en este objeto de SmartArt para realizar el ejemplo (Jerarquía multinivel en horizontal)

Teniendo en cuanta esto, antes de comenzar a programar, debemos diseñar nuestro cuadro de mando para introducir todos los ítems que necesitamos para confeccionar nuestro diagrama, para este ejemplo utilizaré estas tareas:

Aunque se podría programar para obtener automáticamente el porcentaje de consecución de objetivos de acuerdo con la fecha, para este ejemplo he decidido que solo se calculan de forma automática los días entre fechas, el resto es a criterio del usuario el llevar el seguimiento y controlar el %.

Como podéis observar, estoy utilizando diferentes niveles de tareas, dado que algunas conforman otras de entidad superior, por ejemplo las diferentes fases de diseño o de desarrollo. Esto implicará que en nuestra estructura de jerarquía se muestren esas dependencias y ofrezca mayor información visual.

En realidad este diseño puede personalizarse de muchas formas, tantas como le gusten al usuario.

Pues bien, una vez que tenemos esta información en la hoja «DATOS», ya podemos utilizar el siguiente código para generar nuestro peculiar diagrama de Gantt:

Sub DIAGRAMA_GANTT_SMARTART()
'Declaramos variables
Dim Diseño As SmartArtLayout
Dim Shape As Excel.Shape
Dim oNodos As SmartArtNodes
Dim inserta As Shape
Dim i As Integer, Fin As Integer
Dim j As Integer, valor As Double
Dim NPorcent As String, Per_2 As Long, Per_1 As Long
With Sheets("ESTRUCTURA")
.Select
'Eliminamos TODOS objetos en la hoja "ESTRUCTURA"
For Each Shape In .Shapes
Shape.Delete
Next
'Insertamos objeto SmartArt, en este caso "Jerarquía Multinivel"
Set Diseño = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2008/layout/HorizontalMultiLevelHierarchy")
Set inserta = .Shapes.AddSmartArt(Diseño)
Set oNodos = inserta.SmartArt.AllNodes
'Verificamos número de nodos necesarios contando los ítems de la página "DATOS"
Fin = Application.CountA(Sheets("DATOS").Range("A:A"))
'Creamos nodos
Do While oNodos.Count < Fin
oNodos.Add.Promote
Loop
'Eliminamos nodos sobrantes y los nombramos con la información de la hoja "DATOS"
For i = 2 To Fin
Do While oNodos(i - 1).Level < Sheets("DATOS").Range("B" & i).Value
oNodos(i - 1).Demote
Loop
Next
'Eliminamos último nodo (estará vacío al tener encabezado la hoja "DATOS")
oNodos(Fin).Delete
'aplicamos estilos
For Each Shape In .Shapes
'Colores
Shape.SmartArt.Color = Application.SmartArtColors("urn:microsoft.com/office/officeart/2005/8/colors/accent2_1")
'Estilos rápidos
Shape.SmartArt.QuickStyle = Application.SmartArtQuickStyles("urn:microsoft.com/office/officeart/2005/8/quickstyle/simple2")
'Iniciamos loop para recorrer el diagrama desde el último item al primero
For j = Fin To 2 Step -1
'Si el % está vacío, asignamos un valor 0 a la variable valor
If Sheets("DATOS").Range("F" & j).Value = Empty Then
valor = 0
ElseIf Sheets("DATOS").Range("F" & j).Value > 1 Then
valor = 1
Else
valor = Sheets("DATOS").Range("F" & j).Value
End If
'expresamos en color el porcentaje de cumplimiento de objetivos
With oNodos(j - 1).Shapes.Fill
.TwoColorGradient Style:=msoGradientVertical, Variant:=1
.GradientStops(2).Color = vbWhite
.GradientStops(1).Position = valor
.GradientStops(2).Position = valor
.GradientStops(1).Color.RGB = RGB(204, 204, 255)
End With
'Adicionalmente añadimos el porcentaje en número al diagrama y lo coloreamos en azul
With oNodos(j - 1)
.TextFrame2.TextRange.Text = Sheets("DATOS").Range("A" & j) & " " & Format(valor, "Percent")
NPorcent = Sheets("DATOS").Range("A" & j) & " " & Format(valor, "Percent")
Per_1 = UBound(Split(NPorcent)) + 1
Per_2 = UBound(Split(NPorcent)) + 2
.TextFrame2.TextRange.Words(Per_1).Font.Fill.ForeColor.RGB = vbBlue
.TextFrame2.TextRange.Words(Per_2).Font.Fill.ForeColor.RGB = vbBlue
End With
Next j
'Dimensionamos la imagen
With .Shapes(1)
.Height = 581.25 'Alto del objeto
.Width = 600.5 'Ancho del objeto
.Top = 100 ' Altura en la hoja
.Left = 14.25 ' A la izquierda de la hoja
End With
Next
End With
End Sub


Una vez aplicado el código generamos el siguiente diagrama:

Efectivamente, logramos nuestro propósito, el % de consecución se equipara al color azul, que se va desplazando a la derecha a medida que vamos cumpliendo los plazos. Este efecto lo conseguimos con esta parte de la macro:

With oNodos(j - 1).Shapes.Fill
.TwoColorGradient Style:=msoGradientVertical, Variant:=1
.GradientStops(2).Color = vbWhite
.GradientStops(1).Position = valor
.GradientStops(2).Position = valor
.GradientStops(1).Color.RGB = RGB(204, 204, 255)
End With

Al superponer los dos gradientes conseguimos evitar el degradado de los dos colores y que aparezca una línea delimitadora entre el azul y el blanco.

Importante, si estáis programando con Excel 2010 debéis añadir un factor de corrección en la siguiente línea de código, y es añadir un -0.01 al final:

.GradientStops(1).Position = valor - 0.01

Esto se corrige en las versiones 2013 y 2016 (comprobado), pero en 2010 es necesario rebajar en un 0.01 puntos el gradiente nº 1 para que no se muestren los colores difuminados. El ejemplo lo dejo sin el 0.01 dado que estoy programando en Excel 2016 y no es necesario.

También es muy interesante la forma en la que podemos colorear parte de un texto, dado que lo que hacemos es detectar las dos últimas palabras (el número y el símbolo del porcentaje) y aplicarle un color del sistema (azul):

.TextFrame2.TextRange.Words(Per_1).Font.Fill.ForeColor.RGB = vbBlue
.TextFrame2.TextRange.Words(Per_2).Font.Fill.ForeColor.RGB = vbBlue

Aunque podríamos seguir introduciendo elementos en cada nodo, creo que con este ejemplo se consigue, con una simple visual, hacer una idea del nivel de desarrollo en el que se encuentra nuestro proyecto.

En cuanto a la información de la hoja «DATOS», usamos toda la información excepto las fechas y los días. Los datos obligatorios para generar el diagrama son las tareas, la jerarquía y los porcentajes (que van desde el 0% al 100%).

Y esto esto, espero que os resulte de interés : ) Os dejo dos archivos, uno para 2010 y el otro para versiones superiores.

Descarga el archivo de ejemplo pulsando en: PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT

Descarga el archivo de ejemplo pulsando en: PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT_v2010

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.



¡¡Muchas gracias!!