Funções SOMASE e SOMASES no VBA
In this Article
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.