Creando gráficos en Excel automáticamente

Posted by in Visual Basic y VBA

Recientemente tuve que automatizar la creación de gráficas en todas las solapas de un libro de Microsoft Excel, así que he decidido publicar el «cómo lo hago» mediante #VBA. El fichero Excel de trabajo requiere que todas las solapas sean «tablas«, en mi caso, proceden de consultas a una base de datos, y cómo eran muchas solapas y una tarea repetitiva, que mejor que automatizarlo.

Para completar la tarea, primero crearemos una función para trabajar con todas las solapas, y después aplicaremos una función a todas ellas.

Solapas de un libro

Empezamos por conocer las solapas de un libro Excel, para poder aplicar la rutina a todas.

Public Sub Trabaja()
Dim i As Integer, sNomb As String
For i = 1 To Sheets.Count
 sNomb = Sheets.Item(i).Name
 Sheets(sNomb).Select ' Seleccionamos solapa
 EliminaColumnasSinDatosGrafica
Next
End Sub

La rutina Trabaja es la que se encarga de ir seleccionando todas las solapas, y aplicarle el mismo procesamiento EliminaColumnasSinDatosGrafica.

Para conocer las dimensiones de la tabla, haremos uso de las funciones ColumnaProcesar y FilasProcesar, ya explicadas en la entrada anterior Dimensiones de tablas en VBA Excel.

Eliminar columnas sin datos

Cómo el resultado de la consulta, a veces implica que haya columnas sin datos, vamos a incluir una pequeña función que detecte que la columna no tiene datos a procesar. Es necesario que busquemos la última fila a procesar, para todas las columnas. Para ello, volvemos a utilizar FilasProcesar. Si el número es bajo, entonces eliminamos dicha fila.
Para eliminar una fila, creamos la siguiente función:


Public Sub EliminaColumna(ByVal numCol As Integer)
    Columns(numCol).Select
    Selection.Delete Shift:=xlToLeft
End Sub

Código de crear gráfica

Para crear la gráfica, tenemos que seleccionar los datos, por lo que para conocer el rango en la forma que la gusta a Excel, requerimos la función de conocer la letra de una columna, utilizando el código ASCII de los caracteres, para lo que requerimos de CHR y de ASC:


Public Function Letra(ByVal numCol) As String
  Letra = Chr(64 + numCol)
End Function

Por último, el código para generar la gráfica de un modo muy simple es:


    Range("A1:" & Letra(numCols) & numRows).Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'" & nombresolapa & "'!$A$1:$" & Letra(numCols) & "$" & numRows)
    ActiveChart.ChartType = xlLine

Y la función completa es:


Public Sub EliminaColumnasSinDatos(nombresolapa)
Dim numCols As Integer, numRows As Integer, intI As Integer, numR2P As Integer
numCols = mFunciones.ColumnaProcesar(1, 1, 1)
numRows = mFunciones.FilasProcesar(1, 1, 1)
For intI = numCols To 2 Step -1
numR2P = mFunciones.FilasProcesar(1, intI, 1)
If numR2P < kLIMITE_Elimina_COLUMNA Then EliminaColumna (intI)
Next

numCols = mFunciones.ColumnaProcesar(1, 1, 1) ' Recalcula columnas tras eliminación
If numCols > 1 Then
Range("A1:" & Letra(numCols) & numRows).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'" & nombresolapa & "'!$A$1:$" & Letra(numCols) & "$" & numRows)
ActiveChart.ChartType = xlLine
End If
End Sub

Aquí os dejo el fichero xlsm Excel para crear gráficas automáticamente para que lo utiliceis, y si teneis alguna duda o mejora, me escribís un email o dejarme un comentario!