Guru do Excel e Power BI

When you develop custom functions, it’s important to understand a key distinction between functions that you call from other VBA procedures and functions that you use in worksheet formulas. Function procedures used in worksheet formulas must be passive. For example, code within a Function procedure cannot manipulate ranges or change things on the worksheet. An example can help make this clear.

You might be tempted to write a custom worksheet function that changes a cell’s formatting. For example, it could be useful to have a formula that uses a custom function to change the color of text in a cell based on the cell’s value. Try as you might, however, such a function is impossible to write. No matter what you do, the function won’t change the worksheet. Remember, a function simply returns a value. It cannot perform actions with objects.

That said, I should point out one notable exception. It is possible to change the text in a cell comment by using a custom VBA function.

Walkenbach, J. (2007). Microsoft Office Excel 2007 Power Programming with VBA. John Wiley & Sons, p 280.

Fonte: https://stackoverflow.com/questions/316 ... -add-fails

Public Sub fill_filtered_table(index As Integer, sought_value As String)
    Dim cell As Range, new_row As ListRow, clone As Range, cell_filtered As Range, i As Integer
    
    For Each cell In tabela_fonte.ListColumns(index).DataBodyRange
        If cell.Value Like "*" & sought_value & "*" Then
            Set clone = tabela_fonte.ListRows(cell.row - 1).Range
            ActiveWorkbook.Worksheets("Dados Filtrados").Select
            Set new_row = tabela_filtrada.ListRows.Add(Position:=1, AlwaysInsert:=False) '(AlwaysInsert:=True)
            
            'For i = 1 To tabela_filtrada.ListColumns.Count
                'new_row.Range.Cells(1, i).Value = clone(1, i).Value
            'Next i
            
            For Each cell_filtered In new_row.Range
                cell_filtered.Value = clone(1, cell_filtered.column)
            Next
        End If
    Next
End Sub