Funções SOMASE e SOMASES no VBA

Neste tutorial você aprenderá como utilizar as funções do Excel SOMASE (SUMIF) e SOMASES (SUMIFS) no VBA.

O VBA não tem um uma função equivalente ao SOMASE ou SOMASES que se possa utilizar – o usuário tem de usar a função interna do Excel no VBA utilizando o objeto WorksheetFunction.

Função WorksheetFunction.SUMIF

O objeto WorksheetFunction pode ser utilizado para chamar a maioria das funções do Excel que estão disponíveis na caixa de diálogo “Inserir Função” no Excel. A função SUMIF (SOMASE) é uma delas.

Sub TesteSumIf()
Range("D10") = Application.WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
End Sub

O procedimento acima irá somar as células no intervalo Range(D2:D9) se, e somente se, a célula correspondente na coluna C = 150.

Atribuindo o resultado da função SUMIF a uma Variável

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

Sub AtribuirSumIfParaVariavel()
   Dim resultado as Double
'Atribui à variável
   resultado = WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
'Mostra o resultado
  MsgBox "O total do resultado correspondente ao código de vendas número 150 é " &  resultado
End Sub

Utilizando a Função SUMIFS

A função SUMIFS é similar à função SUMIF do objeto WorksheetFunction, porém,  a função SUMIFS lhe permite checar mais de um critério. No exemplo abaixo, queremos somar o Preço de Venda SE o Código de Venda for 150 E o Preço de Custo for maior que 2. Perceba que nesta fórmula, o intervalo de células a serem somados está posicionado na frente, enquanto que na função SUMIF, está posicionado no meio.

Sub MultiplosSumIfs()
   Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), Range("C2:C9"), 150, Range("E2:E9"), ">2")
End Sub

 

Utilizando SUMIF com um Objeto Range

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

Sub TesteSumIFRange()
   Dim rngCriterio As Range
   Dim rngSoma as Range
'Atribui o intervalo de células
   Set rngCriterio = Range("C2:C9")
   Set rngSoma = Range("D2:D9")
'Usa o intervalo na fórmula
   Range("D10") = WorksheetFunction.SumIf(rngCriterio, 150, rngSoma)
'Libera o objeto Range da memória
  Set rngCriterio = Nothing
  Set rngSoma = Nothing
End Sub

Utilizando SUMIFS on Multiple Range Objects

De modo similar, você pode utilizar SUMIFS em múltiplos objetos Range.

Sub TesteSomaMultiplosRanges() 
   Dim rngCriterio1 As Range 
   Dim rngCriterio2 as Range
   Dim rngSoma as Range
'Atribui o intervalo range de células 
   Set rngCriterio1= Range("C2:C9")
   Set rngCriterio2 = Range("E2:E9")   
   Set rngSoma = Range("D2:D9")
'Utiliza os intervalos range na formula 
Range("D10") = WorksheetFunction.SumIfs(rngSoma, rngCriterio1, 150, rngCriterio2, ">2")
 'Libera os objetos Range da memória
  Set rngCriterio1 = Nothing 
  Set rngCriterio2 = Nothing
  Set rngSoma = Nothing
End Sub

Perceba que, porque você utilizando o sinal > (maior que), o critério “>2” precisa estar entre aspas.

Fórmula SUMIF

Quando você utiliza a função WorksheetFunction.SUMIF para adicionar a soma à um intervalo na sua planilha, é retornada uma soma estática, e não uma fórmula flexível. Isso significa que quando os valores no Excel mudarem, o valor que foi retornado pela  WorksheetFunction não irá se modificar automaticamente.

No exemplo acima, o procedimento somou o intervalo Range(D2:D9) onde o Código de Venda fosse igual a 150 na coluna C, e o resultado foi colocado em D10. Como você pode ver na Barra de Fórmula, este resultado é um valor, e não uma fórmula.

Se qualquer um dos valores mudarem, seja no intervalo Range(D2:D9) ou no intervalo Range(C2:D9), o resultado em D10 NÃO será modificado.

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

Método Formula

O método Formula lhe permite apontar para um intervalo específico de células, exemplo: D2:D10 as shown below.

Sub TesteSumIf()
  Range("D10").Formula = "=SUMIF(C2:C9,150,D2:D9)"
End Sub

Método FormulaR1C1

O método FormulaR1C1 é mais flexível, no sentido de que não lhe restringe a definir um intervalo de células. O exemplo abaixo lhe dará a mesma resposta que o exemplo acima.

Sub TesteSumIf()
   Range("D10").FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

Entretanto, para deixar a fórmula ainda mais flexível, podemos alterar o código para ficar assim:

Sub TesteSumIf() 
   ActiveCell.FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

Onde quer que você esteja em sua planilha, a fórmula somará as células que atendem ao critério diretamente acima dela e colocará a resposta em sua ActiveCell. O intervalo dentro da função SUMIF deve ser referido usando a sintaxe de linha (R) e coluna (C).

Ambos os métodos lhe permitem utilizar fórmulas dinâmicas do Excel a partir do VBA.

Agora temos uma fórmula em D10 ao invés de um valor.