VBA Función suma (rangos, columnas y más)

Este tutorial le mostrará cómo utilizar la función Suma de Excel en VBA

La función suma es una de las funciones de Excel más utilizadas, y probablemente la primera que los usuarios de Excel aprenden a usar. Actualmente, VBA no tiene un equivalente – el usuario tiene que utilizar la función incorporada de Excel en VBA utilizando el Objeto WorkSheetFunction.

Suma con WorksheetFunction

El Objeto WorksheetFunction puede utilizarse para llamar a la mayoría de las funciones de Excel que están disponibles en el cuadro de diálogo Insertar función en Excel. La función SUM es una de ellas.

Sub FuncionPrueba()
  Range("D33") = Application.WorksheetFunction.Sum("D1:D32")
End Sub

 

Puedes tener hasta 30 argumentos en la función SUM. Cada uno de los argumentos puede referirse a un rango de celdas.

Este ejemplo de abajo sumará las celdas D1 a D9

Sub PruebaSuma()   
   Range("D10") = Application.WorksheetFunction.SUM("D1:D9")
End Sub

El siguiente ejemplo sumará un rango en la columna D y un rango en la columna F. Si no escribe el objeto Application, se asumirá.

Sub PruebaSuma()
   Range("D25") = WorksheetFunction.SUM(Range("D1:D24"), Range("F1:F24"))
End Sub

Observa que para un solo rango de celdas no tienes que especificar la palabra ‘Range’ en la fórmula delante de las celdas, ya que, el código lo asume; sin embargo, si estás usando múltiples argumentos, sí necesitas hacerlo.

Asignación de un resultado de la suma a una variable

Es posible que quieras utilizar el resultado de tu fórmula en otra parte del código en lugar de escribirlo directamente en un rango de Excel. Si este es el caso, puede asignar el resultado a una variable para usarla más tarde en su código.

Sub asignarResultadoAVariable()
   Dim resultado as Double
'Asignar a la variable "resultado"
  resultado = WorksheetFunction.SUM(Range("G2:G7"), Range("H2:H7"))
'Muestra el resultado
  MsgBox "La suma total del rango es: " &  resultado
End Sub

Sumar un Objeto Range

Puede asignar un grupo de celdas al Objeto Range, y luego usar ese Objeto Range con el objeto WorksheetFunction.

Sub PruebaSumaRango()
   Dim rng As Range
'asignar el rango de celdas
   Set rng = Range("D2:E10")
'utilizar el rango en la fórmula
   Range("E11") = WorksheetFunction.SUM(rng)
'liberar el objeto rng
  Set rng = Nothing
End Sub

Suma de varios Objetos Range

De forma similar, puede sumar múltiples objetos de rango.

Sub PruebaSumaRangos() 
   Dim rngA As Range 
   Dim rngB as Range
'asignar el rango de celdas 
   Set rngA = Rango("D2:D10") 
   Set rngB = Rango("E2:E10")   
'utilizar el rango en la fórmula 
Range("E11") = WorksheetFunction.SUM(rngA, rngB)
'liberar los objetos
  Set rngA = Nothing 
  Set rngB = Nothing
End Sub

Suma de toda la columna o fila

También puedes utilizar la función Suma para sumar una columna o una fila entera.

Este procedimiento de abajo sumará todas las celdas numéricas de la columna D.

Sub PruebaSuma()    
   Range("F1") = WorksheetFunction.SUM(Range("D:D")
End Sub

Mientras que este procedimiento de abajo sumará todas las celdas numéricas de la fila 9.

Sub PruebaSuma()     
   Range("F2") = WorksheetFunction.SUM(Range("9:9") 
End Sub

Sumar un Array

También puedes utilizar WorksheetFunction.Sum para sumar valores en un array.

Sub PruebaArreglo()
   Dim intA(1 To 5) As Integer
   Dim SumaArreglo As Integer
'Llenar el arreglo
   intA(1) = 15
   intA(2) = 20
   intA(3) = 25
   intA(4) = 30
   intA(5) = 40
'Suma el arreglo y muestra el resultado
   MsgBox WorksheetFunction.SUM(intA)
End Sub

Uso de la función SumIf

Otra función de la hoja de cálculo que se puede utilizar es la función SUMIF.

Sub PruebaSumIf()
Range("D11") = WorksheetFunction.SUMIF(Range("C2:C10"), 150, Range("D2:D10"))
End Sub

El procedimiento anterior sólo sumará las celdas del Rango(D2:D10) si la celda correspondiente de la columna C es igual a 150.

 

 

Fórmula de suma

Cuando utilizas la función WorksheetFunction.SUM para añadir una suma a un rango en tu hoja de cálculo, se devuelve una suma estática, no una fórmula flexible. Esto significa que cuando tus cifras en Excel cambian, el valor que ha sido devuelto por la WorksheetFunction no cambiará.

 

En el ejemplo anterior, el procedimiento PruebaSuma ha sumado Range(D2:D10) y el resultado se ha puesto en D11. Como puedes ver en la barra de fórmulas, este resultado es una cifra y no una fórmula. Por lo tanto, si alguno de los valores cambia en el Rango(D2:D10), el resultado en D11 NO cambiará. En lugar de utilizar la función WorksheetFunction.SUM, puedes utilizar VBA para aplicar una función de suma a una celda utilizando los métodos Formula o FormulaR1C1.

Método de la Fórmula

El método de la fórmula le permite apuntar específicamente a un rango de celdas, por ejemplo: D2:D10 como se muestra a continuación.

Sub PruebaSumaFormula()
  Range("D11").Formula = "=SUM(D2:D10)"
End Sub

 

Método FormulaR1C1

El método FormulaR1C1 es más flexible en el sentido de que no lo restringe a un rango de celdas establecido. El siguiente ejemplo nos dará la misma respuesta que el anterior.

Sub PruebaSumaFormulaR1C1()
   Range("D11").FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
End Sub

Sin embargo, para hacer la fórmula más flexible, podríamos modificar el código para que se vea así

Sub PruebaSumaFormulaR1C1_CeldaActiva()
   ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
End Sub

En cualquier lugar de la hoja de cálculo, la fórmula sumará las 8 celdas que están justo encima y colocará la respuesta en su CeldaActiva. El Rango dentro de la función SUM tiene que ser referido usando la sintaxis de Fila (R) y Columna (C). Ambos métodos le permiten utilizar fórmulas dinámicas de Excel dentro de VBA. Ahora habrá una fórmula en D11 en lugar de un valor.