Formules voorzien van een foutcontrole

Pagina's in dit artikel

  1. Simpele versie
  2. Array Formulas

Eerste versie

Het gebeurt regelmatig, dat ik een bereik met formules heb dat op zich goed werkt, maar dat een foutcontrole nodig heeft, bijvoorbeeld om delen door nul te voorkomen. Neem bijvoorbeeld de volgende situatie:

 Een cel met een mogelijke foutwaarde
Als cel A3 geen data bevat of een nul, dan wordt het resultaat in cel C3 #DEEL/0!

Als je dit soort fouten niet wilt laten zien, dan is de oplossing eenvoudig: test of de celinhoud waardoor wordt gedeeld gelijk is aan nul:

=ALS(A3=0;"";B3/A3)

Maar ik heb meestal veel gecompliceerdere formules, waarbij de fout regelmatig niet door slechts 1 cel wordt veroorzaakt. In dat geval wil ik de hele formule in een foutcontrole verpakken. Ik heb daarom een kleine VBA routine geschreven, die het vereenvoudigt om de formule van meerdere cellen van een foutcontrole te voorzien. Hier is de eerste versie van een dergelijke routine:

Sub ChangeFormulas()
    Dim oCell As Range
    Dim sFormula As String
    Dim sInput As String
    Static sFormulaTemplate As String
    If sFormulaTemplate = "" Then
        sFormulaTemplate = "=IF(ISERROR(_form_),"""",_form_)"
    End If
    sInput = InputBox("Voer basis formule in", , sFormulaTemplate)
    If sInput = "" Then Exit Sub
    sFormulaTemplate = sInput
    For Each oCell In Selection
        sFormula = Replace(sFormulaTemplate, "_form_", Right(oCell.Formula, Len(oCell.Formula) - 1))
        oCell.Formula = sFormula
    Next
End Sub

Hoe werkt dit?

Selecter de cellen waar de foutcontrole gewenst wordt. Run dan de bovenstaande subroutine. Een Input box wordt weergegeven, die een standaard sjabloon toont waarmee de bestaande formule zal worden aangepast (In Engelse notatie):

=IF(ISERROR(_form_),"",_form_)

In bovenstaand voorbeeld zal overal waar de tekst "_form_" staat, de huidige formule die in de cel staat worden geplaatst. Bewerk dit sjabloon en klik OK.

Omdat de variabele sFormulaTemplate als Static is gedeclareerd, zal de macro het sjabloon onthouden gedurende de tijd dat Excel open is.

Na uitvoeren van deze macro op de cel C3 van hierboven, wordt de nieuwe formule in C3:

=ALS(ISFOUT(B3/A3);"";B3/A3)