Ignore exceptions when changing column formula in Excel table
Private Sub Worksheet_Change(ByVal Target As Range)



The Worksheet.Change event gets fired whenever anything changes anywhere in the worksheet. Code you write in a handler for that event must do its business as fast as possible, or it can significantly affect (negatively) the performance of the whole Excel instance.

Whenever possible, avoid using this event for anything that doesn't need to run whenever anything changes anywhere in the worksheet.




Application.Calculation = xlCalculationManual
If Target.Count = 1 Then
Application.EnableEvents = False



Your indentation is off, the assignment Application.EnableEvents = False should be one Tab further to the right.

I like that you're disabling automatic calculation and other worksheet event handlers, but I don't understand why only one of them is under the If block.


    Application.EnableEvents = True
    End If
    ActiveSheet.Calculate
End Sub



The result of this is that you're calculating the active sheet, but you've disabled automatic calculation of the workbook without reinstating it, and you end up systematically calling ActiveSheet.Calculate, which gives the illusion that automatic calculation is still enabled.


tb = Target.ListObject.Name



If the modified cell is outside of a ListObject, this is where your code blows up, and because you're not handling run-time errors, this is where your user sees a VBA debugger prompt and might accidentally end up in your source code with a puzzled facial expression.

Whenever you start writing a method/procedure/function in VBA, you should start with something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler

    'code goes here

CleanExit:
    Exit Sub
ErrHandler:
    Resume CleanExit
    Resume 'unreachable code, for debugging
End Sub


Now you can put a breakpoint on the Resume CleanExit instruction, and can inspect the Err object and then set the Resume dead code as the next statement to be executed, and be taken (F8) exactly on the line the error came from, whenever anything goes wrong with this code.




Dim tb As String
tb = Target.ListObject.Name



If tb is supposed to be the name of a table, then why is it not called tableName? Use descriptive identifiers when naming things!


Dim l As Long
Dim i As Long
Dim s As String
Dim cel As Range
Dim fmla As String
Dim nfmla As String
Dim mfmla() As String
Dim mad() As String



Oh, boy. Need I say more?



I'm not going to review your algorithm (not going to try to wrap my head around those cryptic identifiers), but one thing I can say is that, for the reasons explained at the top of this answer, I'd start the procedure like this:

If Target.ListObject Is Nothing Then Exit Sub


That way you don't bother with anything if the active cell isn't in a table, and the handler can't blow up either when you later try to get the table's name.

Also by only testing for Target.Count = 1 all you're really doing, is ensuring that the current selection only has a single cell. This means if a user selects an entire row (or column) and starts entering data, your code happily sets calculation mode to manual, skipts the entire handler and recalculates the sheet.

Hence, the next thing I'd do after ensuring the Target range has a ListObject, would be to toggle calculation mode and event firing. It's probably best to write a small procedure just for that:

Private Sub ToggleWaitMode(Optional ByVal wait As Boolean = True)
    Application.Calculation = IIf(wait, xlCalculationManual, xlCalculationAutomatic)
    Application.EnableEvents = Not wait
    Application.AutoCorrect.AutoFillFormulasInLists = Not wait
End Sub


That leaves your handler with pretty much only the code that's relevant for its task:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler

    If Target.ListObject Is Nothing Then Exit Sub
    ToggleWaitMode

    '...

CleanExit:
    ToggleWaitMode False
    Exit Sub
ErrHandler:
    Resume CleanExit
    Resume 'unreachable code, for debugging
End Sub




I don't like the ReDim Preserve array handling. You know how many cells you're going to be iterating over (Range(tb &amp; s).Count), therefore you know the size of the array you're going to need at the moment you're declaring it. You'll probably gain some performance by doing this, depending on how many cells the range has.