Função Sum (Somar Intervalos, Colunas & Mais) em VBA

Este tutorial irá lhe ensinar como usar a função Sum (Somar) do Excel no VBA

A função Sum (Somar) é uma das mais utilizadas Funções do Excel, e provavelmente a primeira que os usuário de Excel aprendem a usar. O VBA, na verdade, não tem um equivalente a essa função do Excel – o usuário precisa usar a função interna Excel no VBA através do objeto WorkSheetFunction.

Sum WorksheetFunction

O objeto WorksheetFunction pode ser usado para chamar a maioria das funções Excel que estão disponíveis dentro da janela Inserir Função no Excel. A função SUM é uma delas.

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

Você pode ter ter até 30 argumentos na função SUM. Cada argumento pode também se referir a um intervalo de células.

O exemplo abaixo irá somar as células do intervalo D1 a D9

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

O exemplo abaixo irá somar o intervalo na coluna D e o intervalo na coluna F. Se você não explicitar o objeto ‘Application’ na frente de WorksheetFunction, ele ficará implícito.

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

Perceba que para um único intervalo de células você não precisa especificar a palavra ‘Range’ na fórmula na frente do endereço das células,  isso já fica implícito no código. Entretanto, se você está usando múltiplos argumentos, você precisará explicitar a palavra ‘Range’.

Atribuindo o Resultado da Soma a uma Variável

Você pode precisar utilizar o resultado da sua fórmula em outro lugar no código ao invés de escrevê-lo de volta diretamente em um intervalo no Excel. Se este for o caso, você pode atribuir o resultado a uma variável para usar posteriormente em seu código.

Sub AtribuirSomaAVariavel()
   Dim resultado as Double
'Atribuir à variável
   resultado = WorksheetFunction.SUM(Range("G2:G7"), Range("H2:H7"))
'Mostra o resultado
  MsgBox "O Total do intervalo é " &  resultado
End Sub

Somar um Objeto Range

Você pode atribuir um grupo de células a um objeto Range, e então usar esse objeto Range com o objeto WorksheetFunction.

Sub TesteSomaIntervalo()
   Dim rng As Range
'Atribui o intervalo de células ao objeto Range de nome rng
   Set rng = Range("D2:E10")
'Usa o intervalo na fórmula
   Range("E11") = WorksheetFunction.SUM(rng)
'Libera a memória utilizada pelo objeto Range.
  Set rng = Nothing
End Sub

Somar Múltiplos Objetos Range

De modo similar, você pode somar múltiplos Objetos Range.

Sub TesteSomaMultiplosIntervalos() 
   Dim rngA As Range 
   Dim rngB as Range
'Atribui os intervalos de células
   Set rngA = Range("D2:D10") 
   Set rngB = Range("E2:E10")   
'Utiliza os intervalos na fórmula.
Range("E11") = WorksheetFunction.SUM(rngA, rngB)
 'Libera a memória utilizada pelos objetos Range.
  Set rngA = Nothing 
  Set rngB = Nothing
End Sub

Somar a Coluna ou Linha Inteira

Você também pode usar a função SUM para somar uma coluna inteira ou uma linha inteira.

O procedimento a seguir irá somar todas as células numéricas na Coluna D.

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

Similarmente, o procedimento abaixo irá somar todas as células numéricas na Linha 9.

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

Somar um Array

Você também pode usar a função WorksheetFunction.Sum para somar valores em um array.

Sub TesteArray()
   Dim intA(1 To 5) As Integer
   Dim SomaArray As Integer
'Preenche o array
   intA(1) = 15
   intA(2) = 20
   intA(3) = 25
   intA(4) = 30
   intA(5) = 40
'Soma o array e exibe o resultado
   MsgBox WorksheetFunction.SUM(intA)
End Sub

Usando a Função SumIf (SomaSe)

Outra  função do Excel que pode ser utilizada é a função SUMIF.

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

O procedimento acima irá somar as células do intervalo Range(D2:D10) somente se a célula correspondente na coluna C for igual a 150.

Fórmula Sum

Quando você utilizar a função WorksheetFunction.SUM para adicionar a soma ao intervalo em sua planilha, uma soma estática é retornada, não uma fórmula flexível. Isto significa que quando seus valores no Excel mudarem, o valor que foi retornado pela WorksheetFunction não irá mudar.

No exemplo acima, o procedimento TesteSoma somou o intervalo Range(D2:D10) e o resultado foi colocado em D11. Como você pode ver na fórmula, este resultado é um valor numérico, não uma fórmula.

Se qualquer valor mudar no intervalo Range(D2:D10), o resultado em D11 NÃO irá mudar.

Ao invés de usar a função WorksheetFunction.SUM, você pode utilizar o VBA para aplicar a função SOMA a uma célula usando os métodos Formula ou FormulaR1C1.

Método Formula

O método formula lhe permite apontar para um intervalo de células específico para, exemplo: D2:D10 como mostrado abaixo.

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

 

Método FormulaR1C1

O método FormulaR1C1 é mais flexível na medida em que não the restringe a um intervalo de células. O exemplo abaixo irá nos dar a mesma resposta dada no exemplo acima.

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

No entanto, para tornar a fórmula mais flexível, podemos alterar o código para ficar assim:

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

Onde quer que você esteja na sua planilha, a fórmula irá somar as 8 células diretamente acima e colocar o resultado na sua ActiveCell. O intervalo dentro da função SOMA tem de ser referenciado usando a sintaxe da Linha (R) e Coluna (C).

Ambos os métodos permitem que você utilize fórmulas dinâmicas do Excel no VBA.

Agora haverá uma formula em D11 ao invés de um valor numérico.