Como calcular a média dos valores no excel via vba?

No Excel, você pode usar o VBA para calcular valores médios de um intervalo de células ou de vários intervalos. E, neste tutorial, aprenderemos as diferentes formas de utilizá-lo.

Média em VBA usando WorksheetFunction

No VBA, existem diversas funções que você pode utilizar, mas não existe uma função específica para essa finalidade. Isso não significa que não podemos tirar uma média. No VBA, existe uma propriedade chamada WorksheetFunction que pode ajudá-lo a chamar funções no código VBA.

Vamos calcular a média dos valores no intervalo A1:A10.

1. Primeiro insira a propriedade da função da planilha e selecione a função AVERAGE na lista.
   
2. Em seguida, você deve inserir um parêntese inicial, como faz ao inserir uma função na planilha.
   
3. Depois disso, precisamos usar o objeto range para nos referirmos ao intervalo para o qual queremos calcular a média.
   
4. Ao final, digite um parêntese de fechamento e atribua o valor de retorno da função à célula B1.
   

 Application.WorksheetFunction.Average( Range ("A1:A10"))

Agora ao executar esse código ele calcula a média dos valores que você tem no intervalo A1:A10 e insere o valor na célula B1.

Valores médios de uma coluna ou linha inteira

Neste caso, você só precisa especificar uma linha ou coluna em vez do intervalo que usamos no exemplo anterior.

 'for the entire column A Range ("B1") = Application.WorksheetFunction.Average( Range ("A:A")) 'for entire row 1 Range ("B1") = Application.WorksheetFunction.Average( Range ("1:1"))

Use VBA para calcular a média dos valores de seleção

Agora, suponha que você queira calcular o valor médio das células selecionadas apenas porque pode usar um código como este.

 Sub vba_average_selection() Dim sRange As Range Dim iAverage As Long On Error GoTo errorHandler Set sRange = Selection iAverage = WorksheetFunction.Average(Range(sRange.Address)) MsgBox iAverage Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub

No código acima, usamos a seleção, depois especificamos na variável “sRange”, depois usamos o endereço dessa variável de intervalo para obter a média.

Média VBA de todas as células acima

O código a seguir pega todas as células acima e seus valores médios e insere o resultado na célula selecionada .

 Sub vba_auto_Average() Dim iFirst As String Dim iLast As String Dim iRange As Range On Error GoTo errorHandler iFirst = Selection. End (xlUp). End (xlUp).Address iLast = Selection. End (xlUp).Address Set iRange = Range(iFirst & ":" & iLast) ActiveCell = WorksheetFunction.Average(iRange) Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub

Calculando a média de um intervalo dinâmico usando VBA

E da mesma forma, você pode usar a faixa dinâmica ao usar o VBA para calcular a média dos valores.

 Sub vba_dynamic_range_average() Dim iFirst As String Dim iLast As String Dim iRange As Range On Error GoTo errorHandler iFirst = Selection.Offset(1, 1).Address iLast = Selection.Offset(5, 5).Address Set iRange = Range(iFirst & ":" & iLast) ActiveCell = WorksheetFunction.Average(iRange) Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub

Média de uma coluna ou linha dinâmica

Da mesma forma, se quiser usar uma coluna dinâmica, você pode usar o código a seguir que pegará a coluna da célula ativa e a média de todos os valores que você tem lá.

 Sub vba_dynamic_column() Dim iCol As Long On Error GoTo errorHandler iCol = ActiveCell.Column MsgBox WorksheetFunction.Average(Columns(iCol)) Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub

E por uma fileira.

 Sub vba_dynamic_row() Dim iRow As Long On Error GoTo errorHandler iRow = ActiveCell.Row MsgBox WorksheetFunction.Average(Rows(iCol)) Exit Sub errorHandler: MsgBox "make sure to select a valid range of cells" End Sub